How to get difference of sales pre and post 12 weeks in SQL?-CodePudding

I have data like below

category    date        sales
chocs       2022-09-02    20
biscuits    2022-09-02    90
popcorn     2022-09-02    45
popcorn     2021-09-02    85
chocs       2021-09-02    35
biscuits    2021-09-02    75

I'm trying to get the sales of 12 pre and post weeks for every category. For example sales of chocs category on 2022-09-02 is 20. I want to get 12 pre and post weeks sales for same. Expected output is

 category    date          sales   12_weeks_before_sales                  12_weeks_after_sales
 chocs       2022-09-02    20      sales of 12 weeks before date i.e. 2022-09-02
 biscuits    2022-09-02    90      sales of 12 weeks before date i.e. 2022-09-02
 popcorn     2022-09-02    45      sales of 12 weeks before date i.e. 2022-09-02
 popcorn     2021-09-02    85      sales of 12 weeks before date i.e. 2021-09-02
 chocs       2021-09-02    35      sales of 12 weeks before date i.e. 2021-09-02
 biscuits    2021-09-02    75      sales of 12 weeks before date i.e. 2021-09-02

I have the complete data before that date, but unable to write query. Can anyone help me with this?

CodePudding user response：

Try this, hope it helps.

select t.category,
       t.saledate,
       sum(t.sales) current_sales,
       p.pastdate past_date,
       p.pastsales past_sales
  from sales t
  join (select t1.category,
               t1.saledate pastdate,
               sum(t1.sales) pastsales
          from sales t1 
         where t1.saledate = DATEADD(week, -12, t.saledate)
           and t1.category = t.category) p 
 group by 1,2,4,5