Can someone point me in the right direction for this (to me) complex task?
I have a table [Spends] with a unique column ID as the primary key. In this table there are rows for individual spend items containing [Spend Date] (date type) and [Spend Value] (money type).
What I need to do is pass in a [Start Date] and [End Date] and produce a row for each year from [Star Date] to [End Date] with a column for each month ([Jan] to [Dec]) containing a sum of the [Spends].[Spend Value] for all rows that fall in the appropriate month.
For example dates from 2014-05-01 to 2017-08-15, would produce rows for 2014, 2015, 2016, 2017 with a column for each month.
DECLARE @Start_Date date
DECLARE @End_Date date
SET @Start_Date = '20140501'
SET @End_Date = '20170815'
SELECT
YEAR(s.[Spend Date]) AS Year,
SUM(CASE WHEN MONTH(s.[Spend Date]) = 1 THEN s.[Spend Value] ELSE 0 END) AS Jan,
SUM(CASE WHEN MONTH(s.[Spend Date]) = 2 THEN s.[Spend Value] ELSE 0 END) AS Feb,
SUM(CASE WHEN MONTH(s.[Spend Date]) = 3 THEN s.[Spend Value] ELSE 0 END) AS Mar,
--...same for Apr thru Sep...
SUM(CASE WHEN MONTH(s.[Spend Date]) =10 THEN s.[Spend Value] ELSE 0 END) AS Oct,
SUM(CASE WHEN MONTH(s.[Spend Date]) =11 THEN s.[Spend Value] ELSE 0 END) AS Nov,
SUM(CASE WHEN MONTH(s.[Spend Date]) =12 THEN s.[Spend Value] ELSE 0 END) AS [Dec]
FROM Spends s
WHERE
s.[Spend Date] >= @Start_Date AND
s.[Spend Date] < DATEADD(DAY, 1, @End_Date)
GROUP BY YEAR(s.[Spend Date])