I have a column Amount
which I want to get its sum by month within each year.
I have tried the following query and it works well for year but month is summing up incorrectly. For instance in this May, it's summing up all the month of May in various years. So I want it to sum each current month at a time.
DECLARE @currentYear int
DECLARE @currentMonth int
SELECT @currentYear = DATEPART(year, GETDATE())
SELECT @currentMonth = DATEPART(month, GETDATE())
SELECT
SUM(
CASE
WHEN DATEPART(month,[Date]) = @currentMonth THEN Amount
ELSE 0
END
) AS 'This Month',
SUM(
CASE
WHEN DATEPART(year,[Date]) = @currentYear THEN Amount
ELSE 0
END
) AS 'This Year'
FROM Orders
WHEN DATEPART(month,[Date]) = @currentMonth AND DATEPART(year,[Date]) = @currentYear
where
[date] > start and [date] <= end, it would be far more performant than scanning all rows.