Sum Date based rows into Row per year and Column per Month

Transact-SQL
1

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.

This is for MS SQL 2008

Hopefully someone can help?

Thanks

Phil

2 
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])
1 Like
3

Many thanks Scot, just want I needed :smile:

Phil