SQL Continuous Date Ranges Previously Grouped Data

I’m working with a data extract that represents claims data for a population. The ultimate goal is to be able to associate specific claims to unique encounters. The way we’d define an encounter is a string of claims that are continuous, or within the same range; meaning the claim-to date is followed up by a claim-from date immediately after, or has an identical to & from date as another claim. However, because there is no key that actually indicates that a group of claims represent a specific encounter, we have to implement logic that does this for us.

For the sake of this example, we’ve already grouped the data by Patient & Facility.

CREATE TABLE #BillingData (
ClaimID VARCHAR(MAX)
,Patient VARCHAR(MAX)
,Facility VARCHAR(MAX)
,ClaimFromDate DATE
,ClaimToDate DATE
,GroupID VARCHAR(MAX)
)

INSERT INTO #BillingData
VALUES
('Claim1','JOHN DOE','NURSING HOME','2000-01-01','2000-01-01','1')
,('Claim2','JOHN DOE','NURSING HOME','2000-01-01','2000-01-31','1')
,('Claim3','JOHN DOE','NURSING HOME','2000-01-01','2000-01-31','1')
,('Claim4','JOHN DOE','NURSING HOME','2000-01-01','2000-01-31','1')
,('Claim5','JOHN DOE','NURSING HOME','2000-02-01','2000-02-29','1')
,('Claim6','JOHN DOE','NURSING HOME','2000-03-01','2000-03-31','1')
,('Claim7','JOHN DOE','NURSING HOME','2000-04-01','2000-04-30','1')
,('Claim8','JOHN DOE','NURSING HOME','2000-08-01','2000-08-31','1')
,('Claim9','JOHN DOE','NURSING HOME','2000-09-01','2000-09-30','1')
,('Claim10','JANE DOE','HOME HEALTH','2000-02-01','2000-02-29','2')
,('Claim11','JANE DOE','HOME HEALTH','2000-03-01','2000-03-31','2')
,('Claim11','JANE DOE','HOME HEALTH','2000-03-01','2000-03-31','2')

What’d I’d like to see ultimately is an encounter ID. This is how the individual claims should be placed into encounter IDs:

Claims 1,2,3,4,5,6,7 are in Encounter 1.1; Claims 8,9 are in Encounter 1.2; Claims 10,11 are in Encounter 2.1

Any guidance would be greatly appreciated!

Answer

You need to find the gaps, i.e. the current start date is greater than any previous end date (plus one day)

with gaps as
 (
   select *,
      case
        when max(ClaimToDate) -- max previous end date
             over (partition by Patient, Facility
                   order by ClaimFromDate, ClaimID
                   rows between unbounded preceding and 1 preceding)
            < dateadd(day,-1,ClaimFromDate) -- compare to current start date 
        then 1 -- gap
        else 0 -- overlapping ranges
      end as flag
   from #BillingData
 )
select *,
   sum(flag) -- cumulative sum over 0/1 to create encounter number 
   over (partition by Patient, Facility
         order by ClaimFromDate, ClaimID
         rows unbounded preceding) +1 as encounter
from gaps
order by GroupID, encounter, ClaimFromDate

See fiddle