Using SQL 2008
I am trying to develop a query that counts matters that were open at the end of each quarter.
The financial year runs from 1st May to 30th April of the next year
The quarters would be
Q1 – 1st May- 31st July
Q2 – 1st August – 31st October
Q3 - 1st November to 31st January
Q4 – 1st Feb to April 30th
There is data from the year 1996 onwards
At the moment the query is something like this
SELECT worktype , sum(case when opendate < '1996-08-01 00:00:00.000' and AND (closedate > '1996-08-01 00:00:00.000' OR closedate IS NULL) then 1 end) [Q1 -1996]
group by worktype
There are case statements for each quarter to the present quarter, as you can imagine this is unwieldy and I have to keep adding new code when the next quarter comes along
Is there a better way of doing this?