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?
Mark WilsonBI DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ste5anSenior DeveloperCommented:
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 WilsonBI DeveloperAuthor Commented:
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
ste5anSenior DeveloperCommented:
Well, I can:

DECLARE @Matters TABLE ( WorkType INT, OpenDate DATE, CloseDate DATE );

INSERT INTO @Matters
VALUES	( 1, '19960101', '19960101' ),
	( 1, '19960101', '19960401' ),
	( 2, '19960101', '19960101' ),
	( 2, '19960101', '19960401' ),
	( 2, '19960101', '19960801' );

WITH Calendar AS
	(
		SELECT	CAST('19950501' AS DATE) AS DayDate
		UNION ALL
		SELECT	DATEADD(MONTH, 3, DayDate)
		FROM	Calendar
		WHERE	DayDate < '19970430'
	),
	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	Q.*,
		M.*,
		CASE WHEN M.CloseDate > Q.FiscalQuarterEndDate 
			THEN 1 
			ELSE 0 
		END AS IsOpen
	FROM	Quarters Q
		INNER JOIN @Matters M ON M.OpenDate <= Q.FiscalQuarterEndDate and M.CloseDate >= Q.FiscalQuarterStartDate
	OPTION ( MAXRECURSION 1000);

Open in new window

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
Jim HornMicrosoft SQL Server Data DudeCommented:
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 WilsonBI DeveloperAuthor Commented:
Jim - been ill so just got to this, thanks very much this is a very useful article
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.