It is highly unlikely that you need to use a loop of any kind. My article
Processing Loops in SQL Server[
^] offers several worked examples with alternatives to loops.
In this case you need to identify the individual timeslots that exist on the production table e.g.
create table #timeslots (id int identity(1,1), [startDateTime] [datetime] NOT NULL,
[endDateTime] [datetime] NULL)
insert into #timeslots SELECT DISTINCT startDateTime, endDateTime FROM production
This gives each timeslot a unique identifier e.g.
1 2019-02-22 09:00:00.000 2019-02-22 10:00:00.000
2 2019-02-22 10:00:00.000 2019-02-22 11:00:00.000
3 2019-02-22 11:00:00.000 2019-02-22 12:00:00.000
4 2019-02-22 12:00:00.000 2019-02-22 13:00:00.000
All you have to do then is work out the difference in minutes between the start and end times and divide it by the number of tasks the worker was involved in in that timeslot: e.g.
select TaskId, WorkerId, T.id AS TIMESLOT
, DATEDIFF(MINUTE, T.startDateTime, T.endDateTime) / COUNT(*) OVER (PARTITION BY WorkerId, T.id)
from #production P
inner join #timeslots T ON P.startDateTime = T.startDateTime AND P.endDateTime = T.endDateTime
Results:
1 A 1 60
1 A 2 30
2 A 2 30
1 A 3 20
2 A 3 20
3 A 3 20
1 A 4 30
3 A 4 30
This will work if timeslots cross over days but won't work if the
endDateTime
is null. I suggest that you standardise your timeslots over all workers otherwise it is going to be difficult to interpret the results (too many timeslots representing the same period)
if anyone else wants to have a go at improving this, here is the sample data I used based on the OP's description
CREATE TABLE #production(
[id] [varchar](10) NOT NULL,
[taskId] [varchar](10) NOT NULL,
[startDateTime] [datetime] NOT NULL,
[endDateTime] [datetime] NULL,
[workerId] [varchar](5) NOT NULL
)
INSERT INTO #production (id, taskid, startDateTime, endDateTime, workerId) values
('1','1','22-Feb-2019 09:00:00', '22-Feb-2019 10:00:00', 'A'),
('2','1','22-Feb-2019 10:00:00', '22-Feb-2019 11:00:00', 'A'),
('3','2','22-Feb-2019 10:00:00', '22-Feb-2019 11:00:00', 'A'),
('4','1','22-Feb-2019 11:00:00', '22-Feb-2019 12:00:00', 'A'),
('5','2','22-Feb-2019 11:00:00', '22-Feb-2019 12:00:00', 'A'),
('6','3','22-Feb-2019 11:00:00', '22-Feb-2019 12:00:00', 'A'),
('7','1','22-Feb-2019 12:00:00', '22-Feb-2019 13:00:00', 'A'),
('8','3','22-Feb-2019 12:00:00', '22-Feb-2019 13:00:00', 'A')
EDIT (see OP comment below):
To get the total amount of time spent on each task you could use a CTE (or a temporary table) and just group on TaskId (note I've had to add a column name for the calculation
spent
)
;with cte as
(
select TaskId, WorkerId, T.id AS TIMESLOT
, DATEDIFF(MINUTE, T.startDateTime, T.endDateTime) / COUNT(*) OVER (PARTITION BY WorkerId, T.id) as spent
from #production P
inner join #timeslots T ON P.startDateTime = T.startDateTime AND P.endDateTime = T.endDateTime
) select TaskId, SUM(spent)
FROM cte GROUP BY TaskID