January 29, 2009 at 3:03 pm
Hello ALL,
In my table have multiple columns such as DDS,DEClearedDate, SomaticMCClearedDate, PsycMCClearedDate, DESecondClearedDate, SomaticMCSecondClearedDate, PsycMCSecondDate, DEThirdClearedDate, SomaticMCThirdClearedDate, PsycMCThirdClearedDate, DEFourthClearedDate, SomaticMCFourthClearedDate, PsycMCFourthClearedDate. ALL columns contains Date format EXCEPT one column that contains STRING and that column is DDS. In my DDS column, it contain BO, NH, CT.
I wrote scripts to count number of cases for each office has been cleared.
WITH CTE AS
(SELECT COALESCE (a.DDS, b.DDS, c.DDS, d.DDS, e.DDS, f.DDS, g.DDS, h.DDS, i.DDS, j.DDS, k.DDS, l.DDS) AS BODDS, ISNULL(a.DECleared, 0) + ISNULL(b.SomaticMCCleared, 0) + ISNULL(c.PsycMCCleared, 0) + ISNULL(d.DESecondCleared, 0)
+ ISNULL(e.SomaticMCSecondCleared, 0) + ISNULL(f.PsycMCSecondCleared, 0) + ISNULL(g.DEThirdCleared, 0)
+ ISNULL(h.SomaticMCThirdCleared, 0) + ISNULL(i.PsycMCThirdCleared, 0) + ISNULL(j.DEFourthCleared, 0)
+ ISNULL(k.SomaticMCFourthCleared, 0) + ISNULL(l.PsycMCFourthCleared, 0) AS BOCLEARED,
COALESCE (a.DEClearedDate, b.SomaticMCClearedDate, c.PsycMCClearedDate, d.DESecondClearedDate, e.SomaticMCSecondClearedDate, f.PsycMCSecondClearedDate, g.DEThirdClearedDate, h.SomaticMCThirdClearedDate, i.PsycMCThirdClearedDate, j.DEFourthClearedDate, k.SomaticMCFourthClearedDate, l.PsycMCFourthClearedDate) AS BOCLEAREDDATE
FROM dbo.BODECleared AS a FULL OUTER JOIN
dbo.BOSomaticMCCleared AS b ON b.SomaticMCClearedDate = a.DEClearedDate FULL OUTER JOIN dbo.BOPsycMCCleared AS c ON c.PsycMCClearedDate = b.SomaticMCClearedDate FULL OUTER JOIN
dbo.BODESecondCleared AS d ON d.DESecondClearedDate = c.PsycMCClearedDate FULL OUTER JOIN
dbo.BOSomaticMCSecondCleared AS e ON e.SomaticMCSecondClearedDate = d.DESecondClearedDate FULL OUTER JOIN dbo.BOPsycMCSecondCleared AS f ON f.PsycMCSecondClearedDate = e.SomaticMCSecondClearedDate FULL OUTER JOIN dbo.BODEThirdCleared AS g ON g.DEThirdClearedDate = f.PsycMCSecondClearedDate FULL OUTER JOIN dbo.BOSomaticMCThirdCleared AS h ON h.SomaticMCThirdClearedDate = g.DEThirdClearedDate FULL OUTER JOIN dbo.BOPsycMCThirdCleared AS i ON i.PsycMCThirdClearedDate = h.SomaticMCThirdClearedDate FULL OUTER JOIN dbo.BODEFourthCleared AS j ON j.DEFourthClearedDate = i.PsycMCThirdClearedDate FULL OUTER JOIN dbo.BOSomaticMCFourthCleared AS k ON k.SomaticMCFourthClearedDate = j.DEFourthClearedDate FULL OUTER JOIN dbo.BOPsycMCFourthCleared AS l ON l.PsycMCFourthClearedDate = k.SomaticMCFourthClearedDate)
SELECT MAX(BODDS) AS BODDS, SUM(BOCLEARED) AS BOCLEARED, BOCLEAREDDATE
FROM (SELECT BODDS, ISNULL(BOCLEARED, 0) AS BOCLEARED, CONVERT(varchar(16), BOCLEAREDDATE, 101) AS BOCLEAREDDATE
FROM CTE) AS DATA
GROUP BY BOCLEAREDDATE WITH ROLLUP
The outlook is displayed below:
BODDS BOCLEARED BOCLEAREDDATE
BO 1 1/2/2009
BO 3 1/3/2009
Before I wrote a script above, I wrote single script for each column of "BO" Office. The reason I wrote the script for each column because I wish to know how many case have been cleared(which mean there is a date in the column and consider a case was cleared)
Below is the script for each column (I keep repeating the the script for each column by changing the name of that column's name)
SELECT COUNT(DEClearedDate) AS DECleared, DDS, CONVERT(varchar(16), DEClearedDate, 101) AS DEClearedDate
FROM dbo.ROCAPData
WHERE (DDS = 'BO')
Group By DEClearedDate, DDS
Next time, Select Count(SomaticMCClearedDate) as SomaticMCClearedDate, DDS .........
When I have counted the number of cases for all three offices BO, CT, NH then I wish to count the total of all three office. I wish to have the total cases were cleared of all three offices and here is what I wish it to display:
BOCLEARED CTCLEARED......TotalCleared ClearedDate DDS
1 2 3 1/3/2009 BO, CT
3 0 3 1/5/2009 BO
0 4 4 1/7/2009 CT
Please help me to solve this issue.
Thank you
January 30, 2009 at 5:01 am
[font="Verdana"]I din't get your requirement. But I think you are looking for PIVOT. For more information, check out BOL
Mahesh[/font]
MH-09-AM-8694
January 30, 2009 at 6:27 am
please help me with the question that I have posted above
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply