Merging all overlapping time periods in a table

[ludmart]

This is a simplified example of my actual table:


CREATE TABLE TimePeriods(
TimePeriodID int IDENTITY (1, 1) NOT NULL ,
StartDateTime datetime NOT NULL ,
EndDateTime datetime NOT NULL
) ON [PRIMARY]


StartDateTime will always be less than EndDateTime. The table may contain overlapping time periods, eg:-

TimePeriodID StartDT EndDT
------------ --------------------------- -------------------
1 2001-01-12 12:00 2001-01-19 18:00
2 2001-01-15 13:45 2001-02-19 17:00
3 2001-02-22 11:19 2001-02-27 17:19
(Note: I trimmed the seconds and ms from the DateTime to make them fit)

Note that rows 1 and 2 overlap. I need a way to generate a result set to mertge all overlapping time periods, so that
the example data will generate this:

TimePeriodID StartDT EndDT
------------ --------------------------- -----------
1 2001-01-12 12:00 2001-02-19 17:00
3 2001-02-22 11:19 2001-02-27 17:19

Any suggestions? My actual table may contain thousands of rows.