I love the little challenges Ayende Rahien puts on his blog. The last SQL challenge was to convert a table like this:
Where adjacent date ranges collapse to a single record. The first step was to setup some experimental tables.
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:
Typically these kind of solutions involve a self join, so I added the above into the following query:
Which generates the following resultset:
Looking at that resultset it was easy to see that coalescing the ToDate into the NULL value left behind by a LEFT JOIN and using a GROUP BY would process the sample data.
Then Ayende had to up the ante by updating the post with a harder challenge. This one has me scratching my head. I ended up with the above SELECT query inside a SQL 2005 common table expression. I’m sure someone will come along and tell me the following query is horribly inefficient, and terribly wrong:
The query plan is an ugly beast (but not the ugliest). I think I need to step away from this problem. There has to be a more elegant solution.