Itzik Ben-Gan wrote an incredible article on this subject. You can find it at the following URL:
Because you're not using SQL Server 2012 or above, we can't use his more modern 3rd method.
With that, here's you're test data setup so that it actually works and works for everyone in case they want to play. I kept the same casing as you in case (no pun intended) you have a case-sensitive server. I do have to express a great dislike for the DATE and TIME data types because you have to combine them to do this simple type of date math if you want to survive possibly traversing midnight with your data for a given process.
CREATE TABLE #TABLE_PROCESSTIMES
INSERT INTO #TABLE_PROCESSTIMES
SELECT '5935','20190919','11:45:00.000','12:45:00.000','28' UNION ALL
SELECT '5936','20190919','13:45:00.000','14:15:00.000','41' UNION ALL
SELECT '5936','20190919','14:15:00.000','14:30:00.000','41' UNION ALL
SELECT '5936','20190919','14:30:00.000','14:45:00.000','41' UNION ALL
And here's Itzik's very high performance method warped to fit your needs...
--===== Solve the problem using Itzik''s count up/count down method of grouping.
(--==== Find all the starts and number them as "S"
,TS = CONVERT(DATETIME,START_DATE)+START_TIME
,Type = +1
,E = NULL
,S = ROW_NUMBER() OVER (PARTITION BY EMPID ORDER BY CONVERT(DATETIME,START_DATE)+START_TIME, PROCESSID)
--==== Find all the ends and number them as "E" and add 1 second of time to the "TS" (end time)
,TS = DATEADD(ss,1,CONVERT(DATETIME,START_DATE)+END_TIME)
,Type = -1
,E = ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY DATEADD(ss,1,CONVERT(DATETIME,START_DATE)+END_TIME), PROCESSID)
,S = NULL
(--==== Create a sort order (SE) according to the time stamp, row type, and process ID by employee ID
,SE = ROW_NUMBER() OVER (PARTITION BY EMPID ORDER BY TS, Type DESC, PROCESSID)
FROM C1 c1
(--==== Create group numbers according to the beginning and end of contiguous times by employee ID
,GrpNum = FLOOR((ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY TS)-1)/2+1)
WHERE COALESCE(S-(SE-S)-1, (SE-E)-E) = 0
)--===== Return the min and max times for each contiguous group for the final answer
,Start_Date = MIN(TS)
,End_Date = MAX(DATEADD(ss,-1,TS))
GROUP BY PROCESSID, EMPID, GrpNum
ORDER BY PROCESSID, EMPID, START_DATE
And here are the results...
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)