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
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