Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

9.4. Consolidating Intervals

Given different data sources, you will get incomplete data about an event that you need to convert into a minimal representation. To make the problem more concrete, let’s assume a simple table that looks a lot like a clipboard:

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', ...