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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tim HumphriesDirectorCommented:
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
Chris AllenIT 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.