Sequence Repeat for N times in SQL

Question

Sequence Repeat for N times in SQL

PS 191

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!

Yitzhak Khabinsky 20,721 Reputation points

2023-03-02T19:46:28.99+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
Viorel 89,236 Reputation points

2023-03-02T19:52:40.71+00:00

How will it look if the first parameter of the interval is not Monday?

It is not clear in which order to take the names from #Temp.
PS 191 Reputation points

2023-03-02T20:27:24.62+00:00

@Yitzhak Khabinsky - sorry if the request isn't clear enough.

Request - If I have N workitems in my workitem table & I have N1 days in the time frame selected. Then I need to distribute the number of days equally among those work items in a sequence.

Example -

Table - WorkItem - 5 Records (N)

Date Window - 10 days (N1)

Then I need to assign 2 Days for each work item. I would like to parameterize N & N1

To produce a date dimension - you can use

https://gist.github.com/jrgcubano/c4dbaa879a1cfc9899f961d6eafa737c

for the WorkItem table & expected output - I have provided the details in my original post. In my screenshot, I have considered 25days, so each work item got 5days.

Please let me know if you need any other info. Thank you!

Yitzhak Khabinsky 20,721 Reputation points

2023-03-02T19:46:28.99+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
Viorel 89,236 Reputation points

2023-03-02T19:52:40.71+00:00

How will it look if the first parameter of the interval is not Monday?

It is not clear in which order to take the names from #Temp.
PS 191 Reputation points

2023-03-02T20:27:24.62+00:00

@Yitzhak Khabinsky - sorry if the request isn't clear enough.

Request - If I have N workitems in my workitem table & I have N1 days in the time frame selected. Then I need to distribute the number of days equally among those work items in a sequence.

Example -

Table - WorkItem - 5 Records (N)

Date Window - 10 days (N1)

Then I need to assign 2 Days for each work item. I would like to parameterize N & N1

To produce a date dimension - you can use

https://gist.github.com/jrgcubano/c4dbaa879a1cfc9899f961d6eafa737c

for the WorkItem table & expected output - I have provided the details in my original post. In my screenshot, I have considered 25days, so each work item got 5days.

Please let me know if you need any other info. 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];

User's image

PS 191 Reputation points

2023-03-02T23:41:31.0066667+00:00

This is great, and exactly what I was expecting. Thank you @Guoxiang !!

Answer 2

Erland Sommarskog 72,891 MVP

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

PS 191

@Erland Sommarskog Thank you for your response and always appreciate your endless support to the community.

Since, our SQL Managed Instance compatibility level is set to 140, I am unable to use generate_series() function. However, I created a temp table to get the similar series.

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
	DROP TABLE #Temp
CREATE TABLE #Temp
(
    WorkItem VARCHAR(20)
);
INSERT INTO #Temp
SELECT 'X1'
UNION
SELECT 'X2'
UNION
SELECT 'X3'
UNION
SELECT 'X4'
UNION
SELECT 'X5';

IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
	DROP TABLE #Temp1
CREATE TABLE #Temp1
(
    days INT
);
INSERT INTO #Temp1
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5;


WITH CTE AS (
   SELECT WorkItem, dateadd(DAY, [days], '20230603') Date
   FROM   #Temp
   --CROSS  JOIN generate_series(0, 4)
   CROSS JOIN [#Temp1]
)
SELECT Date, CONVERT(VARCHAR(11), Date), datename(WEEKDAY, Date), WorkItem
FROM   CTE

your query is giving me the result set like below.

User's image

But, I am looking for something like this..

User's image

Please let me know if any additional details is needed.

Sequence Repeat for N times in SQL

1 additional answer

Activity