asked on

SQL - Grouping by Quarters

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]
from Matter_T
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?

ste5an

You need a calendar table. E.g.

WITH Calendar AS
	(
		SELECT	CAST('19950501' AS DATE) AS DayDate
		UNION ALL
		SELECT	DATEADD(MONTH, 3, DayDate)
		FROM	Calendar
		WHERE	DayDate < '20991231'
	),
	Quarters AS
	(
		SELECT	DayDate AS FiscalQuarterStartDate,
		        DATEADD(DAY, -1, DATEADD(MONTH, 3, DayDate)) AS FiscalQuarterEndDate,
			CASE MONTH(DayDate) 
				WHEN 5 THEN 1
				WHEN 8 THEN 2
				WHEN 11 THEN 3
				WHEN 2 THEN 4
			END AS FiscalQuarter,
			CASE WHEN MONTH(DayDate) IN ( 1, 2 ) 
				THEN YEAR(DayDate) - 1 
				ELSE YEAR(DayDate) 
			END AS FiscalYear  
		FROM	Calendar
	)
	SELECT	*
	FROM	Quarters
	OPTION ( MAXRECURSION 1000);

Open in new window

Now you can join in your matters table and group by fiscal year and fiscal quarter.

Mark Wilson

ASKER

Thanks for the answer

I don't think it will work in this case

I am counting the matters that were still open at the end of that quarter, which is different from the number opened in that quarter. I am using both the open and closed date to work it out, see previous code. So I cannot join to the dates table

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

halifaxman - I just kicked out an article that deals specifically with Fiscal calendar planning --> SQL Server Calendar Table: Fiscal Years. Let me know if this helps you, and if yes please click on the 'Good Article' button and provide some feedback. Thanks.

Mark Wilson

ASKER

Jim - been ill so just got to this, thanks very much this is a very useful article