How To Group Records By Values that are NOT data? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How To Group Records By Values that are NOT data?

Author

Topic

parallaxman
Starting Member

3 Posts

Posted - 2012-07-23 : 16:38:40

I'm very new to SQL, using SQL Server 2005 and this is my first question posted to this forum. I have a request for a report from an end user that I do not understand how to structure (Note that I have rights to read all tables but not create tables, insert records or alter tables):

There are currently two reports available to an end user at a law firm:

Report A: groups legal case records and simply counts them according to the year and month of a date field using the DATEPART function. The field represents the date a case was opened. The date range is based on report params <startdate> and <enddate> that are used in the WHERE clause:

WHERE cases.caseopened BETWEEN <startdate> AND <enddate>

Result:
Year Month Opened
2011 11 7
2012 1 12
2012 3 5

There were no cases opened in Dec, 2011 or Feb, 2012.

Report B: does exactly the same thing, but the field represents the date a case was closed.

Result:
Year Month Closed
2011 11 1
2012 12 8
2012 1 3
2012 3 6

Note that there were none closed in Feb, 2012.

They would like a single report that counts cases opened and cases closed each month within a date range they specify, but would not only like to see a count of cases opened and cases closed, but row headers showing each year and quarter of the report:

Result:
Year Month Opened Closed
2011 11 7 1
2011 12 0 8
2012 1 12 3
2012 2 0 0
2012 3 5 6

My confusion is that the row headings for grouping aren't data. They're just the year/month within an arbitrary timeframe. So given a start and end date parameter, how does one group by each year and month within those dates? I understand how to count records using the CASE function.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-23 : 18:22:23

Create a date (or number) table or function or cte, and use that, combined with the date range, to produce a set up values. Then use that set LEFT joined to your results.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-23 : 18:32:30

Here is a quick example:

DECLARE @StartDate DATE = '2011-11-01';
DECLARE @EndDate DATE  = '2012-03-01';

DECLARE @Results TABLE (CaseYear SMALLINT, CaseMonth TINYINT, OpenedCount INT)
INSERT @Results
VALUES
(2011, 11, 7),
(2012, 1, 12),
(2012, 3, 5);



WITH Cte AS
(
	SELECT @StartDate AS MyDate
	UNION ALL
	SELECT DATEADD(MONTH, 1, MyDate) 
	FROM Cte WHERE MyDate < @EndDate
)
SELECT 
	YEAR(MyDate) AS [Year]
	,MONTH(MyDate) AS [Month]
	,COALESCE(r.OpenedCount, 0) AS Opened
FROM 
	Cte
LEFT OUTER JOIN
	@Results AS r
	ON r.CaseYear = YEAR(MyDate)
	AND r.CaseMonth = MONTH(MyDate)

parallaxman
Starting Member

3 Posts

Posted - 2012-07-23 : 19:29:05

quote:

Originally posted by Lamprey

Here is a quick example:

DECLARE @StartDate DATE = '2011-11-01';
DECLARE @EndDate DATE  = '2012-03-01';

DECLARE @Results TABLE (CaseYear SMALLINT, CaseMonth TINYINT, OpenedCount INT)
INSERT @Results
VALUES
(2011, 11, 7),
(2012, 1, 12),
(2012, 3, 5);



WITH Cte AS
(
	SELECT @StartDate AS MyDate
	UNION ALL
	SELECT DATEADD(MONTH, 1, MyDate) 
	FROM Cte WHERE MyDate < @EndDate
)
SELECT 
	YEAR(MyDate) AS [Year]
	,MONTH(MyDate) AS [Month]
	,COALESCE(r.OpenedCount, 0) AS Opened
FROM 
	Cte
LEFT OUTER JOIN
	@Results AS r
	ON r.CaseYear = YEAR(MyDate)
	AND r.CaseMonth = MONTH(MyDate)

Lamprey, thanks for the suggestion. As I mentioned, I'm new to SQL so the use of CTE's is a bit challenging but I get the idea. Because you were just presenting me with an example to help me along I understand that I have to fill in the blanks, so to speak, however I don't understand how to compute the values that appear in the INSERT...VALUES section. It appears that you've hardcoded the years, months and numbers. Do I use an INSERT followed by a SELECT clause instead of a VALUES clause?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 21:05:17

that INSERT was just a sample data for your illustration. You need to concentrate only on CTE part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

parallaxman
Starting Member

3 Posts

Posted - 2012-07-24 : 15:16:14

Thanks visakh16, I get the method. Very cool.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources