CODEWITHSUNDEEP
sqldatesql-server-2008
Conger88
Conger88

Reputation: 55

SQL find duplicate active records between two date ranges

I have two tables, a Patients table and an Insurance Table. One patient can have multiple Insurances. I am trying to find all Insurances that have overlapping active dates or that show two as being active.

PATID    START_DTTM           END_DTTM
1        2002-09-10 00:00:00.000  NULL
1        2007-03-06 10:18:00.000  2019-04-11 11:59:00.000

If END_DTTM is Null then it is active. A start date should start when the subsequent end date ends. I am trying to find all entries where the active dates overlap or that it shows two active entries for a period if that makes sense?

Also to add to the mix an Patient can have multiple insurances, the above example shows a patient with two insurance details. they could also have a third or fourth entry...

Any help would be great

Upvotes: 0

Views: 1605

Answers (2)

ecarter
ecarter

Reputation: 1

If your insurance table also has a unique id for each insurance (we would hope so) then you could have a query like this

declare @tab table (
    patid int
    , insid int
    , start_dttm datetime
    , end_dttm datetime
)

insert into @tab values (1, 8, '2002-09-10', NULL)
                        , (1, 9, '2007-03-06', '2019-04-11')
                        , (53, 321513, '2015-01-13', NULL )
                        , (53, 11, '2008-08-14', '2015-01-13')
                        , (54, 12, '2015-01-13', NULL )
                        , (54, 13, '2008-08-14', '2015-01-12')

select      a.*
            , b.*
            , 'Insurance record ' + cast(b.insid as varchar) + ' (' + convert(varchar,b.start_dttm,103) + ' to ' + convert(varchar,b.end_dttm,103) + ') '
             + 'overlaps Insurance record ' + cast(a.insid as varchar) + ' (' + convert(varchar,a.start_dttm,103) + isnull(' to ' + convert(varchar,a.end_dttm,103), ' still active') + ')'

from        @tab a
inner join  @tab b
on          a.patid = b.patid
and         a.insid != b.insid
where       (b.start_dttm > a.start_dttm and b.start_dttm < isnull(a.end_dttm, getdate()+1))
or          (b.end_dttm > a.start_dttm and b.start_dttm < isnull(a.end_dttm, getdate()+1))
or          (a.end_dttm is null and b.end_dttm is null)

Note - You would not need to create a table variable @tab like I have done, just use your insurance table.

Upvotes: 0

Serg
Serg

Reputation: 22811

This will list all insuarances which has at least one other insuarance overlapping.

select ID, PATID, START_DTTM, END_DTTM
from insurances i1
where exists (select null 
              from insurances i2
              where i1.ID != i2.ID and i1.PATID = i2.PATID
                and (i1.START_DTTM  <= i2.END_DTTM or i2.END_DTTM is null)
                and (i2.START_DTTM  <= i1.END_DTTM or i1.END_DTTM is null)
             )
order by PATID, START_DTTM;

Two active insurances (null end date) are considered overlapping. You may wish to change <= to < if equal start /end dates are not considered overlapping.

Upvotes: 1

Related Questions