27,579
社区成员
发帖
与我相关
我的任务
分享
DECLARE @START_DATE DATE
DECLARE @END_DATE DATE
SET @START_DATE='2022-01-01'
SET @END_DATE='2023-01-01'
;WITH CTE
AS
(SELECT NUMBER,DATEADD(DAY,NUMBER,@START_DATE) AS SINGLE_DATE,
CASE WHEN DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,@START_DATE))=1 THEN 7 ELSE DATEPART(WEEKDAY,DATEADD(DAY,NUMBER,@START_DATE))-1 END AS SINGLE_DAY
FROM MASTER.DBO.SPT_VALUES
WHERE NUMBER<=DATEDIFF(DAY,@START_DATE,@END_DATE)
AND TYPE='P')
SELECT C.SINGLE_DATE,A.StartTime,A.Weekly,B.ClassId
FROM ScheduleTime A
JOIN Schedule B ON A.ScheduleId=B.ScheduleId
JOIN CTE C ON C.SINGLE_DATE BETWEEN B.StartDate AND B.EndDate AND A.Weekly=C.SINGLE_DAY