September 14, 2011 at 2:26 pm
WITH Casesubtype AS
( SELECT C.CASETYPE, DatePart(q,C.CREATIONDATE) AS numberofdays, C.CASESUBTYPE,T.LONGDESC FROM CASETABLE C
LEFT OUTER JOIN CASESUBTYPETABLE T ON C.CASESUBTYPE = T.CASESUBTYPE
/*THIS IS OTHER WAY TO GET COUNTS FROM THE DAY BEFORE FROM CURRENT DATE */
WHERE (C.CREATIONDATE BETWEEN CONVERT(VARCHAR(10), GETDATE()-1, 120) + ' 00:00:00.000'
AND CONVERT(VARCHAR(10), GETDATE()-1, 120) + ' 23:59:59.000')
AND C.CASETYPE in ('WCONT', 'WTSCO')
AND C.ROWADDEDOPRID <> 'User1'
AND (T.CASETYPE = C.CASETYPE AND T.CASESUBTYPE = C.CASESUBTYPE)
)
SELECT CASESUBTYPE,LONGDESC,
[1] AS Day1,
[2] AS Day2,
[3] AS Day3,
[4] AS Day4,
[5] AS Day5,
[6] AS Day6,
[7] AS Day7
FROM Casesubtype
PIVOT (COUNT(CASETYPE) FOR numberofdays IN ([1],[2],[3],[4],[5],[6],[7])) AS ResultSet
Dataset with normal SQL look like for day (09/14/11)
Casesubtype Longdescription Totalcount
WCONT WaterContinue 5
WCODS Hardsurface 6
I ‘ m trying to pull the data in cross tab SQL using above pivot statement
Casesubtype Longdescription 09/07/1109/08/1109/09/11
WCONT WaterContinue 201
WCODS Hardsurface 327
For past seven days. Can someone please guide how to modified above the pivot statement to achieve past seven days total count .
September 24, 2011 at 3:31 pm
Check out WITH Rollup, it works like Pivot... the results may be more desireable.
SELECT CASESUBTYPE,LONGDESC,
[1] AS Day1,
[2] AS Day2,
[3] AS Day3,
[4] AS Day4,
[5] AS Day5,
[6] AS Day6,
[7] AS Day7
FROM Casesubtype
GROUP BY CASESUBTYPE,LONGDESC With Rollup
September 25, 2011 at 2:06 am
Or turn the pivot into a classic CrossTab (as described in the related link in my signature).
It's much more flexible and seems to be faster, too.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply