Changes between Version 6 and Version 7 of AdvancedReports

Timestamp:

02/17/25 12:14:21 (8 months ago)

Author:

185022

Comment:

--

AdvancedReports

@@ -40 +40 @@
         avs.avg_units_sold,
         (i.currentStock - avs.avg_units_sold) as remaining_after_forecast
-from inventory i
+from Inventory i
 join article a on i.art_id = a.art_id
 join AvgSales avs on i.art_id = avs.art_id
@@ -48 +48 @@
 
 {{{#!sql
-with salesByPrice as (select au.art_id,
-                             p.price,
-                             p.price_eff_date,
-                             count(au.unit_id)           as unitsSold,
-                             count(au.unit_id) * p.price as revenue
-                      from article_unit au
-                               join orders o on au.ord_id = o.ord_id
-                               join price p on au.art_id = p.art_id
-                      where o.ord_date >= p.price_eff_date
-                        and o.ord_date < coalesce(
-                              (select min(price_eff_date)
-                               from price p2
-                               where p2.art_id = p.art_id
-                                 and p2.price_eff_date > p.price_eff_date),
-                              '9999-12-31'
-                                         )
-                      group by au.art_id, p.price, p.price_eff_date)
-select a.art_name        as articleName,
-       sp.price          as unitPrice,
-       sp.price_eff_date as priceEffectiveDate,
-       sp.unitsSold,
-       sp.revenue
-from salesByPrice sp
-         join article a on sp.art_id = a.art_id
-order by a.art_name,sp.price_eff_date;
+with PriceChanges as (
+        select
+                p.art_id,
+                p.price,
+                p.price_eff_date,
+                lag(p.price) over (partition by p.art_id order by p.price_eff_date) as prevPrice
+        from price p
+),
+FilteredPrice as (
+        select
+                art_id,
+                price,
+                price_eff_date
+        from PriceChanges
+        where prevPrice is null or abs(price - prevPrice) >= 1
+),
+SalesByPrice as (
+        select
+                p.art_id,
+                fp.price,
+                fp.price_eff_date,
+                count(au.unit_id) as units_sold,
+                count(au.unit_id) * fp.price as revenue
+        from article_unit au
+        join orders o on au.ord_id = o.ord_id
+        join unit_price up on up.unit_id = au.unit_id 
+        join price p on up.price_id = p.price_id 
+        join FilteredPrice fp on p.art_id = fp.art_id
+                                                and p.price_eff_date = fp.price_eff_date
+        where o.ord_date >= fp.price_eff_date
+                and o.ord_date < coalesce(
+                        (select min(fp2.price_eff_date)
+                        from FilteredPrice fp2
+                        where fp2.art_id = fp.art_id
+                                and fp2.price_eff_date > fp.price_eff_date),
+                        '9999-12-31'
+                )
+        group by p.art_id, fp.price, fp.price_eff_date
+)
+select
+        a.art_name as articleName,
+        sp.price as unitPrice,
+        sp.price_eff_date as priceEffectiveDate,
+        sp.units_sold,
+        sp.revenue
+from SalesByPrice sp
+join article a on sp.art_id = a.art_id
+order by a.art_name, sp.price_eff_date;
 }}}