Vfrdtyky

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!

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