Chris Allen
asked on
Convert SQL select to use financial year
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for that - but it's the actual syntax where I am struggling - how and where do I add the DATEADD sequence to the query?