By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table #temp
(
ClientID int,
DateCreated datetime,
Score decimal(18,2)
)
insert into #temp
values
(1, '01 Nov 2023', 56),
(1, '15 Jan 2024', 90),
(2, '08 Dec 2023', 76),
(2, '25 Jan 2024', 98),
(2, '01 Mar 2024', 23)
5 rows affected
WITH Months AS (
SELECT DATEADD(month, DATEDIFF(month, 0,
(SELECT MIN(T.DateCreated) FROM #Temp T)
), 0) AS Month -- Tricky, equivalent of DATETRUNC(month, ...)
UNION ALL
SELECT DATEADD(month, 1, M.Month) AS Month
FROM Months M
WHERE DATEADD(month, 1, M.Month) < GETDATE()
),
Clients AS (
SELECT DISTINCT T.ClientId
FROM #Temp T
)
SELECT T.ClientID, M.Month AS DateCreated, T.Score, Comments = '(auto-generated)'
FROM Months M
CROSS APPLY Clients C
CROSS APPLY (
SELECT TOP 1 *
FROM #Temp T
WHERE T.ClientId = C.ClientId
AND T.DateCreated < M.Month -- Prior
ORDER BY T.DateCreated DESC -- Most Recent
) T
WHERE NOT EXISTS (
-- Row does not exists for current month and client
SELECT *
FROM #Temp T
WHERE T.ClientId = C.ClientId
AND T.DateCreated >= M.Month
AND T.DateCreated < DATEADD(month, 1, M.Month)
)
UNION ALL
SELECT T.ClientID, T.DateCreated, T.Score, Comments = ''
FROM #Temp T
ORDER BY CLientId, DateCreated
| ClientID | DateCreated | Score | Comments |
|---|---|---|---|
| 1 | 2023-11-01 00:00:00.000 | 56.00 | |
| 1 | 2023-12-01 00:00:00.000 | 56.00 | (auto-generated) |
| 1 | 2024-01-15 00:00:00.000 | 90.00 | |
| 1 | 2024-02-01 00:00:00.000 | 90.00 | (auto-generated) |
| 1 | 2024-03-01 00:00:00.000 | 90.00 | (auto-generated) |
| 2 | 2023-12-08 00:00:00.000 | 76.00 | |
| 2 | 2024-01-25 00:00:00.000 | 98.00 | |
| 2 | 2024-02-01 00:00:00.000 | 98.00 | (auto-generated) |
| 2 | 2024-03-01 00:00:00.000 | 23.00 |