SQL 2005 solution utilising ROW_NUMBER. This is a resubmission as my previous entry included an additional column I was using to debug! Oops. Divide each ID partition by 2 further partitions: one where interval date is defined, the other where it is not.

SQL 2005 solution utilising ROW_NUMBER. Divide each ID partition into 2 further partitions: one where IntervalDate is defined, the other where it is not. Derive the rank of the elements using ROW_NUMBER in Interval order. The rank of the partition contai

I am not sure this problem is fully defined and if it were a work project, I would certainly call a meeting for information. Since it is not, here is my solution. I assume that if the ID was not always the same, then the episode would start at 1 for each

1

1. Use CTE 2. First select the records which IntervalDate is not null. 3. Then Join CTE with TC75 to get the one with Interval+1 and IntervalDate is NULL. 4. Calculate the row number partition by ID, Episode and Order BY Interval

Subquery in place of CTE, 2pass solution.

nonRecursive, ver 2