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];
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.
@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!