复杂 SQL 查询建议：预留分配逻辑

Question

我正在尝试在 SQL Server 2008 上执行一个复杂的查询（至少对我来说很复杂），到目前为止我可以做到这一点。这是代码；

DECLARE @Hotels AS TABLE(
  HotelID   INT, 
  HotelName NVARCHAR(100)
);

DECLARE @HotelAllotments AS TABLE(
  HotelID   INT, 
  StartDate DATETIME,
  EndDate   DATETIME,
  Allotment INT
);

DECLARE @Reservations AS TABLE(
  ReservationID INT,
  HotelID       INT, 
  CheckIn       DATETIME, 
  CheckOut      DATETIME, 
  IsCanceled    BIT
);

INSERT @Hotels VALUES(1,'Foo Hotel');
INSERT @Hotels VALUES(2,'Poo Hotel');

INSERT @HotelAllotments VALUES(1,'2011-01-01', '2011-02-01', 10);
INSERT @HotelAllotments VALUES(1,'2011-02-02', '2011-02-18', 7);
INSERT @HotelAllotments VALUES(1,'2011-02-19', '2011-05-18', 19);
INSERT @HotelAllotments VALUES(1,'2011-05-19', '2011-10-18', 30);
INSERT @HotelAllotments VALUES(2,'2011-05-19', '2011-10-18', 30);

INSERT @Reservations VALUES(100, 1, '2011-05-10','2011-05-24',0);
INSERT @Reservations VALUES(101, 1, '2011-05-18','2011-05-28',0);
INSERT @Reservations VALUES(102, 1, '2011-03-07','2011-03-19',0);
INSERT @Reservations VALUES(103, 1, '2011-08-29','2011-09-07',0);
INSERT @Reservations VALUES(104, 1, '2011-09-01','2011-09-07',1);
INSERT @Reservations VALUES(105, 1, '2011-09-01','2011-09-07',1);

with e as( 
  SELECT ReservationID as resid1, CheckIn as chin1, 1 as lvl
  FROM @Reservations res1
  WHERE res1.HotelID = 1
  UNION ALL
  SELECT ReservationID as resid2, DATEADD(DAY,1,stall.chin1) as chin2, 1
  FROM @Reservations res2
    INNER JOIN e stall ON stall.chin1 < res2.CheckOut
  WHERE stall.resid1 = res2.ReservationID
)
SELECT tb.chin1, SUM(lvl)
FROM e tb
GROUP BY tb.chin1
ORDER BY tb.chin1 DESC

在@HotelAllotments部分，您可以看到开始和结束日期。分配是按日计算的。我的意思是如果行如下所示；

INSERT @HotelAllotments VALUES(1,'2011-01-01', '2011-01-03', 10);

意思是这个；

• id 为 1 的酒店在 2011-01-01 拥有 10 个配额
• id 为 1 的酒店在 2011-01-02 拥有 10 个配额
• id 为 1 的酒店在 2011-01-03 拥有 10 个配额

然后，如果我们收到 2011-01-01 至 2011-01-03 之间的预订，例如 以下;

INSERT @Reservations VALUES(106, 1, '2011-01-01','2011-01-03',0);

情况如下；

• id 为 1 的酒店在 2011-01-01 预订后还剩 9 个房
• 位 id 为 1 的酒店在 2011-01-02 预订后还剩 9 个房
• 位 id 为 1 的酒店有 10 2011-01-03 预订后剩余分配

上面，我创建了一些临时表并插入了一些假值，然后我尝试查询。它让我到达某个地方（我不知道如何称呼它。所以如果你有一个 有机会运行查询，您会看到到目前为止它已经到达了我的位置），但不是我需要的地方。我在这里需要的是；

我需要列出酒店签订协议的所有日期以及收到预订后剩余的分配额。这是一个例子；

HotelID  Date        Allotment
-------  ----------  ---------
1        2011-01-01  9
1        2011-01-02  9
1        2011-01-03  10
1        2011-01-04  10
1        2011-01-05  10

那么我怎样才能实现这一目标呢？

编辑

有些人应该想知道为什么预订的前两天的分配被取消，但最后两天却没有。这是因为最后一天客人不会在酒店呆一整天。她/他应该清空房间直到中午 12:00。因此最后日期不会有任何分配使用。

Answer 1

;WITH expanded AS (
  SELECT
    a.HotelID,
    Date = DATEADD(DAY, v.number, a.StartDate),
    a.Allotment
  FROM @HotelAllotments a
    INNER JOIN master..spt_values v ON v.type = 'P'
      AND v.number BETWEEN 0 AND DATEDIFF(DAY, a.StartDate, a.EndDate)
),
filtered AS (
  SELECT
    e.HotelID,
    e.Date,
    Allotment = e.Allotment - COUNT(r.ReservationID)
  FROM expanded e
    LEFT JOIN @Reservations r ON e.HotelID = r.HotelID
      AND e.Date >= r.CheckIn AND e.Date < r.CheckOut
      AND r.IsCanceled = 0
  GROUP BY e.HotelID, e.Date, e.Allotment
)
SELECT *
FROM filtered;

此解决方案使用系统表 master..spt_values，作为<一href="http://www.sqlservercentral.com/articles/T-SQL/62867/" rel="nofollow noreferrer" title="“Numbers”或“Tally”表：它是什么以及它如何替换一个循环。">统计表来获取日期列表而不是日期范围。接下来，扩展的分配列表与@Resevations 表连接。对于列表中的每个日期，相应的分配都会减少范围与给定日期匹配的预订数量。

Answer 2

我写 where 子句有点仓促。不知道你是否想整理一下空白的日子。这是我在设置 where 子句后想到的。我使用 datejumps 的原因是为了补偿 sql 中 100 次重复调用的限制。因此，我加入系统表中的 10 行，更好地利用 100 递归，这样我可以获得 1000 行而不是 100 行。

WITH cte(HOTELID, STARTDATE, ENDDATE, Allotment)
as
(
SELECT H.HOTELID, A.STARTDATE + RN STARTDATE, (SELECT MAX(ENDDATE) FROM @HotelAllotments) ENDDATE,  (select Allotment from @HotelAllotments where A.STARTDATE + RN between StartDate and enddate and H.HOTELID = HOTELID) Allotment
FROM (
SELECT MIN(STARTDATE) STARTDATE from @HotelAllotments c    
) A,
(SELECT TOP 10 rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1 FROM INFORMATION_SCHEMA.COLUMNS) B,
@Hotels H 
UNION ALL
SELECT ch.HOTELID, ch.STARTDATE + 10, ENDDATE, (select Allotment from @HotelAllotments where CH.STARTDATE + 10 between StartDate and enddate and CH.HOTELID = HOTELID)
FROM cte ch    
WHERE CH.STARTDATE<  ENDDATE
AND CH.HOTELID = HOTELID
)
SELECT HotelID,  StartDate Date , Allotment - (select count(*) from @Reservations where cte.STARTDATE between CheckIn and CheckOut and cte.HOTELID = HOTELID) Allotment
FROM CTE where allotment is not null
ORDER BY STARTDATE, HOTELID