Merging Non overlapping timedates

Question

Post reply

Merging Non overlapping timedates

mark 4643

SSCommitted

Points: 1503
More actions
September 17, 2014 at 4:35 am

#299444

Hi All
Using SQL 2008 R2 and stuck on this.
I have several sets of timedate ranges and I need to merge the ranges where there is no overlap with the jobs on resource1. In my example data, I want all jobs from ResourceID 1 and those jobs from all other resources where they do not overlap with EXISTING jobs on resource 1 (i.e. imagine I'm trying to select candidates from other resources to fill ResourceID 1 with continuous jobs)
Below is some sample data, my failed attempt and expected results. I managed to excluded everything that should be excluded except job 10
-- Need to select all other jobs from all other resources that can be merged into resource 1 where there is no overlap with existing jobs in resource 1 only
CREATE TABLE #Jobs
(
resourceID INT
,JobNo INT
,StartTime SMALLDATETIME
,EndTime SMALLDATETIME
,ShouldBeOmitted BIT
)
INSERT INTO [#Jobs]
SELECT
1
,1
,'2014-08-01 08:00:00'
,'2014-08-01 10:00:00'
,0
UNION
SELECT
1
,2
,'2014-08-01 18:00:00'
,'2014-08-01 20:00:00'
,0
UNION
SELECT
2
,3
,'2014-08-01 07:00:00'
,'2014-08-01 20:00:00' -- should be omitted as ends in middle of job 1
,1
UNION
SELECT
2
,4
,'2014-08-01 09:00:00'
,'2014-08-01 11:00:00' -- should be omitted as starts in middle of job 1
,1
UNION
SELECT
2
,5
,'2014-08-01 10:00:00'
,'2014-08-01 11:00:00' -- OK because it starts exactly at end of job 1
,0
UNION
SELECT
2
,6
,'2014-08-01 12:00:00'
,'2014-08-01 14:00:00' -- OK because no overlap
,0
UNION
SELECT
2
,7
,'2014-08-01 16:00:00'
,'2014-08-01 18:00:00' -- OK because it ends exactly at start of job 2
,0
UNION
SELECT
2
,8
,'2014-08-01 19:00:00'
,'2014-08-01 19:30:00' -- should be omitted as it is inside tme range of job 2
,1
UNION
SELECT
2
,9
,'2014-08-01 20:00:00'
,'2014-08-01 21:00:00' -- should be OK as it is starts exactly at end of job 2
,0
UNION
SELECT
4
,10
,'2014-08-01 02:00:00'
,'2014-08-01 22:00:00' -- should be omitted as spans other jobs
,1
UNION
SELECT
5
,11
,'2014-08-01 08:00:00'
,'2014-08-01 10:00:00' -- should be omitted as it matches other job
,1
SELECT
'Source Data', *
FROM
#Jobs
SELECT
'ExpectedResults'
,*
FROM
[#Jobs] j
WHERE
j.[ShouldBeOmitted] = 0
SELECT
'MyResults'
,[j].[ResourceID]
,[j].[JobNo]
,[j].[StartTime]
,[j].[EndTime]
,[j].[ShouldBeOmitted]
FROM
#Jobs j
WHERE
[ResourceID] = 1
UNION ALL
SELECT
'MyResults'
,[j2].[ResourceID]
,[j2].[JobNo]
,[j2].[StartTime]
,[j2].[EndTime]
,[j2].[ShouldBeOmitted]
FROM
#Jobs j2
WHERE
[ResourceID] != 1
AND NOT EXISTS ( SELECT
1
FROM
#Jobs
WHERE
[ResourceID] = 1
AND (
(
-- Starts within existing job
j2.[StartTime] >= [StartTime]
AND j2.[StartTime] < [EndTime]
)
OR (
-- Ends withing existing job
j2.[EndTime] > [StartTime]
AND j2.[EndTime] <= [EndTime]
)
) )
ORDER BY
[j].[resourceID]
,j.starttime
Would appreciate any help
Thanks
Mark

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 1

Cannt figure out why jobs 5 and 7 are to be in the result set while they overlap with job 2.

The query must ignore overlapping when ... what?

mark 4643 SSCommitted Points: 1503 More actions · Answer 2

serg-52 (9/17/2014)
Cannt figure out why jobs 5 and 7 are to be in the result set while they overlap with job 2.
The query must ignore overlapping when ... what?

Sorry, I edited the data and comments to make it clearer. basically anything that does not overlap the jobs currently on resource 1 should be selected.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Strange definition of overlapping eg. not counting cases when second job starts exactly at the same time as first does finish...

Anyway, you may try this:

;WITH AnyOverlapping

AS

(SELECT J1.resourceID, J1.JobNo, J1.StartTime, J1.EndTime,J1.ShouldBeOmitted

,J2.resourceID XresourceID, J2.JobNo XJobNo, J2.StartTime XStartTime, J2.EndTime XEndTime,J2.ShouldBeOmitted XShouldBeOmitted

FROM #Jobs J1

LEFT JOIN #Jobs J2

ON J2.JobNo < J1.JobNo

AND (

(J2.EndTime > J1.StartTime AND J2.EndTime <= J1.EndTime)

OR (J2.StartTime >= J1.StartTime AND J2.StartTime < J1.EndTime)

OR (J2.StartTime <= J1.StartTime AND J2.EndTime >= J1.EndTime)

)

SELECT *

FROM #Jobs J

WHERE NOT EXISTS (SELECT 1 FROM AnyOverlapping O1

WHERE EXISTS (SELECT 1 FROM AnyOverlapping O2 WHERE O2.JobNo = O1.XJobNo and O2.XJobNo IS NULL)

AND O1.JobNo = J.JobNo)

The above works for set of data you provided as sample, but I'm not sure it will cover all possible permitations of data you have.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

mark 4643 SSCommitted Points: 1503 More actions · Answer 4

Thanks Eugene

Worked great. Yeah it is bit of strange definition, but it's the spec I was given

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Perhaps I'm missing something but for each job, wouldn't you just compare the start date of the job with the end date of the other jobs and the end date of the job with the start date of the other jobs? If the end date of the other jobs is less than "the" job, it can't overlap and if the start date of the other jobs is greater than "the" job, it can't over lap there either. I'm thinking that only two conditions are needed instead of 3.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

Jeff Moden (9/17/2014)
Perhaps I'm missing something but for each job, wouldn't you just compare the start date of the job with the end date of the other jobs and the end date of the job with the start date of the other jobs? If the end date of the other jobs is less than "the" job, it can't overlap and if the start date of the other jobs is greater than "the" job, it can't over lap there either. I'm thinking that only two conditions are needed instead of 3.

I've just looked into this:

................................|-------------------|

>>>>...................................|-------------------------------|

.......................................................................|-------------------|

.............................|---------------------------------------------------------|

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 7

Overlap (x,y) == (x.Start <= y.End AND y.Start <= x.End)

Here overlap means intervals x and y have at least one common point. Intervals are closed, ie boundaries are included. Otherwise just replace '<=' with '<'

mark 4643 SSCommitted Points: 1503 More actions · Answer 8

Jeff Moden (9/17/2014)
Perhaps I'm missing something but for each job, wouldn't you just compare the start date of the job with the end date of the other jobs and the end date of the job with the start date of the other jobs? If the end date of the other jobs is less than "the" job, it can't overlap and if the start date of the other jobs is greater than "the" job, it can't over lap there either. I'm thinking that only two conditions are needed instead of 3.

Think you might be right Jeff. Will investigate