CODEWITHSUNDEEP
sqlsql-serverdatetimeoverlapping
Phil
Phil

Reputation: 57

How can I query for overlapping date ranges?

I'm using SQL Server 2008 R2 and trying to create a query that will show whether dates overlap.

I'm trying to calculate the number of days someone is covered under a certain criteria. Here is an example of the table...

CREATE TABLE    mytable
(   

CARDNBR varchar(10) 
GPI char(14)    ,
GPI_DESCRIPTION_10  varchar(50) ,
RX_DATE datetime    ,
DAYS_SUPPLY int ,
END_DT  datetime    ,
METRIC_QUANTITY float   

)       
INSERT INTO mytable VALUES ('1234567890','27200040000315','Glyburide','01/30/2013','30','03/01/2013','60')      
INSERT INTO mytable VALUES ('1234567890','27200040000315','Glyburide','03/04/2013','30','04/03/2013','60')      
INSERT INTO mytable VALUES ('1234567890','27250050007520','Metformin','01/03/2013','30','02/02/2013','120')     
INSERT INTO mytable VALUES ('1234567890','27250050007520','Metformin','02/27/2013','30','03/29/2013','120')

I want to be able to count the number of days that a person was covered from the first RX_DATE to the last END_DT, which in this example is 90 days (4/3/13 - 1/3/13).

That part is done, but this is where I'm getting into trouble.

Between row 1 and row 2, there was a 3 day period where there were no drugs being taken. Between rows 3 and 4 there was a 25 day period. However, during that 25 day period, row 1 covered that gap. So the end number I need to show is 3 for the gap between rows 1 and 2.

Any help would be greatly appreciated.

Thanks.

Upvotes: 1

Views: 1785

Answers (1)

Hart CO
Hart CO

Reputation: 34784

There might be a better approach, but you could create a lookup of days, join to it and select the distinct days that join, that will get you the total count of days covered for all lines:

CREATE TABLE #lkp_Calendar (Dt DATE)
GO
SET NOCOUNT ON
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=500)
BEGIN
--Loop through this:
INSERT INTO #lkp_Calendar 
SELECT DATEADD(day,@intFlag,'20120101')
SET @intFlag = @intFlag + 1
END
GO

--Days Covered 

SELECT CARDNBR, COUNT(DISTINCT b.Dt)CT
FROM  #mytable a
JOIN #lkp_Calendar b
    ON b.Dt BETWEEN a.RX_DATE AND a.END_DT
GROUP BY CARDNBR

--Total Days

SELECT CARDNBR, DATEDIFF(DAY,MIN(RX_DATE),MAX(END_DT))+1 'Total_Days'
        FROM #mytable
        GROUP BY CARDNBR

--Combined

SELECT covered.CARDNBR, covered.CT 'Days Covered', total.Total_Days 'Total Days', total.Total_Days - covered.CT 'Days Gap'
FROM  (SELECT CARDNBR, COUNT(DISTINCT b.Dt)CT
        FROM  #mytable a
        JOIN #lkp_Calendar b
            ON b.Dt BETWEEN a.RX_DATE AND a.END_DT
        GROUP BY CARDNBR
      )covered
JOIN (SELECT CARDNBR, DATEDIFF(DAY,MIN(RX_DATE),MAX(END_DT))+1 'Total_Days'
        FROM #mytable
        GROUP BY CARDNBR
     )total
ON covered.CARDNBR = total.CARDNBR

You said 90 days, but I believe you should have 91. Date diff from Mon-Wed is only 2, but that's 3 days covered. But you can decide if coverage begins on the rx date or the day after.

Upvotes: 1

Related Questions