I love the little challenges Ayende Rahien puts on his blog. The last SQL challenge was to convert a table like this:
FromDate
ToDate
01/01/2000
01/31/2000
02/01/2000
02/28/2000
03/05/2000
03/31/2000
Into this:
FromDate
ToDate
01/01/2000
02/28/2000
03/05/2000
03/31/2000
Where adjacent date ranges collapse to a single record. The first step was to setup some experimental tables.
CREATE TABLE #InDates( FromDate datetime, ToDate datetime)INSERT INTO #InDates VALUES('1/1/2000', '1/31/2000')INSERT INTO #InDates VALUES('2/1/2000', '2/28/2000')INSERT INTO #InDates VALUES('3/5/2000', '3/31/2000')CREATE TABLE #OutDates( FromDate datetime, ToDate datetime)
I have to think about SQL in small chunks, so I first thought about how to get a resultset with a FromDate minus one day. That would give me a column to compare against another record's ToDate:
SELECT DATEADD(dd, -1,...