Changes between Version 5 and Version 6 of AdvancedReports
- Timestamp:
- 02/17/25 11:46:06 (5 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedReports
v5 v6 1 1 = Напредни извештаи од базата (SQL и складирани процедури) 2 2 3 ==== П отрошувачка по коминтент за даден период по производ, по количина и пари3 ==== Прогноза на потрошувачка на артикли од магацинот според продажба во претходните недели, за подобра претпоставка за набавка на нови артикли 4 4 5 5 {{{#!sql 6 with SalesByCustomer as (select o.cust_id, 7 au.art_id, 8 p.price, 9 count(au.unit_id) as quantity, 10 count(au.unit_id) * p.price as total_spent 11 from article_unit au 12 join orders o on au.ord_id = o.ord_id 13 join price p on au.art_id = p.art_id 14 where o.ord_date between '2024-01-01' and '2024-12-31' 15 group by o.cust_id, au.art_id, p.price) 16 select c.cust_company_name as customerName, 17 a.art_name as articleName, 18 sp.price as unitPrice, 19 sp.quantity, 20 sp.total_spent as totalMoneySpent, 21 sp.total_spent / sp.quantity as avgUnitPrice 22 from SalesByCustomer sp 23 join customer c on sp.cust_id = c.user_id 24 join article a on sp.art_id = a.art_id 25 order by c.cust_company_name, a.art_name; 26 }}} 27 28 ==== Артикли кои се при крај на залиха во магацинот и треба да се набават дополнителни количини 29 30 {{{#!sql 31 select a.art_name as articleName, w.wh_id as warehouseId, count(distinct au.unit_id) as unitNumber 6 with WeeklySales as ( 7 select a.art_id, 8 date_trunc('week', o.ord_date) as week_start, 9 count(*) as units_sold 10 from orders o 11 join article_unit au on o.ord_id = au.ord_id 12 join unit_price up on au.unit_id = up.unit_id 13 join price p on up.price_id = p.price_id 14 join article a on p.art_id = a.art_id 15 where au.wh_id = 1 16 and o.ord_date between current_date - interval '10 weeks' 17 and current_date - interval '2 week' 18 group by a.art_id, date_trunc('week',o.ord_date) 19 ), 20 AvgSales as ( 21 select art_id, 22 avg(units_sold) as avg_units_sold 23 from WeeklySales 24 group by art_id 25 ), 26 Inventory as ( 27 select a.art_id, count(distinct au.unit_id) as currentStock 32 28 from article_unit au 33 29 join warehouse w on au.wh_id = w.wh_id 34 join article a on au.art_id = a.art_id 30 join unit_price up on au.unit_id = up.unit_id 31 join price p on up.price_id = p.price_id 32 join article a on p.art_id = a.art_id 35 33 where w.wh_id = 1 36 group by a.art_name, w.wh_id 37 order by unitNumber; 34 group by a.art_id 35 order by currentStock 36 ) 37 select i.art_id, 38 a.art_name, 39 i.currentStock, 40 avs.avg_units_sold, 41 (i.currentStock - avs.avg_units_sold) as remaining_after_forecast 42 from inventory i 43 join article a on i.art_id = a.art_id 44 join AvgSales avs on i.art_id = avs.art_id 38 45 }}} 39 46 … … 72 79 {{{#!sql 73 80 with ArticleSales as ( 74 select a u.art_id,81 select a.art_id, 75 82 a.ctg_id, 76 83 w.wh_id, … … 80 87 count(au.unit_id) as unitsSold, 81 88 count(au.unit_id)*p.price as income 82 from article_unit au 83 join article a on au.art_id = a.art_id 89 from article_unit au 90 join unit_price up on up.unit_id = au.unit_id 91 join price p on up.price_id = p.price_id 92 join article a on p.art_id = a.art_id 84 93 join warehouse w on au.wh_id = w.wh_id 85 94 join city c on w.city_id = c.city_id 86 95 join region r on c.region_id = r.region_id 87 join price p on a.art_id = p.art_id88 96 join orders o on au.ord_id = o.ord_id 89 97 where o.ord_date>=p.price_eff_date 90 group by a u.art_id, a.ctg_id, w.wh_id, c.city_id, r.region_id, p.price, p.price98 group by a.art_id, a.ctg_id, w.wh_id, c.city_id, r.region_id, p.price, p.price 91 99 ) 92 100 select r.region_name as region, … … 99 107 group by r.region_name, cat.ctg_name 100 108 order by r.region_name, totalIncome desc; 109 101 110 }}} 102 111 … … 108 117 c.city_name as warehouseCity, 109 118 r.region_name as warehouseRegion, 110 extract(year from o.ord_date) as year,111 extract(quarter from o.ord_date) as quarter,119 extract(year from o.ord_date) as "year", 120 extract(quarter from o.ord_date) as "quarter", 112 121 count(au.unit_id) as totalArticlesSold, 113 122 sum(o.ord_sum) as totalSales … … 118 127 join orders o on au.ord_id = o.ord_id 119 128 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) 120 order by w.wh_id, year,quarter;129 order by w.wh_id,"year","quarter"; 121 130 }}} 122 131 … … 127 136 c.city_name as warehouseCity, 128 137 r.region_name as warehouseRegion, 129 extract(year from o.ord_date) as year,138 extract(year from o.ord_date) as "year", 130 139 sum(o.ord_sum) as totalIncome, 131 140 sum(ord_sum)-sum(au.unit_cost_price) as total_profit … … 136 145 join orders o on au.ord_id = o.ord_id 137 146 group by w.wh_id, c.city_name, r.region_name, o.ord_date 138 order by w.wh_id, year;147 order by w.wh_id,"year"; 139 148 }}} 140 149 … … 142 151 143 152 {{{#!sql 144 with ProductSales as (select a u.art_id,153 with ProductSales as (select a.art_id, 145 154 a.art_name, 146 155 cat.ctg_name as category, … … 150 159 count(au.unit_id) as totalUnitsSold 151 160 from article_unit au 152 join article a on au.art_id = a.art_id 153 join category cat on a.ctg_id = cat.ctg_id 154 join price p on a.art_id = p.art_id 155 join orders o on au.ord_id = o.ord_id 161 join unit_price up on up.unit_id = au.unit_id 162 join price p on up.price_id = p.price_id 163 join article a on p.art_id = a.art_id 164 join category cat on a.ctg_id = cat.ctg_id 165 join orders o on au.ord_id = o.ord_id 156 166 where o.ord_date between '2024-01-01' and '2024-12-31' 157 167 and o.ord_date >= p.price_eff_date 158 group by a u.art_id, a.art_name, cat.ctg_name)168 group by a.art_id, a.art_name, cat.ctg_name) 159 169 select art_id, 160 170 art_name, … … 166 176 from ProductSales 167 177 order by totalProfit desc; 178 168 179 }}}
