Sequence Repeat for N times in SQL

PS 376 Reputation points
2023-03-02T18:30:19.36+00:00

Hi All, I would like to check some ideas in achieving the below task.

I have a workitem table with 5 records and I would like to distribute each item for 5 days in a row with the date interval parameterized. something like below.

User's image

sample code:

CREATE TABLE #Temp (WorkItem VARCHAR(20))
INSERT INTO #Temp
SELECT 'X1'
UNION
SELECT 'X2'
UNION
SELECT 'X3'
UNION
SELECT 'X4'
UNION
SELECT 'X5'

Please share ideas if you have any. thank you!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,852 questions
{count} votes

Accepted answer
  1. Guoxiong 8,126 Reputation points
    2023-03-02T23:24:17.2833333+00:00

    Try this (I used the Date table based on the link you posted):

    DECLARE @N INT = 5;	--WorkItem
    DECLARE @N1 INT = 25;	-- Date Window
    DECLARE @D INT = @N1 / @N	-- Days assigned for each work item
    DECLARE @StartDate DATE = '2023-03-06';
    
    DECLARE @Temp TABLE (
    	[ID] INT IDENTITY(1, 1), 
    	[WorkItem] VARCHAR(20)
    )
    INSERT INTO @Temp
    SELECT 'X1'
    UNION
    SELECT 'X2'
    UNION
    SELECT 'X3'
    UNION
    SELECT 'X4'
    UNION
    SELECT 'X5';
    
    DECLARE @Date TABLE (
    	[ID] INT IDENTITY(1, 1),
    	[Date] DATE,
    	[DayName] VARCHAR(9)
    );
    
    INSERT INTO @Date ([Date], [DayName])
    SELECT TOP (@N1) [Date], [DayName]
    FROM [dbo].[Date] 
    WHERE [Date] >= @StartDate AND [IsWeekday] = 1 AND [IsHoliday] = 0
    ORDER BY [Date];
    
    DECLARE @WorkItemTemp TABLE (
    	[ID] INT IDENTITY(1, 1), 
    	[WorkItem] VARCHAR(20)
    )
    
    DECLARE @i INT = 1;
    DECLARE @workItem VARCHAR(20);
    DECLARE @j INT;
    
    WHILE @i <= @N 
    BEGIN 
    	SELECT @workItem = [WorkItem] FROM @Temp WHERE [ID] = @i;
    
    	SET @j = 1;
    	WHILE @j <= @D
    	BEGIN
    		INSERT INTO @WorkItemTemp ([WorkItem]) VALUES (@workItem); 
    
    		SET @j = @j + 1
    	END
    
        SET @i = @i + 1; 
    END; 
    
    SELECT d.[Date], d.[DayName], t.[WorkItem] 
    FROM @WorkItemTemp AS t
    INNER JOIN @Date AS d ON t.[ID] = d.[ID];
    
    

    User's image


1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 102.1K Reputation points MVP
    2023-03-02T21:12:30.8766667+00:00

    On SQL 2022 or Azure SQL Database/Managed Instance, you can use this query:

    WITH CTE AS (
       SELECT WorkItem, dateadd(DAY, value, '20230603') Date
       FROM   #Temp
       CROSS  JOIN generate_series(0, 4)
    )
    SELECT Date, convert(varchar(11), Date), datename(WEEKDAY, Date), WorkItem
    FROM   CTE
    
    

    On older versions of SQL Server, you would have to use a table of numbers, see my short article at https://www.sommarskog.se/Short%20Stories/table-of-numbers.html