sql server生成时间序列

DECLARE @startDate DATE = '2024-01-01';

-- 定义结束日期
DECLARE @endDate DATE = DATEADD(DAY, 365, @startDate)

-- 生成日期序列
;
WITH DateSequence AS (SELECT @startDate AS [Date],
            1 AS [DayNumber]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date]),
            [DayNumber] + 1
    FROM DateSequence  
    WHERE [Date] < @endDate),
calendar AS (SELECT d.ID,
            EmpId,
            CONVERT(VARCHAR(10), StartDate, 120) AS StartDate,
            CONVERT(VARCHAR(10), EndDate, 120) AS EndDate,
            TypeId,
            LocationId,
            d.Days
    FROM dbo.Etad_Calendar d WHERE d.TypeId = 'Travel (TL)' OR d.TypeId LIKE '%Leave%'),
k AS (
SELECT YEAR([Date]) AS Year,
		MONTH(t1.Date) Month,
		--YEAR([Date]) + '-' + MONTH(t1.Date) AS MonthStr,
        calendar.*
FROM DateSequence  AS t1 
    INNER JOIN calendar
        ON t1.Date >= calendar.StartDate
            AND t1.Date <= calendar.EndDate
    INNER JOIN dbo.Employee AS emp
        ON emp.EmployeeNo = calendar.EmpId
WHERE emp.DEPT = 'MCS'
        AND emp.COE = 'MCS_OTSS'
        AND emp.Active = 'Active'
)

SELECT k.Year,k.Month,k.TypeId,COUNT(k.TypeId) AS TypeCount FROM k  GROUP BY k.Year,k.Month,k.TypeId OPTION (maxrecursion 0)