Nice article.
When Jeff said, "even though the DateGroup looks like a date, it really doesn't mean anything," he was fibbing a bit, probably to keep unnecessary detail out of the article.
To see what the DateGroup means, we can watch what happens to it as we move through the test results from the first set of test data. It starts out as '2009-12-31', and it would keep that value on every row if there were no gaps in the dates. Each time we pass a gap, the number of days in the gap are added to the DateGroup. So the DateGroup means:
The day prior to the first day in our input data, adjusted by the number of days skipped along the way to our current row.
Or, put another way:
The day prior to what the first day in our input data would be, if we shuffled all the dates forward to fill in the gaps up to the current row.
OK, so Jeff was probably right to gloss over this, but hang on a minute. Now that we know what DateGroup is, we can use it to count the skipped days. It's just the number of days between our earliest date and DateGroup. Here I changed Jeff's query to group by DaysSkipped instead of DateGroup.
WITH
cteGroupedDates AS
(SELECT UniqueDate = SomeDate,
DaysSkipped = DATEDIFF(dd, MinDate, DATEADD(dd, - ROW_NUMBER() OVER (ORDER BY SomeDate), SomeDate) + 1)
FROM #MyHead
CROSS JOIN (SELECT MIN(SomeDate) AS MinDate FROM #MyHead) m
GROUP BY MinDate, SomeDate
)
SELECT StartDate = MIN(UniqueDate),
EndDate = MAX(UniqueDate),
Days = DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate))+1,
DaysSkipped
FROM cteGroupedDates
GROUP BY DaysSkipped
ORDER BY StartDate
;
This gives:
StartDate EndDate Days DaysSkipped
----------------------- ----------------------- ----------- -----------
2010-01-01 00:00:00.000 2010-01-01 00:00:00.000 1 0
2010-01-03 00:00:00.000 2010-01-03 00:00:00.000 1 1
2010-01-05 00:00:00.000 2010-01-06 00:00:00.000 2 2
2010-01-10 00:00:00.000 2010-01-12 00:00:00.000 3 5
DaysSkipped gives us a running total of the number of days in the gaps, which might be useful in some circumstances.