如何查询重叠的日期范围？ [英] How can I query for overlapping date ranges?

问题描述

我正在使用SQL Server 2008 R2，并试图创建一个查询来显示日期是否重叠。

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') 

我希望能够计算一个人从第一个RX_DATE到最后一个END_DT所经历的天数。 90天（4/3/13-1/3/13）。

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.

在第1行和第2行之间，有3天的时间没有毒品。在第3行和第4行之间有25天的时间。但是，在那25天的时间内，第1行弥补了这一缺口。因此，对于行1和行2之间的间隙，我需要显示的结束号是3。

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.

任何帮助将不胜感激。

谢谢。

推荐答案

也许有更好的方法，但是您可以创建几天的查询，加入并选择要加入的不同天数，这将为您提供所有行涵盖的天数总数：

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

-涵盖天数

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

-Tota l天

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

-组合

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

您说90天，但我相信您应该有91天。与周一至周三的日期差异只有2天，但涵盖的时间为3天。但是您可以决定覆盖范围是从rx日期还是第二天开始。

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.

这篇关于如何查询重叠的日期范围？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！