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?