drew.allen - Wednesday, February 28, 2018 8:42 AM

Luis Cazares - Wednesday, February 28, 2018 7:41 AM

Your query looks so overwhelming when it can be so simple:

SELECT CAST( td.CallDateTime AS DATE),
   COUNT(CASE WHEN td.CallTypeId = 1 THEN 1 END) AS [1],
   COUNT(CASE WHEN td.CallTypeId = 2 THEN 1 END) AS [2],
   COUNT(CASE WHEN td.CallTypeId = 3 THEN 1 END) AS [3],
   COUNT(CASE WHEN td.CallTypeId = 4 AND Duration <> '00:00:00.000' THEN 1 END) AS [4],
   COUNT(CASE WHEN td.CallTypeId = 5 AND Duration <> '00:00:00.000' THEN 1 END) AS [5],
   SUM(DATEDIFF(MILLISECOND, 0,ISNULL(Duration,'00:00:00')))
FROM #TmpData AS td
GROUP BY CAST( td.CallDateTime AS DATE);


EDIT: I made a mistake on the Types 4 and 5. I have corrected it and made it even simpler.

You can make it even simpler.  Null values are ignored in SUM, so there is no reason to convert it to the additive identity, i.e., zero.


SELECT CAST( td.CallDateTime AS DATE),
   COUNT(CASE WHEN td.CallTypeId = 1 THEN 1 END) AS [1],
   COUNT(CASE WHEN td.CallTypeId = 2 THEN 1 END) AS [2],
   COUNT(CASE WHEN td.CallTypeId = 3 THEN 1 END) AS [3],
   COUNT(CASE WHEN td.CallTypeId = 4 AND Duration <> '00:00:00.000' THEN 1 END) AS [4],
   COUNT(CASE WHEN td.CallTypeId = 5 AND Duration <> '00:00:00.000' THEN 1 END) AS [5],
   SUM(DATEDIFF(MILLISECOND, 0,Duration))
FROM #TmpData AS td
GROUP BY CAST( td.CallDateTime AS DATE);


Drew