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.