O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required