Query to ignore overlapping dates

[salma]

Hello

I have a table that stores start and end times for machine faults. The times often overlap because when one fault occurs it can cause a knock on effect and trigger several other fault conditions.

I need to query this table and only bring back the rows showing the first fault in the sequence, i.e. where the start time does not fall between any earlier start / end times.

e.g.

CREATE TABLE temp_start_end_datetimes (
counter integer identity(1,1),
start_time DATETIME,
end_time DATETIME
)
GO

INSERT INTO temp_start_end_datetimes
SELECT '2005-01-01 01:00:00', '2005-01-01 01:01:00' UNION
SELECT '2005-01-01 01:05:00', '2005-01-01 01:10:00' UNION
SELECT '2005-01-01 01:07:00', '2005-01-01 01:08:00' UNION
SELECT '2005-01-01 01:08:00', '2005-01-01 01:09:00' UNION
SELECT '2005-01-01 01:10:00', '2005-01-01 01:30:00' UNION
SELECT '2005-01-01 01:11:00', '2005-01-01 01:12:00'

will return:

1 2005-01-01 01:00:00 2005-01-01 01:01:00
2 2005-01-01 01:05:00 2005-01-01 01:10:00
3 2005-01-01 01:07:00 2005-01-01 01:08:00
4 2005-01-01 01:08:00 2005-01-01 01:09:00
5 2005-01-01 01:10:00 2005-01-01 01:30:00
6 2005-01-01 01:11:00 2005-01-01 01:12:00

I only want to return records 1, 2 and 5. I have tried several methods but cannot get anything to work and would appreciate any advice.

I hope this makes sense!

Thanks in advance
Salma

 

[vongrunt]

> I need to query this table and only bring back the rows showing the first fault in the sequence, i.e. where the start time does not fall between any earlier start / end times.

Any or only previous? If the answer is "any" then record #5 touches edge of interval from record #2...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[salma]

I am working on the assumption that if a fault ends at a particular time and another starts at that exact same time, both records would be valid.

I will ultimately be calculating fault durations. I already have this query but because I have all the symptomatic faults in the results it is skewing my results - my total daily duration is coming out to more than 86,400 seconds!

So yes I would expect to return both records 2 and 5.

Does this help?

 

[vongrunt]

Lemme elaborate: final result must contain "merged" intervals:

2005-01-01 01:00:00 - 2005-01-01 01:01:00
2005-01-01 01:05:00 - 2005-01-01 01:30:00

From that point on total failure time (during which at least one machine failed) is trivial to calculate. Correct?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[amrita418]

If the data is ordered, i.e. the counter is set by the ascending order of start and end times then something like this should get you the output.

select  distinct 
	a.* 
from 	#temp_start_end_datetimes a, 
	#temp_start_end_datetimes b
where 	(b.counter = a.counter + 1
	and  (a.start_time <= b.start_time
	and  a.end_time >=  b.end_time)
	OR a.counter = 1)

Regards,
AA

 

[vongrunt]

Another sample data:

TRUNCATE TABLE temp_start_end_datetimes

INSERT INTO temp_start_end_datetimes
SELECT '2005-01-01 01:00:00', '2005-01-01 01:01:00' UNION
SELECT '2005-01-01 01:05:00', '2005-01-01 01:15:00' UNION
SELECT '2005-01-01 01:10:00', '2005-01-01 01:20:00' UNION
SELECT '2005-01-01 01:30:00', '2005-01-01 01:40:00'

What are expected results?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[salma]

Hi AA
Unfortunately the counter is not always sequentially ordered and may contain gaps so it can't be used in that way

Hi Vongrunt,
In my sample data I need the three rows returned seperately.
1 2005-01-01 01:00:00 2005-01-01 01:01:00
2 2005-01-01 01:05:00 2005-01-01 01:10:00
5 2005-01-01 01:10:00 2005-01-01 01:30:00
This is because the table contains other information such as fault text and machine name.

I need to find out within a given period what length of time a machine was down for and what was the main cause. This means that any faults that occur after the first fault need to be ignored until the first fault is cleared. That way I can get an accurate duration for downtime attributed to individual faults.

In your sample data set you have identified a problem that I was ignoring for the moment! But to answer your question, I would need to return
'2005-01-01 01:00:00', '2005-01-01 01:01:00'
'2005-01-01 01:05:00', '2005-01-01 01:15:00'
'2005-01-01 01:15:00', '2005-01-01 01:20:00' - start time is changed to end time of previous record
'2005-01-01 01:30:00', '2005-01-01 01:40:00'

I'm not even sure this can be done (without cursors). I think I need to re-evaluate my original question as I'm sure I'm missing something fundamental. Thanks for the help.

Salma