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'
FROM dbo.tblTranWIP
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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.