-- Create the sample events table
CREATE TABLE Events
(event_date DATETIME NOT NULL PRIMARY KEY,
event_venue VARCHAR(20) NOT NULL);
-- Insert venue event dates
INSERT INTO Events VALUES ('20080101', 'The Palace');
INSERT INTO Events VALUES ('20080201', 'The Palace');
INSERT INTO Events VALUES ('20080301', 'The Palace');
INSERT INTO Events VALUES ('20080401', 'The Palace');
INSERT INTO Events VALUES ('20080501', 'The Palace');
INSERT INTO Events VALUES ('20080601', 'Fox Theater');
INSERT INTO Events VALUES ('20080701', 'Fox Theater');
INSERT INTO Events VALUES ('20080801', 'Grand Hall');
INSERT INTO Events VALUES ('20080901', 'Grand Hall');
INSERT INTO Events VALUES ('20081001', 'Grand Hall');
INSERT INTO Events VALUES ('20081101', 'The Palace');
INSERT INTO Events VALUES ('20071201', 'The River Place');
INSERT INTO Events VALUES ('20081202', 'The River Place');
-- Group the event period dates at each venue
SELECT MIN(event_venue) AS venue,
MIN(event_date) AS venue_start_date,
MAX(event_date) AS venue_end_date
FROM (
SELECT event_venue , event_date ,
ROW_NUMBER() OVER (ORDER BY event_venue, event_date) -
ROW_NUMBER() OVER (PARTITION BY event_venue
ORDER BY event_date),
ROW_NUMBER() OVER (ORDER BY event_date) -
ROW_NUMBER() OVER (PARTITION BY event_venue
ORDER BY event_date)
FROM Events) AS X(event_venue, event_date, grp1, grp2)
GROUP BY grp1, grp2;
/*
-- Results
venue venue_start_date venue_end_date
-------------------- ----------------- --------------
The River Place 2007-12-01 2007-12-01
The Palace 2008-01-01 2008-05-01
Fox Theater 2008-06-01 2008-07-01
The Palace 2008-11-01 2008-11-01
Grand Hall 2008-08-01 2008-10-01
The River Place 2008-12-02 2008-12-02
*/
Labels: t-sql programming