September 17, 2014 at 4:35 am
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
September 17, 2014 at 6:51 am
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?
September 17, 2014 at 7:27 am
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.
September 17, 2014 at 8:12 am
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.
September 17, 2014 at 4:48 pm
Thanks Eugene
Worked great. Yeah it is bit of strange definition, but it's the spec I was given
September 17, 2014 at 8:31 pm
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
September 18, 2014 at 2:35 am
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:
................................|-------------------|
>>>>...................................|-------------------------------|
.......................................................................|-------------------|
.............................|---------------------------------------------------------|
September 18, 2014 at 8:31 am
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 '<'
September 19, 2014 at 11:52 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply