How to get all records from previous year

Question

My code is producing current year's rows instead of previous year's rows in the last year column

DECLARE @prevYear int
DECLARE @prevMonth int

SELECT @prevYear = DATEPART(year, GETDATE() - 1)
SELECT @prevMonth = DATEPART(month, GETDATE() - 30)

SELECT  
    SUM(CASE 
            WHEN DATEPART(month, [OrderDate]) = @prevMonth  
                THEN Amount 
                ELSE 0 
        END) AS 'LastMonth',    
    SUM(Amount) AS 'LastYear'
FROM 
    Orders
INNER JOIN 
    OrderDetail ON Orders.OrderID = OrderDetail.OrderID 
WHERE 
    DATEPART(year, [OrderDate]) = @prevYear

I'm expecting to get previous year (2022) row in last year column

FYI, syntax like WHERE DATEPART(year, [OrderDate]) = @prevYear is not SARGable. Use date ranges, don't use functions on your columns. — Thom A
– Thom A ♦, Commented Jun 20, 2023 at 17:49

Thom A · Accepted Answer · 2023-06-20 18:05:59Z

0

You would be far better off working out the year and then using DATEFROMPARTS for a SARGable clause:

DECLARE @prevYear int;
DECLARE @prevMonth int;

SELECT @prevYear = DATEPART(YEAR, GETDATE())-1;
SELECT @prevMonth = DATEPART(MONTH, DATEADD(MONTH,-1,GETDATE()));

SELECT SUM(CASE WHEN DATEPART(month,O.OrderDate) = @prevMonth THEN Amount ELSE 0 END) AS LastMonth,  --Dont use single quote aliases
       SUM(OD.Amount) AS LastYear
FROM Orders O
INNER JOIN dbo.OrderDetail OD ON O.OrderID = OD.OrderID 
WHERE O.OrderDate >= DATEFROMPARTS(@PrevYear,1,1) --Alias guessed
  AND O.OrderDate < DATEFROMPARTS(@PrevYear+1,1,1);

All column qualifiers are assumed.

answered Jun 20, 2023 at 18:05

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

User_K47 Over a year ago

I've applied your codes but it didn't work. So I have modified my codes as SELECT @prevYear = DATEPART(year, GETDATE() - 360) and it works fine

Thom A Over a year ago

That isn't a year though... What does "doesn't work" mean, as they won't work on a date like 2023-12-29.

Collectives™ on Stack Overflow

How to get all records from previous year

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related