| 6 | | with latestPrice as ( |
| 7 | | select p.art_id,p.price as latest_price |
| 8 | | from price p |
| 9 | | where p.price_eff_date = ( |
| 10 | | select max(price_eff_date) |
| 11 | | from price |
| 12 | | where p.art_id=p.art_id |
| 13 | | ) |
| 14 | | ) |
| 15 | | select c.cust_company_name as customerName, |
| 16 | | c.user_id as customerId, |
| 17 | | a.art_name as articleName, |
| 18 | | count(au.unit_id) as quantity, |
| 19 | | lp.latest_price as unitPrice, |
| 20 | | count(au.unit_id)*lp.latest_price as total_spent, |
| 21 | | sum(o.ord_sum) as orderTotal |
| 22 | | from orders o |
| 23 | | join article_unit au on o.ord_id = au.ord_id |
| 24 | | join article a on au.art_id = a.art_id |
| 25 | | join customer c on o.cust_id = c.user_id |
| 26 | | join latestPrice lp on a.art_id=lp.art_id |
| 27 | | where o.ord_date between '2024-01-01' and '2024-12-31' |
| 28 | | group by c.cust_company_name,c.user_id,a.art_name,lp.latest_price |
| 29 | | order by c.cust_company_name,a.art_name; |
| | 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; |
