Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

01/06/25 15:01:53 (9 days ago)

Author:

185022

Comment:

--

AdvancedReports

@@ -146 +146 @@
 
 {{{#!sql
-
+with ProductSales as (select au.art_id,
+                             a.art_name,
+                             cat.ctg_name                           as category,
+                             sum(p.price)                           as totalIncome,
+                             sum(au.unit_cost_price)                as totalCost,
+                             sum(p.price) - sum(au.unit_cost_price) as totalProfit,
+                             count(au.unit_id)                      as totalUnitsSold
+                      from article_unit au
+                               join article a on au.art_id = a.art_id
+                               join category cat on a.ctg_id = cat.ctg_id
+                               join price p on a.art_id = p.art_id
+                               join orders o on au.ord_id = o.ord_id
+                      where o.ord_date between '2024-01-01' and '2024-12-31'
+                        and o.ord_date >= p.price_eff_date
+                      group by au.art_id, a.art_name, cat.ctg_name)
+select art_id,
+       art_name,
+       category,
+       totalUnitsSold,
+       totalIncome,
+       totalCost,
+       totalProfit
+from ProductSales
+order by totalProfit desc;
 }}}