Search code examples
mysqlsubqueryself-join

SQL Self Join / Sub Query

I got stuck in the below query, Tables as follows. wktest(Store, dt, sale) Cal (dt, fin_yr, wk)

Note: wktest is a sale table which has last year & current year, I want to get result of Last year's sale and current year's sale by week with following key

Current year's WK number + weekday = Last year's WK number + weekday

select w.Store,c.Fin_Yr ,c.Wk, sum(w.sale)
        from wktest w
        join cal c on w.dt = c.dt
        where
        concat(c.wk,weekday(w.dt)) = 
        
        (select concat(cal.wk,weekday(wktest.dt)) from cal join wktest on cal.dt = wktest.dt where wktest.dt between '2019-04-01' and '2020-03-31')
        
        group by w.Store,c.Fin_Yr ,c.Wk;

Sharing Sample tables & expected output enter image description here

Solution

  • Test, maybe this query is safe for you?

    SELECT wk, SUM(FY20.sale) FY20_sale, SUM(FY22.sale) FY22_sale
FROM ( SELECT dt, cal.wk, wktest.sale
       FROM wktest
       JOIN cal USING (dt)
       WHERE cal.fin_yr = 'FY20' ) FY20
JOIN ( SELECT dt, cal.wk, wktest.sale
       FROM wktest
       JOIN cal USING (dt)
       WHERE cal.fin_yr = 'FY22' ) FY22 USING (wk)
WHERE WEEKDAY(FY20.dt) = WEEKDAY(FY22.dt)
GROUP BY wk

    The query assimes that dt column values are unique in each separate table.