0

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).

for example:

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!

0

Algorythm:

SELECT product_name,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2017-10-1' AND '2017-10-31' 
                         THEN volume END), 'no sale') AS volume2017,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2018-10-1' AND '2018-10-31' 
                         THEN volume END), 'no sale') AS volume2018,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2017-10-1' AND '2017-10-31' 
                         THEN margin END), 'no sale') AS margin2017,
       COALESCE(SUM(CASE WHEN `date` BETWEEN '2018-10-1' AND '2018-10-31' 
                         THEN margin END), 'no sale') AS margin 2018
FROM datatable
GROUP BY product_name
/* WHERE `date` BETWEEN '2017-10-1' AND '2017-10-31'
      OR `date` BETWEEN '2018-10-1' AND '2018-10-31' */
  • tanks a lot man! – Artemis Nov 14 '18 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.