OK so preparing this data for SSC seems to have stimuated a few grey cells

I've fixed the output it - as follows
However - can it be further simplified?


;WITH SplitDates AS (
    SELECT    CAST(N.CallDateTime AS date) AS CallDate,
            DATEDIFF(MILLISECOND, '1900-01-01',ISNULL(N.Duration,'00:00:00')) AS [DurationMs],
            N.CallTypeId,
            -- Rules - some not counted
            CASE WHEN N.CallTypeID = 4 AND ISNULL(N.Duration,'00:00:00.000') = '00:00:00.000' THEN 0
                 WHEN N.CallTypeID = 5 AND ISNULL(N.Duration,'00:00:00.000') = '00:00:00.000' THEN 0
                 ELSE 1
            END AS CallCount
    FROM #TmpData AS N
), SumCallsPerTypePerDay AS (
    SELECT SUM(CallCount) AS NumCalls,
   CallDate,
     CallTypeId
    FROM SplitDates
    GROUP BY CallDate, CallTypeId
), DurationByDay AS (
    SELECT CallDate, SUM(DurationMs) AS [Total Duration]
    FROM SplitDates
    GROUP BY CallDate
), PivotNumbers AS (
    SELECT CallDate, [1], [2], [3], [4], [5] , [Total Duration]
    FROM ( SELECT CallTypeId, T.CallDate, NumCalls, D.[Total Duration]
            FROM SumCallsPerTypePerDay AS T
            JOIN DurationByDay AS D ON D.CallDate = T.CallDate
            ) AS SourceTable
    PIVOT ( SUM(NumCalls)
            FOR CallTypeId IN ([1], [2], [3], [4], [5]) ) AS PivotTable
)
SELECT *
FROM PivotNumbers
ORDER BY CallDate