CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012pivot
cpiasecki
cpiasecki

Reputation: 47

Cumulative sum based on months in a year with NULL Months

I am trying to return a summary of all prospects entered in the system for a chosen sales person and a chosen year. The date entered is a datetime and I am pivoting the data to show it broken by month to month.

The first query returns the number of referrals each month, where the second query shows how many referrals entered cumulatively. I have gotten them properly showing with the exception of months containing null values. Months with null values I want to show the value of the previous month in that case.

I am currently using T-SQL 2012.

SELECT 'Number of Referrals' AS Measure,[1] AS Jan, [2] AS Feb, [3] AS Mar, [4] AS Apr, 
[5] AS May, [6] AS Jun, [7] AS Jul, [8] AS Aug, [9] AS Sep, [10] AS Oct, [11] AS Nov, [12] AS Dec
FROM
(
SELECT ISNULL(COUNT(DISTINCT CSCU.ClientID), 0) AS Referrals, DATEPART(M, CS.DateAdded) AS Months
FROM ClientTracker.dbo.ClientService CS
INNER JOIN ClientTracker.dbo.ClientServiceContactUser CSCU
ON CS.ClientID = CSCU.ClientID
AND CS.ServiceID = CSCU.ServiceID
WHERE UserId = @UserID
AND DATEPART(YEAR, CS.DateAdded) = @Year
AND CSCU.ServiceID = 30
GROUP BY DATEPART(M, CS.DateAdded)
) AS S
PIVOT
(
SUM(Referrals)
FOR [Months] IN ([1], [2], [3], [4], 
    [5], [6], [7], [8], [9], [10], [11], [12])
)AS PV

UNION

SELECT 'Number of Referrals Cumulative' AS Measure,[1] AS Jan, [2] AS Feb, [3] AS Mar, [4] AS Apr, [5] AS May, [6] AS Jun, [7] AS Jul, [8] AS Aug, [9] AS Sep, [10] AS Oct, [11] AS Nov, [12] AS Dec
FROM
(
SELECT DATEPART(M, CS.DateAdded) AS Months, 
SUM(COUNT(CSCU.ClientID)) OVER (ORDER BY DATEPART(M, CS.DateAdded)) AS Cumulative
FROM ClientTracker.dbo.ClientService CS
INNER JOIN ClientTracker.dbo.ClientServiceContactUser CSCU
ON CS.ClientID = CSCU.ClientID
AND CS.ServiceID = CSCU.ServiceID
WHERE UserId = @UserID
AND DATEPART(YEAR, CS.DateAdded) = @Year
AND CSCU.ServiceID = 30
GROUP BY DATEPART(M, CS.DateAdded)
) AS S
PIVOT
(
SUM(Cumulative)
FOR [Months] IN ([1], [2], [3], [4], 
    [5], [6], [7], [8], [9], [10], [11], [12])
)AS PV

Upvotes: 0

Views: 943

Answers (1)

sahalMoidu
sahalMoidu

Reputation: 1152

You could use coalesce() function. It takes in as many parameters and returns the first non null values. So for in your example rewrite the query to

SELECT 'Number of Referrals' AS Measure,[1] AS Jan, coalesce([2],[1]) AS Feb, .....

COALESCE() on MSDN

Upvotes: 0

Related Questions