I want to make comparisons on the volume of sales, turnover and margin of all product sales between 2 time intervals (2017-10-1 to 2017-10-31 ) and (2018-10-1 to 2018-10-31).
Product Name | Volume 2017 | Volume 2018 | Margin 2017 | Margin 2018 | A | 200 | 100 | 10 | 12 | B | 190 | 120 | 23 | 20 | C | 'no sales' | 100 | 'no sales' | 12 | D | 300 | 'no sales' | 25 | 'no sales' |
My approach is the following:
select /* attempt to get either name if null but doesnt work as I join product ID's*/ if(ifnull(q1.ProductName,false), q2.ProductName , q1.ProductName) as 'Name' ,q1.NumofProductsSold as 'Volume 2017' ,q2.NumofProductsSold as 'Volume 2018' ,format(q1.Margin,2) as 'Margin 2017' ,format(q2.Margin,2) as 'Margin 2018' ,q1.TotalTurnOver as 'Turnover 2017' ,q2.TotalTurnOver as 'Turnover 2018' from ( select format(sum(oi.product_quantity),0) as 'NumofProductsSold' ,format(sum(oi.turnover),2) as 'TotalTurnOver', ,SUM(oi.total_Mark_up) + o_inf.Markup 'Margin' ,order_id ,ifnull(p.name, 'empty') as 'ProductName' ,p.id as 'PID' ,op.date as 'date' FROM order_items oi JOIN products p ON p.id = op.product_id join product_categories pc on p.id = pc.product_id join orders o on op.order_id = o.id join order_info o_inf on o.order_number = o_all.orderNumber where pc.category_id = 1 -- cat A products only AND op.order_status != 'CNL' -- not canceled AND op.date >= '2017-09-01' AND op.date <= '2017-10-31' group by p.id) as q1 left join ( Select /* same query BUT for 2018 date range */ ) as q2 on q1.PID = q2.PID -- join on product ID
My issue is that if a product doesn't have any sales in either/both the first or second time interval, I get only the rows that correspond on both, so I always "miss" some products if they have sales in one time interval and not the other.
With left/right join I can get correct results only if either set is a subset of the other respectively, i.e if a product ID appears in the right and not the left it doesn't appear with left join. I tried UNION it works BUT it doesn't produce the outcome in specified in the description above. Is there a workaround based on how I designed the Query or should I follow a different approach?
All suggestions-corrections-critique more than welcome! Thanks!