May 12, 2009 at 4:35 am
Hi all,
I am facing a problem at work atm, where I have a requirement to produce a report for the call centre.
The data looks as follows (copied from http://www.sqlservercentral.com/articles/Best+Practices/61537/ thanks Jeff ;):
--===== If the tale exists, drop it
IF OBJECT_ID('[tempdb]..#TestCalls', 'U') IS NOT NULL
DROP TABLE #TestCalls
--===== Create the table
CREATE TABLE #TestCalls
(
callIDINT,
createDateDATETIME,
statusNVARCHAR(11)
)
SET DATEFORMAT MDY
--===== Insert the test data
INSERT INTO #TestCalls
(callID, CreateDate, status)
SELECT '2','Jan 2 2008 12:00AM','New Rentals' UNION ALL
SELECT '5','Jan 2 2008 12:00AM','New Rentals' UNION ALL
SELECT '7','Jan 3 2008 12:00AM','New Cases' UNION ALL
SELECT '9','Jan 3 2008 12:00AM','New Cases' UNION ALL
SELECT '10','Jan 3 2008 12:00AM','New Rentals' UNION ALL
SELECT '12','Feb 4 2008 12:00AM','New Rentals' UNION ALL
SELECT '13','Feb 7 2008 12:00AM','New Cases' UNION ALL
SELECT '14','Feb 9 2008 12:00AM','New Cases' UNION ALL
SELECT '15','Feb 1 2008 12:00AM','New Cases' UNION ALL
SELECT '18','Mar 1 2008 12:00AM','New Rentals' UNION ALL
SELECT '20','Mar 8 2008 12:00AM','New Cases' UNION ALL
SELECT '21','Mar 9 2008 12:00AM','New Rentals' UNION ALL
SELECT '22','Apr 4 2008 12:00AM','New Cases' UNION ALL
SELECT '23','Apr 4 2008 12:00AM','New Cases' UNION ALL
SELECT '24','Apr 4 2008 12:00AM','New Cases' UNION ALL
SELECT '25','Apr 4 2008 12:00AM','New Cases' UNION ALL
SELECT '26','Apr 4 2008 12:00AM','New Cases' UNION ALL
SELECT '27','Apr 4 2008 12:00AM','New Cases' UNION ALL
SELECT '29','Apr 7 2008 12:00AM','New Rentals' UNION ALL
SELECT '30','Jun 7 2008 12:00AM','New Rentals' UNION ALL
SELECT '31','Jun 7 2008 12:00AM','New Cases' UNION ALL
SELECT '32','Jun 7 2008 12:00AM','New Cases' UNION ALL
SELECT '33','Jun 7 2008 12:00AM','New Cases' UNION ALL
SELECT '34','Jun 7 2008 12:00AM','New Cases' UNION ALL
SELECT '35','Jun 7 2008 12:00AM','New Cases' UNION ALL
SELECT '36','Jun 7 2008 12:00AM','New Cases' UNION ALL
SELECT '37','Jun 7 2008 12:00AM','New Cases' UNION ALL
SELECT '38','Jun 7 2008 12:00AM','New Cases' UNION ALL
SELECT '39','Jun 7 2008 12:00AM','New Cases' UNION ALL
SELECT '40','Jun 7 2008 12:00AM','New Cases'
Now normaly I would just use Crystal Reports for the complicated stuff or write a simple query for the easy stuff, but due to some politics I can only use a query for this one. Now the requirement is a little above my level of knowledge with queries.
After searching the site I decided that PIVOT / Cross-tab queries (thanks to Jeff's Cross Tabs and Pivots, Part 1 / Part 2) is probably the way to go.
Anyways enough chatter, let me tell you the problem I am facing. I have the follwing query that has me halfway there, but I am lost as to what to do next.
SELECT CASE WHEN GROUPING(CAST(status AS VARCHAR)) = 1 THEN 'Total' ELSE CAST(status AS VARCHAR) END,
SUM(CASE WHEN MonthDate = 'Jan 2008' THEN Total ELSE 0 END) AS [Jan 2008],
SUM(CASE WHEN MonthDate = 'Feb 2008' THEN Total ELSE 0 END) AS [Feb 2008],
SUM(CASE WHEN MonthDate = 'Mar 2008' THEN Total ELSE 0 END) AS [Mar 2008],
SUM(CASE WHEN MonthDate = 'Apr 2008' THEN Total ELSE 0 END) AS [Apr 2008],
SUM(CASE WHEN MonthDate = 'May 2008' THEN Total ELSE 0 END) AS [May 2008],
SUM(CASE WHEN MonthDate = 'Jun 2008' THEN Total ELSE 0 END) AS [Jun 2008],
SUM(CASE WHEN MonthDate = 'Jul 2008' THEN Total ELSE 0 END) AS [Jul 2008],
SUM(CASE WHEN MonthDate = 'Aug 2008' THEN Total ELSE 0 END) AS [Aug 2008],
SUM(Total) AS Total
FROM (--==== Derived table "d" does preaggregation by whole month
SELECT DATEADD(mm,DATEDIFF(mm,0,createDate),0) AS MonthDate,
status,
COUNT(callID) AS Total
FROM #TestCalls
WHERE createDate >= 'Jan 1 2008 12:00AM'
AND createDate < 'Sep 1 2008 12:00AM'
GROUP BY DATEADD(mm,DATEDIFF(mm,0,createDate),0), status
) d
GROUP BY CAST(status AS VARCHAR) WITH ROLLUP
[/code]
It produces the following :
[code]
Jan 2008 Feb 2008 Mar 2008 Apr 2008 May 2008 Jun 2008 Jul 2008 Aug 2008 Total
------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
New Cases 2 3 1 6 0 10 0 0 22
New Rentals 3 1 2 1 0 1 0 0 8
Total 5 4 3 7 0 11 0 0 30
[/code]
Now what I would like to know is :
a. Is it possible to add YTD under the Month columns i.e. Feb = Jan + Feb, Mar = Jan + Feb + Mar as another row ?
Any advice/help would be greatly appreciated. If I am missing something obvious please feel free to slap me on the ear 😛
May 12, 2009 at 6:23 am
One way I can think of now is to do a UNION ALL of "YTD".
; WITH TestCalls
AS
(
SELECTDATEADD( MONTH, DATEDIFF( MONTH, 0, createDate ), 0 ) AS MonthDate, [status],
COUNT( * ) AS Total
FROM#TestCalls
WHEREcreateDate >= '01-Jan-2008'
AND createDate = '01-Jan-2008' AND MonthDate = '01-Jan-2008' AND MonthDate = '01-Jan-2008' AND MonthDate = '01-Jan-2008' AND MonthDate = '01-Jan-2008' AND MonthDate = '01-Jan-2008' AND MonthDate = '01-Jan-2008' AND MonthDate < '01-Sep-2008' THEN Total ELSE 0 END ) ) AS [Aug 2008],
SUM( Total ) AS Total
FROMTestCalls
--Ramesh
May 12, 2009 at 6:45 am
Thanks, that was exactly what I needed to do.
May 12, 2009 at 6:58 am
Thanks, that was exactly what I needed to do. SSC is the best, I learn something new every time I come here 🙂
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply