Tracking status changes over time

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

Table 1: Customer

Table 2: StatusHistory

The results I'm looking for are:

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
  );

But it doesn't give the results I need. How can I do this?

Try this:

DECLARE @filekey int = 1000;
WITH StatusChanges AS (
   
    SELECT filekey, status, activestatuseffdate effdate 
    FROM Customer,
    WHERE filekey = @filekey AND activestatuseffdate < '20230201'
    UNION
    SELECT filekey, status, effdate
    FROM StatusHistory
    WHERE filekey = @filekey AND effdate < '20230201'
),
Dates AS (
  SELECT DATEADD(day, n, '20230101') 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, '20230101') < '20230201'
)
SELECT DateValue, @filekey,
   (
        SELECT TOP 1 status
        FROM StatusChanges
        WHERE Filekey = @filekey AND effdate <= DateValue
        ORDER BY effdate DESC  
   ) effdate 
FROM Dates
ORDER BY DateValue

And for all customer files:

WITH StatusChanges AS (
   
    SELECT filekey, status, activestatuseffdate effdate 
    FROM Customer,
    WHERE activestatuseffdate < '20230201'
    UNION
    SELECT filekey, status, effdate
    FROM StatusHistory
    WHERE effdate < '20230201'
),
Dates AS (
  SELECT DATEADD(day, n, '20230101') 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, '20230101') < '20230201'
),
DateKeys AS (
     SELECT DateValue, filekey
     FROM Dates
     CROSS JOIN (SELECT DISTINCT filekey FROM Customer) t
)
SELECT dk.DateValue, dk.filekey,
   (
        SELECT TOP 1 sc.status
        FROM StatusChanges sc
        WHERE sc.Filekey = dk.filekey AND sc.effdate <= dk.DateValue
        ORDER BY sc.effdate DESC  
   ) effdate 
FROM DateKeys dk

You'll want to add an ORDER BY to this, but it's not clear whether you want filekey or DateValue first.