This is a 3rd attempt at the solution. This solution will replace all previous solutions. This solution corrects a hard coded number with some scripts.

This solution is to replace an earlier solution submitted. While the older solution appears to give an answer to the sample given when I added in some more dates a hole appeared. This newer solution overcomes that problem. Regards.

!. Sort Startdate by GroupID and assign a row number to each row 2. Count items in each group 3. Find the Disjoint Startdate to gauge the possible gaps 4. Find the minimum connecting groups as Keypoints 5. List the first and last items as anchors 6. I

This is an edit to my previous solution. It runs much quicker since it now eliminates unnecessary joins in the recursion process. -Zane

What this solution first does is it uses a recursive CTE to traverse through all possible date-interval sequence paths in order to find the earliest recursion level at which the last date-interval appears. Once one or more occurrences of the last date-inte

Basically get all valid record combinations per the rules and keep a running total of days, then grab the combo with the min # of days, finally compound a list of all start dates as your creating all combos and use the 'list' to determine which records to