查找StartDate和EndDate之间的重叠，以查看在同一天是否有超过3个事件

Question

所以我有一个SQL SERVER 2008表，有一个项目列表。一个人每天最多只能订购4件物品。因此，我想知道在任何开始日期或结束日期，是否有一个时间，他们有超过4项的特定的一天。

下面是我的表格的一个例子:

OrderNo  Item     quantity   StartDate   EndDate
112      xbox        2       2012-12-05  2012-12-10
123      tv          1       2012-12-06  2012-12-07
125      computer    4       2012-12-10  2012-12-11
165      game        1       2012-12-06  2012-12-10
186      toy         2       2012-12-02  2012-12-04

所以从这张表中我们可以看到他们每天有超过4个项目…现在我需要知道他们有多少件物品，哪些天他们有超过4件物品。

基本上我想检查重叠的日期，从项目出来的时候，当它被返回，看看是否有超过4个项目在同一时间在一个特定的日期。

我不知道如何处理这个问题。我查看了SQL中许多重叠的日期和范围。

Answer 1

您需要一个日历表。理想情况下，这是一个在主数据库中设置并正确索引的永久表，但您可以动态创建它，如:

WITH Calendar
AS
(
    SELECT MIN(StartDate) AS Today
          ,MAX(EndDate) AS LastDay
    FROM table
    UNION ALL
    SELECT DATEADD(day,1,Today)
          ,LastDay
    FROM Calendar
    WHERE Today<LastDay
)

注意:你有一个正常的最大递归100，所以你最多可以得到100天，除非你加上OPTION (MAXRECURSION n)，其中n是小于32768的int。

你现在有一个表，你可以连接到你的原始表，包括所有相关的日期，像这样

SELECT Today
      ,SUM(Quantity) AS ItemCount
FROM Calendar c
     INNER JOIN 
     Table t ON c.Today BETWEEN t.StartDate AND t.EndDate
GROUP BY Today
HAVING SUM(Quantity)>4

查看此SQL文件

这个SQL代码给出了一个"永久"日历表的解决方案。

Answer 2

这个使用运行总数来汇总每个日期订购的项目。要检查任何日期(样本数据中未给出)，只需使用此结果并执行一些连接。有关序号/日期的更多信息，请查看孤岛和间隙

insert into "timestamps"
select
  *
from
(
select "OrderNo", "ts", "quantity", case when "timestamp" = 'StartDate' then 1 else -1 end as "factor", 0 as "rolSum" from
    ( select "OrderNo", "StartDate", "EndDate", "quantity" from "data" ) as d
    unpivot ( "ts" for "timestamp" in ( "StartDate", "EndDate" )) as pvt
) as data
order by "ts"
declare @rolSum int = 0
update "timestamps" set @rolSum = "rolSum" = @rolSum + "quantity" * "factor" from "timestamps"
select 
    "OrderNo"
  , "ts"
  , "rolSum"
from
  "timestamps"