SQL : Repeat patterns for given date range

Question

我尝试了几件事并从互联网上获得了一些帮助，并获得了在给定日期范围内重复每个员工模式的解决方案。
但我面临一个挑战，如果中间有任何周末，那么我将失去模式的连续性。我有一个要求，如果有任何周末或任何假期，那么我的模式应该在周末或任何休假之后继续。
请找SQL Fiddle更清楚地说明我的要求。
还可以找到我当前输出和预期要求的附加屏幕截图。

对于给定的屏幕截图，我只使用了一名默认周末(周六、周日或 6,7 点)的员工，但在 SQL fiddle 中，我们为每位员工安排了不同的一周假期......

Answer 1

检查以下:SQL Fiddle
使用您提供的 SQL 和我之前的回答中描述的 SQL 准备两个中间表，如下所示:

    TempWeekOff(Txt , i , WeekOffId , EmployeeID)
    AS (
            SELECT STUFF(WeekOff, 1, CHARINDEX(@delimiter, WeekOff+@delimiter+'~'), ''), 1 , CAST(LEFT(WeekOff, CHARINDEX(@delimiter, WeekOff+@delimiter+'~')-1) AS VARCHAR(MAX)), 
            EmployeeID
            FROM RuleTableTemp
            UNION ALL
            SELECT STUFF(Txt, 1, CHARINDEX(@delimiter, Txt+@delimiter+'~'), '')
                , i + 1
                , CAST(LEFT(Txt, CHARINDEX(@delimiter, Txt+@delimiter+'~')-1) AS VARCHAR(MAX))
                , EmployeeID
            FROM TempWeekOff
            WHERE Txt > ''
         ),
    TempDates(i_count,Dates,dd,EmployeeID,SortCount,WeekOffId)
    AS (
        SELECT 
            i_count,
            DATEADD(DAY, i_count, @startDate) AS Dates , 
            DATEPART(DW,DATEADD(DAY, i_count, @startDate)) as dd,
            EmpID,
            sortCount,
            WeekOffId
        FROM (SELECT DATEDIFF(DAY, @startDate, @endDate) + 1) AS t_datediff(t_days)
        CROSS APPLY (SELECT TOP (t_days) ROW_NUMBER() OVER(ORDER BY (SELECT 0) ) - 1 FROM E8) AS t_dateadd(i_count)
        CROSS APPLY (SELECT DISTINCT EmployeeID FROM RuleTableTemp) AS t(EmpID)
        CROSS APPLY (SELECT COUNT(Sort) FROM PatternXFrequency WHERE EmployeeID = EmpID ) AS EmpPattern(sortCount)
        LEFT OUTER JOIN TempWeekOff ON EmpID = TempWeekOff.EmployeeID AND DATEPART(DW,DATEADD(DAY, i_count, @startDate)) = TempWeekOff.WeekOffId
    )

请检查您是否可以使用以下 SQL 获得预期的输出。

SELECT
    d.EmployeeID,
    d.Dates,
    d.dd,
    p.ShiftId
    FROM (SELECT *,((ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Dates)-1) % SortCount) AS i FROM TempDates WHERE WeekOffId IS NULL) AS d
    INNER JOIN PatternXFrequency p ON p.EmployeeID = d.EmployeeID AND d.i = p.Sort
UNION
SELECT
    d.EmployeeID,
    d.Dates,
    d.dd,
    NULL
    FROM (SELECT * FROM TempDates WHERE WeekOffId IS NOT NULL) AS d
ORDER BY 1,2