SQL : Repeat patterns for given date range

user_3861484 0
Viewed 317

I have tried few things and took some help from internet and got the solution to repeat the pattern of each employee for given date range.

but I am facing one challenge which is, if there is any weekend in between then I am losing the pattern's continuity. I have a requirement that if there is any weekend or any leave then my pattern should continue after the weekend or any applied leaves.

Please find the SQL Fiddle for more clarity on my requirement.

Also find the attached screen shot of my current output and expected requirement.

enter image description here

For given screen shot I have taken only one employee with default weekend (sat,sun or 6,7) but in SQL fiddle we have different week off for each employee...

sql-server sql
1 Answers

Check below: SQL Fiddle

Prepare two intermediate tables using the SQL you presented and the SQL described in my previous answer as follows:

    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
    )

Please check if you can get the expected output with the following 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
user_138410160
Related