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.
Chris AllenIT ManagerAsked:
Who is Participating?
 
Chris AllenConnect With a Mentor IT ManagerAuthor Commented:
In the end, I created a Financial Year lookup table. I got the number of days between 1April 2005 and 31March 2030 and created a table with columns for day of week, quarters, name of day, FY_Month, FY_Quarter and FY_Year. It is then a simple matter to update the FY_Year, FY_Month and FY_Quarter to the correct values.

To get the results I needed, I then joined my FY table to the query tables and all is well.

0
 
Tim HumphriesConnect With a Mentor DirectorCommented:
If your 2009-2010 year starts April 2009 then it is 3 months 'behind' the calendar year.
So you should be able to replace each instance of WIPDate in your query with DATEADD(mm, 3, WIPDate)
0
 
Chris AllenIT ManagerAuthor Commented:
Tim,

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?
0
All Courses

From novice to tech pro — start learning today.