SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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