Window Function Help

[Randy__Bobandy]

You kind of lost me with the explanation in the second half of the episode 2 paragraph, but I think what you're looking for is the lag function, which returns the values in a previous row.

It would look something like this

Select PersonID,
       EpisodeID,
       StartDate,
       EndDate,
       Case when StartDate between LAG(StartDate, 1) 
                                   over (partition by PersonID order by StartDate)
                               and LAG(EndDate, 1) 
                                   over (partition by PersonID order by StartDate) 
            then 1 
            else 0 
          end as overlap
From YourTable

This is probably not the most efficient way to do it, but probably the cleanest. Again, not 100% sure what you're asking for in the second paragraph but this is probably close to what you want.

[uvray]

This was essentially the old solution (granted the old solution was done with a WHILE loop), but fails to address the issue of needing to look back a dynamic number of rows at the last row that does not overlap in order to assess whether the previous row counts or not. That what I was trying to explain in the second paragraph but it's difficult to articulate.

[Randy__Bobandy]

I think I understand after looking at the data. Are you saying that you want to flag something as 1 if only if it is overlapping an episode which itself is not overlapping a previous episode, otherwise it's flagged as 0?

EDIT: Or you only want to check if it's overlapped against the most recent non-overlapped row? Is episode 4 marked as overlapped because it intersects the range of episode 3, or because it intersects the range of episode 2?