I am trying to track status changes over a given date range. 1/1/2023 – 1/31/2023

Table 1: Customer C columns: filekey, activestatus, activestatuseffdate
Table 1, row 1: 1000, 0, 1/15/2023
Table 2: StatusHistory SH columns: filekey, status, effdate
Table 2, row 1: 1000, 0, 11/28/2022
Table 2, row 2: 1000, 1, 01/05/2023

The results I’m looking for are:
1/1/2023, 1000, 0
1/2/2023, 1000, 0
1/3/2023, 1000, 0
1/4/2023, 1000, 0
1/5/2023, 1000, 1
…
1/14/2023, 1000, 1
1/15/2023, 1000, 0
…
1/31/2023, 1000, 0

This is one of the many, many queries I’ve tried:

WITH StatusChanges AS (
  SELECT filekey, status, effdate,
         ROW_NUMBER() OVER (PARTITION BY filekey ORDER BY effdate) AS rn
  FROM empactstatushist
  WHERE filekey = 1000 AND effdate <= '2023-01-31'
),
Dates AS (
  SELECT DATEADD(day, n, '2023-01-01') AS DateValue
  FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY a.object_id) - 1 AS n
    FROM sys.objects a
  ) AS n
  WHERE DATEADD(day, n, '2023-01-01') < '2023-02-01'
)
SELECT
  d.DateValue,
  filekey,
  sc.status
FROM Dates d
LEFT JOIN StatusChanges sc ON sc.filekey = 1000
  AND sc.effdate = (
    SELECT MAX(effdate)
    FROM StatusChanges sc2
    WHERE sc2.filekey = 1000 AND sc2.effdate <= d.DateValue
  );