GSquared (8/28/2008)
The first query breaks them up into sets, based on the SetStart and SetEnd columns and the Date column.The second query then takes each set and gives it a sequence number within the set.
Is that what you're looking for? Is it clear enough?
Unfortunately this type of problem is not so clear to most users even in its recognition let alone a solution. Sql server doesn't help matters by offering no construct(s) to directly solve the problem. And what is the clearest, simplest way to characterize a problem like this so it becomes easy to communicate about it? This problem, along with relational division, appears to be sqls version of porn. You can't nail down the definition but you know it when you see it:)
These are two pretty good threads about approaching this type of problem. They do contain spoons but forks as well. FWIW I'm dassin, steve.
'searching for the longest subsequence of ones'
'Roll Up IP-Location Database'