I have the following table:
What I am trying to do is to return ranges of dates for each group which are identified by the missing day flag. To make this more clear the results of the query will have to look like this:
The is_missing flag basicaly separates the ranges per group. It actually says that a date is missing and therefore all the other dates located between is_missing flags are the groups I am trying to find their start and end dates as well as their days numbers count.
Is there a simple way to do this?
Thanks a lot.
You can go about it using windowing functions, using the idea of islands and gaps.
You identify the groups of consecutive dates inside of a
Translated into T-SQL:
That should do what you want.
answered May 29, 2011 at 07:45 AM