SQL: Repeat patterns between date range

Question

DECLARE     
@startDate  date = '2020-07-03'
@endDate date = 2020-07-06'

I have a tabe as below

---------------------------------------------------------
|EmployeeID | EmpName   |Pattern   | Frequency      |
---------------------------------------------------------
|  11       | X         |   1,2,3  |    1           |
|  12       | Y         |   4,5    |    1           |
|  13       | Y         |   1,2    |    3           |
|  14       | Z         |   1,2    |    2           |
---------------------------------------------------------

AND I want to generate dates between given date range. WANT result table as bellows:

--------------------------------
| EmpId |   Dates    | Pattern |
--------------------------------
| 11    |2020-07-03  |   1     |
| 11    |2020-07-04  |   2     |
| 11    |2020-07-05  |   3     |
| 11    |2020-07-06  |   1     |

| 12    |2020-07-03  |   4     |
| 12    |2020-07-04  |   5     |
| 12    |2020-07-05  |   4     |
| 12    |2020-07-06  |   5     |

| 13    |2020-07-03  |   1     |
| 13    |2020-07-04  |   1     |
| 13    |2020-07-05  |   1     |
| 13    |2020-07-06  |   2     |

| 14    |2020-07-03  |   1     |
| 14    |2020-07-04  |   1     |
| 14    |2020-07-05  |   2     |
| 14    |2020-07-06  |   2     |

Generate the dates as per given date range for each employee and repeat the pattern for each employee as per their pattern and frequency(days).

means as per frequency(days) pattern will change.

What I have acheived : Able to generate the records for each employees between the given date range.

What I am not able to get: I am not able to repeat the pattern based on the frequency for each employee between the date range.

I am able achieve everything but need little help while repeating the pattern based on frequency.*

Note: Data are storing in this way only.. now I won't change existing schema...

Answer 1

I've came up with this. It's basically a splitter, a tally table and some logic. Joining (Frequency)-Amount of Tally-datasets with the splitted pattern for the correct amount of pattern-values. Sorting them by their position in the pattern-string. Join everything together and repeat the pattern by using modulo.

DECLARE @t TABLE( EmployeeID INT
                , EmpName    VARCHAR(20)
                , Pattern    VARCHAR(255)
                , Frequency  INT )

DECLARE @startDate DATE = '2020-07-03'
DECLARE @endDate DATE = '2020-07-09'

INSERT INTO @t
VALUES (11, 'X', '1,2,3', 1),
       (12, 'Y', '4,5', 1),
       (13, 'Y', '1,2', 3),
       (14, 'Z', '1,2', 2)

DECLARE @delimiter CHAR(1) = ',';

WITH split(Txt
         , i
         , elem
         , EmployeeID)
     AS (SELECT STUFF(Pattern, 1, CHARINDEX(@delimiter, Pattern+@delimiter+'~'), '')
              , 1
              , CAST(LEFT(Pattern, CHARINDEX(@delimiter, Pattern+@delimiter+'~')-1) AS VARCHAR(MAX))
              , EmployeeID
           FROM @t
         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 split
          WHERE Txt > ''),
     E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
               SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
               SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
               SELECT 1), --10E+1 or 10 rows
     E2(N) AS (SELECT 1 FROM E1 AS a, E1 AS b), --10E+2 or 100 rows
     E4(N) AS (SELECT 1 FROM E2 AS a, E2 AS b), --10E+4 or 10,000 rows 
     E8(N) AS (SELECT 1 FROM E4 AS a , E4 AS b), --10E+8 or 100,000,000 rows 
     PatternXFrequency(EmployeeID
                     , Sort
                     , elem)
     AS (SELECT split.EmployeeID
              , ROW_NUMBER() OVER(PARTITION BY split.EmployeeID ORDER BY i) - 1
              , elem
           FROM split
           INNER JOIN @t AS t ON t.EmployeeID = split.EmployeeID
           CROSS APPLY (SELECT TOP (t.Frequency) 1
                          FROM E8
                       ) AS Freq(Dummy))

     SELECT EmployeeID
          , DATEADD(DAY, i_count, @startDate) AS Dates
          , elem
       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 PatternXFrequency.*
                      FROM (SELECT DISTINCT EmployeeID FROM @t) AS t(EmpID)
                      CROSS APPLY (SELECT COUNT(Sort)
                                     FROM PatternXFrequency
                                    WHERE EmployeeID = EmpID
                                  ) AS EmpPattern(sortCount)
                      CROSS APPLY (SELECT *
                                     FROM PatternXFrequency
                                    WHERE EmployeeID = EmpID
                                          AND Sort = ((i_count % sortCount))
                                  ) AS PatternXFrequency
                   ) AS t
      ORDER BY t.EmployeeID
             , Dates

Answer 2

Like mentioned in the comments fix your data model.

Your output pattern is a little bit strange. But is it something like this you are looking for?

DECLARE @startDate  date = '2020-07-03'
DECLARE @endDate date = '2020-07-09'
DECLARE @Dates TABLE([Date] Date)

;WITH seq(n) AS 
(
    SELECT 0 UNION ALL SELECT n + 1 FROM seq
    WHERE n < DATEDIFF(DAY, @StartDate, @endDate)
)
INSERT INTO @Dates ([Date])
SELECT DATEADD(Day,n, cast(GetDate() as date)) Date 
FROM seq
ORDER BY n 
OPTION (MAXRECURSION 0); 


SELECT e.EmployeeId, d.Date, x.Value Pattern
FROM Employee e
CROSS APPLY STRING_SPLIT(e.Pattern, ',') x
INNER JOIN @Dates d on 1=1
    -- Correct for the first iteration of the pattern
    AND DATEDIFF(DAY, DATEADD(DAY, -1, @StartDate), d.Date) = x.Value

Answer 3

This isn't particularly pretty, but it avoids the recursion of a rCTE, so should provide a faster experience. As STRING_SPLIT still doesn't know what ordinal position means, we have to use something else here; I use DelimitedSplit8k_LEAD.

I also assume your expected results are wrong, as they stop short of your end date (20200709). This results in the below:

CREATE TABLE dbo.YourTable (EmployeeID int,
                            EmpName char(1),
                            Pattern varchar(8000), --This NEEDS fixing
                            Frequency tinyint);
INSERT INTO dbo.YourTable
VALUES(11,'X','1,2,3',1),
      (12,'Y','4,5',1),
      (13,'Y','1,2',3),
      (14,'Z','1,2',2);
GO

DECLARE @StartDate  date = '20200703',
        @EndDate date = '20200709';

WITH CTE AS(
    SELECT *,
           MAX(ItemNumber) OVER (PARTITION BY EmployeeID) AS MaxItemNumber
    FROM dbo.YourTable YT
         CROSS APPLY dbo.DelimitedSplit8K_LEAD(YT.Pattern,',') DS),
N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (SELECT DATEDIFF(DAY,@startDate, @EndDate)+1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS I
    FROM N N1, N N2, N N3) --1000 Rows
SELECT C.EmployeeID,
       DATEADD(DAY,T.I, @StartDate),
       C.Item       
FROM CTE C
     JOIN Tally T ON ISNULL(NULLIF((T.I +1) % C.MaxItemNumber,0),C.MaxItemNumber) = C.ItemNumber
ORDER BY EmployeeID,
         T.I;
GO

DROP TABLE dbo.YourTable;