Complex SQL Query advise : Reservation allotment logic

Question

I am trying to do a complex query (at least, it is complex for me) on SQL Server 2008 and so far I can come this far. Here is the code;

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

On @HotelAllotments section, there are start and end dates as you can see. The allotment is for daily basis. I mean if row is like below;

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

It means this;

• The Hotel whose id is 1 has 10 allotment on 2011-01-01
• The Hotel whose id is 1 has 10 allotment on 2011-01-02
• The Hotel whose id is 1 has 10 allotment on 2011-01-03

Then, after that if we receive a reservation between 2011-01-01 and 2011-01-03, like below;

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

The situation will be as below;

• The Hotel whose id is 1 has 9 allotment left after the reservation on 2011-01-01
• The Hotel whose id is 1 has 9 allotment left after the reservation on 2011-01-02
• The Hotel whose id is 1 has 10 allotment left after the reservation on 2011-01-03

Above, I have created some temp tables and inserted some fake values and I tried a query. It gets me somewhere (I don't know how to call it. So if you have a chance to run the query, you would see where it has gotten me so far) but not the place I need. What I need here is that;

I need to list all the dates which a hotel has an agreement and its left allotments after received reservations. here is an example;

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

So how can I achieve this?

EDIT

Some them should wonder why an allotment is taken away for the first two days of the reservation, but not the last one. It is because the guest wouldn't be staying all day at the hotel at the last day. S/he should empty the room until 12:00 am. So there won't be any allotment usage on the last date.

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;

This solution uses a system table, master..spt_values, as a tally table to obtain the lists of dates instead of the date ranges. Next, the expanded allotment list is joined with the @Resevations table. For every date in the list, the correpsonding allotment is decreased by the number of reservations whose ranges match the given date.

Answer 2

I was a bit hasty on writing my where clause. I didnt know if you wanted to sort out the blank days. here is what i came up with after setting the where clause. The reason i have the datejumps is to compensate for the limitation of 100 recusive calls in sql. So I join with 10 rows from a system table make better use of the 100 recusive, that way i can get 1000 rows instead of 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