Sequence Repeat for N times in SQL

Question

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.

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!

Answer 1

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];

Answer 2

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