I am trying to graph fees information over 3 financial years.
I can produce the figures, but only for calendar years - the select is shown below
SELECT TOP 100 PERCENT YEAR(WIPDate) AS 'Year', MONTH(WIPDate) AS 'Month', SUM(WIPAmount) * - 1 AS 'FeeAmount'
WHERE (TransTypeIndex IN (3, 4, 6, 14)) AND (WIPDate BETWEEN CONVERT(DATETIME, '2007-04-01', 102) AND CONVERT(DATETIME, '2010-03-31', 102))
GROUP BY YEAR(WIPDate), MONTH(WIPDate)
ORDER BY YEAR(WIPDate), MONTH(WIPDate)
this gives me the data in the correct layout for an excel pivot table - but I cant workout how to include datediff statements (or similar) to make the data appear in Financial year series.
Our financial year runs 1st April to 31 March.