interesting SQL, consolidate time period
it's post on usenet, intersting problem, let think about it. Siege had given the answers.
Given a very simple table of events, with
their durations, I want to consolidate time periods for an event that
(1) touch each other or (2) overlap each other.
CREATE TABLE Events
(event_id VARCHAR(25) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CHECK (start_date <= end_date),
PRIMARY KEY (event_id, start_date, end_date));
INSERT INTO Events
VALUES ('Pie Eating', '2009-01-01', '2009-01-02'),
('Pie Eating', '2009-01-03', '2009-01-05'),
('Pie Eating', '2009-01-04', '2009-01-07'),
('Pie Eating', '2009-02-01', '2009-02-07');
The goal is to reduce these three rows into fewer rows that show how
many days we were eating pies.
('Pie Eating', '2009-01-01', '2009-01-07')
The first and second rows in the INSERT INTO statement touch each
other and can be replaced with:
('Pie Eating', '2009-01-01', '2009-01-05')
with it, as shown before. However, the fourth row has data that
occurs a month after the first three and it is disjoint from all the
other rows.
