SQL Server How to get all records from previous year

edqdpe6u 于 2023-06-28 发布在其他

关注(0)|答案(1)|浏览(114)

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

sql-server

来源：https://stackoverflow.com/questions/76517127/how-to-get-all-records-from-previous-year

1条答案

按热度按时间

aurhwmvo1#

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.

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server How to get all records from previous year

1条答案

相关问题

热门标签

最新问答