locked
Calculate year over year RRS feed

  • Question

  • Hello,

    I am getting last four weeks qty. But I am trying to pull Last year same 4 weeks qty with weekID for Same ID and Name.

    I need to show last year qty beside current year qty. 

    Select 
    NAMEID,Id,QTY AMT,DATE,Datepart(Week,CBG.[DATE]) As WeekNumber

    From SalesTable

    Where Date Between dateadd(week,-4,getdate()) and dateadd(week,-0,getdate())--LAST 4 weeks  


    Result : I want to have 2 colums  for QTY

    NAMEID   ID    QTY   Last year qty       AMT        DATE            Weeknumber
    ABC        1      22          2                     222      2019-04-27        17
    BCD        1      11                           120       2019-04-27         17
    DCD        1     111       Null                 1120     2019-04-27         17
    ABC        1       2                            22       2019-05-27          18
    BCD        1      25         66                  29        2019-05-27          18

    How can I do this in SQL?

    I tried rejoining the same table but getting Duplicates.

    Help Plzz...

     





    • Edited by Victory5 Tuesday, May 21, 2019 4:18 PM
    Monday, May 20, 2019 11:40 PM

Answers

  • Hi Victory5,

     

    Please try following script.

     
    select b.NAMEID,b.ID,b.QTY,a.QTY [Last year qty],b.AMT,b.DATE,b.WeekNumber
    From SalesTable a
    right join 
    	(Select NAMEID,Id,QTY AMT,DATE,Datepart(Week,[DATE]) As WeekNumber
    	From SalesTable
    	Where Date Between dateadd(week,-4,getdate()) and dateadd(week,-0,getdate())) b
    on a.ID =b.ID and a.NAMEID=b.NAMEID 
    where b.WeekNumber =Datepart(Week,a.[DATE]) 
    and year(a.[DATE])=year(getdate())-1
    

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Victory5 Wednesday, May 22, 2019 8:39 PM
    Wednesday, May 22, 2019 6:27 AM

All replies

  • Hi Victory5,

     

    Please try following script. If it still not satisfies your requirement, please let me know .

     
    select NAMEID,Id,QTY AMT,DATE,Datepart(Week,[DATE]) As WeekNumber
    From SalesTable a
    where exists 
    	(Select 1
    	From SalesTable
    	Where Date Between dateadd(week,-4,getdate()) and dateadd(week,-0,getdate())
    	and a.ID =ID and Datepart(Week,[DATE]) =Datepart(Week,a.[DATE]) )  
    and year([DATE])=year(getdate())-1
    

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 21, 2019 2:06 AM
  • Hi Rachel,

    I need to show both the columns side by side (Current year/week and Last Year/week QTY).

    Tuesday, May 21, 2019 3:59 PM
  • Hi Victory5,

     

    Please try following script.

     
    select b.NAMEID,b.ID,b.QTY,a.QTY [Last year qty],b.AMT,b.DATE,b.WeekNumber
    From SalesTable a
    right join 
    	(Select NAMEID,Id,QTY AMT,DATE,Datepart(Week,[DATE]) As WeekNumber
    	From SalesTable
    	Where Date Between dateadd(week,-4,getdate()) and dateadd(week,-0,getdate())) b
    on a.ID =b.ID and a.NAMEID=b.NAMEID 
    where b.WeekNumber =Datepart(Week,a.[DATE]) 
    and year(a.[DATE])=year(getdate())-1
    

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Victory5 Wednesday, May 22, 2019 8:39 PM
    Wednesday, May 22, 2019 6:27 AM
  • Thank you for the response.working great ...


    • Edited by Victory5 Wednesday, May 22, 2019 8:39 PM
    Wednesday, May 22, 2019 5:46 PM