| Author |
 Topic  |
|
|
parallaxman
Starting Member
3 Posts |
 Posted - 07/23/2012 : 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
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 07/23/2012 : 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
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 07/23/2012 : 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 - 07/23/2012 : 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
India
47189 Posts |
Posted - 07/23/2012 : 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 - 07/24/2012 : 15:16:14
|
| Thanks visakh16, I get the method. Very cool. |
|
|
| |
Topic |
|
|
|
How To Group Records By Values that are NOT data?
