Changes between Version 2 and Version 3 of AdvancedReports

Timestamp:

01/06/25 12:00:32 (10 months ago)

Author:

185022

Comment:

--

AdvancedReports

@@ -4 +4 @@
 
 {{{#!sql
-
+with latestPrice as (
+    select p.art_id,p.price as latest_price
+    from price p
+    where p.price_eff_date = (
+        select max(price_eff_date)
+        from price
+        where p.art_id=p.art_id
+        )
+)
+select c.cust_company_name as customerName,
+       c.user_id as customerId,
+       a.art_name as articleName,
+       count(au.unit_id) as quantity,
+       lp.latest_price as unitPrice,
+       count(au.unit_id)*lp.latest_price as total_spent,
+       sum(o.ord_sum) as orderTotal
+from orders o
+join article_unit au on o.ord_id = au.ord_id
+join article a on au.art_id = a.art_id
+join customer c on o.cust_id = c.user_id
+join latestPrice lp on a.art_id=lp.art_id
+where o.ord_date between '2024-01-01' and '2024-12-31'
+group by c.cust_company_name,c.user_id,a.art_name,lp.latest_price
+order by c.cust_company_name,a.art_name;
 }}}
 
-==== Автоматско дополнување на залихите во зависност од моменталната и претпоставената идна потрошувачка/Артикли кои се при крај на залиха во магацинот и треба да се набават дополнителни количини
+==== Артикли кои се при крај на залиха во магацинот и треба да се набават дополнителни количини
 
 {{{#!sql
-
+select a.art_name as articleName, w.wh_id as warehouseId, count(distinct au.unit_id) as unitNumber
+from article_unit au
+         join warehouse w on au.wh_id = w.wh_id
+         join article a on au.art_id = a.art_id
+where w.wh_id = 1
+group by a.art_name, w.wh_id
+order by unitNumber;
 }}}
 
@@ -16 +45 @@
 
 {{{#!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;
 }}}
 
@@ -22 +75 @@
 
 {{{#!sql
-
+with ArticleSales as (
+    select au.art_id,
+           a.ctg_id,
+           w.wh_id,
+           c.city_id,
+           r.region_id,
+           p.price as unitPrice,
+           count(au.unit_id) as unitsSold,
+           count(au.unit_id)*p.price as income
+    from article_unit au
+    join article a on au.art_id = a.art_id
+    join warehouse w on au.wh_id = w.wh_id
+    join city c on w.city_id = c.city_id
+    join region r on c.region_id = r.region_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>=p.price_eff_date
+    group by au.art_id, a.ctg_id, w.wh_id, c.city_id, r.region_id, p.price, p.price
+)
+select r.region_name as region,
+       cat.ctg_name as category,
+       sum(asales.unitsSold) as totalUnitsSold,
+       sum(asales.income) as totalIncome
+from ArticleSales asales
+join category cat on asales.ctg_id=cat.ctg_id
+join region r on asales.region_id=r.region_id
+group by r.region_name, cat.ctg_name
+order by r.region_name, totalIncome desc;
 }}}
 
@@ -28 +108 @@
 
 {{{#!sql
-
+select w.wh_id as warehouseId,
+       w.wh_adr as warehouseAddress,
+       c.city_name as warehouseCity,
+       r.region_name as warehouseRegion,
+       extract(year from o.ord_date) as year,
+       extract(quarter from o.ord_date) as quarter,
+       count(au.unit_id) as totalArticlesSold,
+       sum(o.ord_sum) as totalSales
+from warehouse w
+join city c on w.city_id = c.city_id
+join region r on c.region_id = r.region_id
+join article_unit au on w.wh_id = au.wh_id
+join orders o on au.ord_id = o.ord_id
+group by w.wh_id, w.wh_adr, c.city_name, r.region_name, extract(year from o.ord_date), extract(quarter from o.ord_date)
+order by w.wh_id,year,quarter;
 }}}
 
@@ -34 +128 @@
 
 {{{#!sql
-
+select w.wh_id as warehouseId,
+       c.city_name as warehouseCity,
+       r.region_name as warehouseRegion,
+       extract(year from o.ord_date) as year,
+       sum(o.ord_sum) as totalIncome,
+       sum(ord_sum)-sum(au.unit_cost_price) as total_profit
+from warehouse w
+         join city c on w.city_id = c.city_id
+         join region r on c.region_id = r.region_id
+         join article_unit au on w.wh_id = au.wh_id
+         join orders o on au.ord_id = o.ord_id
+group by w.wh_id, c.city_name, r.region_name, o.ord_date
+order by w.wh_id,year;
 }}}