如何在SQL中根据生效日期创建时间序列

DECLARE @StartDate DATE = '2022-01-01';
DECLARE @EndDate DATE = '2022-01-31';

SELECT DATEADD(DAY, seq.number, @StartDate) AS Date
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS number
    FROM sys.columns
) seq
WHERE seq.number <= DATEDIFF(DAY, @StartDate, @EndDate);

WITH DateSequence AS (
    SELECT @StartDate AS Date
    UNION ALL
    SELECT DATEADD(DAY, 1, Date)
    FROM DateSequence
    WHERE DATEADD(DAY, 1, Date) <= @EndDate
)
SELECT Date
FROM DateSequence;

SELECT Date
FROM YourTable t
INNER JOIN DimDate d ON t.EffectiveDate <= d.Date
WHERE t.EffectiveDate >= @StartDate AND t.EffectiveDate <= @EndDate;