Fill rows for missing data by last day of month

Question

I have a table that looks like

UserID        LastDayofMonth              Count
1234          2015-09-30 00:00:00         12
1237          2015-09-30 00:00:00         5
3233          2015-09-30 00:00:00         3
8336          2015-09-30 00:00:00         22
1234          2015-10-31 00:00:00         8
1237          2015-10-31 00:00:00         5
3233          2015-10-31 00:00:00         7
8336          2015-11-30 00:00:00         52
1234          2015-11-30 00:00:00         8
1237          2015-11-30 00:00:00         5
3233          2015-11-30 00:00:00         7

(with around ~10,000 rows). As you can see in the example, UserID 8336 has no record for October 31st (dates are monthly but always the last day of the month, which I want to keep). How do I return a table with a records that fills in records for a period of four months so that users like 8336 get records like

8336          2015-10-31 00:00:00         0

I do have a calendar table with all days that I can use.

Answer 1

This solution uses a couple of CTEs, not knowing your calendar table layout. The only advantage this solution has over Gordon Linoff's is it doesn't assume at least one user per possible month. I've provided test data per your example with an extra record for the month of July, skipping August entirely.

/************** TEST DATA ******************/
IF OBJECT_ID('MonthlyUserCount','U') IS NULL
BEGIN
    CREATE TABLE MonthlyUserCount
    (
          UserID INT
        , LastDayofMonth DATETIME
        , [Count] INT
    )

    INSERT MonthlyUserCount
    VALUES (1234,'2015-07-31 00:00:00',12),--extra record
           (1234,'2015-09-30 00:00:00',12),
           (1237,'2015-09-30 00:00:00',5),
           (3233,'2015-09-30 00:00:00',3),
           (8336,'2015-09-30 00:00:00',22),
           (1234,'2015-10-31 00:00:00',8),
           (1237,'2015-10-31 00:00:00',5),
           (3233,'2015-10-31 00:00:00',7),
           (8336,'2015-11-30 00:00:00',52),
           (1234,'2015-11-30 00:00:00',8),
           (1237,'2015-11-30 00:00:00',5),
           (3233,'2015-11-30 00:00:00',7)
END
/************ END TEST DATA ***************/

DECLARE @Start DATETIME;
DECLARE @End DATETIME;

--establish a date range
SELECT @Start = MIN(LastDayofMonth) FROM MonthlyUserCount;
SELECT @End   = MAX(LastDayofMonth) FROM MonthlyUserCount;

--create a custom calendar of days using the date range above and identify the last day of the month
--if your calendar table does this already, modify the next cte to mimic this functionality
WITH cteAllDays AS
(
    SELECT @Start AS [Date], CASE WHEN DATEPART(mm, @Start) <> DATEPART(mm, @Start+1) THEN 1 ELSE 0 END [Last]
    UNION ALL
    SELECT [Date]+1, CASE WHEN DATEPART(mm,[Date]+1) <> DatePart(mm, [Date]+2) THEN 1 ELSE 0 END 
    FROM cteAllDays
    WHERE [Date]< @End
),
--cte using calendar of days to associate every user with every end of month
cteUserAllDays AS
(
    SELECT DISTINCT m.UserID, c.[Date] LastDayofMonth
    FROM MonthlyUserCount m, cteAllDays c
    WHERE [Last]=1      
)
--left join the cte to evaluate the NULL and present a 0 count for that month
SELECT c.UserID, c.LastDayofMonth, ISNULL(m.[Count],0) [Count]
FROM cteUserAllDays c 
    LEFT JOIN MonthlyUserCount m ON m.UserID = c.UserID
        AND m.LastDayofMonth =c.LastDayofMonth
ORDER BY c.LastDayofMonth, c.UserID
OPTION ( MAXRECURSION 0 )

Answer 2

If I understand correctly, you want a record for each user and for each end of month. And, if the record does not currently exist, then you want the value of 0.

This is two step process. Generate all the rows first, using cross join. Then use left join to get the values.

So:

select u.userId, l.LastDayofMonth, coalesce(t.cnt, 0) as cnt
from (select distinct userId from t) u cross join
     (select distinct LastDayofMonth from t) l left join
     t
     on t.userId = u.userId and t.LastDayofMonth = l.LastDayofMonth;