# Calculate year over year

• ### 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

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 Tuesday, May 21, 2019 4:18 PM
Monday, May 20, 2019 11:40 PM

• Hi Victory5,

```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
on a.ID =b.ID and a.NAMEID=b.NAMEID
where b.WeekNumber =Datepart(Week,a.[DATE])
and year(a.[DATE])=year(getdate())-1
```

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 Wednesday, May 22, 2019 8:39 PM
Wednesday, May 22, 2019 6:27 AM

### All replies

• Hi Victory5,

```select NAMEID,Id,QTY AMT,DATE,Datepart(Week,[DATE]) As WeekNumber
From SalesTable a
where exists
(Select 1
From SalesTable
and a.ID =ID and Datepart(Week,[DATE]) =Datepart(Week,a.[DATE]) )
and year([DATE])=year(getdate())-1
```

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,

```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
on a.ID =b.ID and a.NAMEID=b.NAMEID
where b.WeekNumber =Datepart(Week,a.[DATE])
and year(a.[DATE])=year(getdate())-1
```

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 Wednesday, May 22, 2019 8:39 PM
Wednesday, May 22, 2019 6:27 AM
• Thank you for the response.working great ...

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