Programming hotel reservation software can be a huge challenge. Guests stay between an arrival date and departure date, but that only includes the nights, so it technically does not include the departure date since the room that night is available for another guest. Most hotels close books at midnight. Guests can share rooms and sometimes split the fees. Guests can pay via multiple payment methods, including company and personal credit cards, coupons, and, of course, cash, at multiple different places in the hotel complex. Point of sale systems in the hotel restaurant, bar, or gift shop must communicate with the main system to allow certain charges to be billed to the room. And reporting is the biggest challenge, especially by analysts who may not understand the system or the intracacies of the business. (etc. etc.)
Okay, enough prelude...
So how do you write a query to determine the rooms that were filled on a certain date? Better yet, we need to show a list of rooms that were filled for a certain historical week, sorted and grouped by customer.
The database is Oracle but it can be done in SQL Server as well without much modification.
To demonstrate, let's create a table and add some data.
CREATE TABLE TEST_RES
(res_id NUMBER,
customer_id NUMBER,
vendor_id NUMBER,
arrival_date DATE,
departure_date DATE,
CONSTRAINT test_res_u1
PRIMARY KEY ( RES_ID ));
Now we add some data:
INSERT INTO test_res
(res_id, customer_id, vendor_id, arrival_date, departure_date)
VALUES
(1, 234, 9842, to_date('04-Apr-06'), to_date('22-Apr-06'));
INSERT INTO test_res
(res_id, customer_id, vendor_id, arrival_date, departure_date)
VALUES
(2, 234, 9842, to_date('11-Apr-06'), to_date('22-Apr-06'));
INSERT INTO test_res
(res_id, customer_id, vendor_id, arrival_date, departure_date)
VALUES
(3, 638, 9842, to_date('11-Apr-06'), to_date('13-Apr-06'));
INSERT INTO test_res
(res_id, customer_id, vendor_id, arrival_date, departure_date)
VALUES
(4, 638, 9842, to_date('15-Apr-06'), to_date('18-Apr-06'));
INSERT INTO test_res
(res_id, customer_id, vendor_id, arrival_date, departure_date)
VALUES
(5, 638, 9842, to_date('28-Apr-06'), to_date('30-Apr-06'));
This gives us data that looks like this:
| RES_ID |
CUSTOMER_ID |
VENDOR_ID |
ARRIVAL_DATE |
DEPARTURE_DATE |
| 1 |
234 |
9842 |
4/4/2006 |
4/22/2006 |
| 2 |
234 |
9842 |
4/11/2006 |
4/22/2006 |
| 3 |
638 |
9842 |
4/11/2006 |
4/13/2006 |
| 4 |
638 |
9842 |
4/15/2006 |
4/18/2006 |
| 5 |
638 |
9842 |
4/28/2006 |
4/30/2006 |
(the vendor is the hotel and is the same in every record in this example)
I want to see how many rooms were full on a given night, including a whole week of data. It must be historical, so I can't go back x-days from today (sysdate). (I don't care3 how many people were in the room, only that the room was occupied and I couldn't sell it to anyone else - it was full).
Because the records have an arrival date and departure date, I don't have discrete records of individual days to query. So I can't use the Between statement in the Where clause of the SQL.
A way to do this is to join with some other table to get discrete days. I am only looking at one week, so I only need seven discrete days.
I can do this in Oracle using any table, using a query similar to this one:
SELECT (to_date(:week)) + rownum -1 bill_date,
rownum day_key
FROM po_vendors
WHERE rownum <= 7
Note that I used po_vendors from the Oracle applications/ERP system, but you can use any table, even one that does not have the range of dates in it. The only requirement is that the table must have at least seven records in it. The variable :week is used in TOAD to enter in the date. I would use &week in PL/SQL Developer. In this case, :week is the date of the first day of the historical week that I want to query.
This query gives me the following data if I pass in '11-APR-06' as the date:
| BILL_DATE |
DAY_KEY |
| 4/11/2006 |
1 |
| 4/12/2006 |
2 |
| 4/13/2006 |
3 |
| 4/14/2006 |
4 |
| 4/15/2006 |
5 |
| 4/16/2006 |
6 |
| 4/17/2006 |
7 |
Now I can use the following query to get the reservations for that week, grouped by customer and vendor :
SELECT rt.customer_id, rt.vendor_id,
-- Day 1 of that week
SUM(DECODE(x.bill_date, to_date(:week), 1, 0)) Res1,
-- Day 2
SUM(DECODE(x.bill_date, to_date(:week)+1, 1, 0)) Res2,
-- Day 3
SUM(DECODE(x.bill_date, to_date(:week)+2, 1, 0)) Res3,
-- Day 4
SUM(DECODE(x.bill_date, to_date(:week)+3, 1, 0)) Res4,
-- Day 5
SUM(DECODE(x.bill_date, to_date(:week)+4, 1, 0)) Res5,
-- Day 6
SUM(DECODE(x.bill_date, to_date(:week)+5, 1, 0)) Res6,
-- Day 7
SUM(DECODE(x.bill_date, to_date(:week)+6, 1, 0)) Res7
FROM test_res rt,
(SELECT (to_date(:week)) + rownum -1 bill_date,
rownum day_key
FROM po_vendors
WHERE rownum <= 7) X
WHERE
-- break out the time window
(rt.departure_date > to_date(:week)
and rt.arrival_date <= to_date(:week)+6)
-- join to bill_date to get individual dates in the stay
and (x.bill_date between rt.arrival_date and rt.departure_date -1)
GROUP BY rt.vendor_id, rt.customer_id
Note that this uses the much maligned DECODE statement in Oracle, which is used to do if/then/else in a query. If I was using Oracle 8i or greater I could alternately use the case statement:
SELECT rt.customer_id, rt.vendor_id,
-- Day 1 of that week
SUM(case when x.bill_date = (to_date(:week)) then 1
else 0 end) Res1,
-- Day 2
SUM(case when x.bill_date = (to_date(:week)+1) then 1
else 0 end) Res2,
-- Day 3
SUM(case when x.bill_date = (to_date(:week)+2) then 1
else 0 end) Res3,
-- Day 4
SUM(case when x.bill_date = (to_date(:week)+3) then 1
else 0 end) Res4,
-- Day 5
SUM(case when x.bill_date = (to_date(:week)+4) then 1
else 0 end) Res5,
-- Day 6
SUM(case when x.bill_date = (to_date(:week)+5) then 1
else 0 end) Res6,
-- Day 7
SUM(case when x.bill_date = (to_date(:week)+6) then 1
else 0 end) Res7
FROM test_res rt,
(SELECT (to_date(:week)) + rownum -1 bill_date,
rownum day_key
FROM po_vendors
WHERE rownum <= 7) X
WHERE
-- break out the time window
(rt.departure_date > to_date(:week)
and rt.arrival_date <= to_date(:week)+6)
-- join to bill_date to get individual dates in the stay
and (x.bill_date between rt.arrival_date and rt.departure_date -1)
GROUP BY rt.vendor_id, rt.customer_id
Note in these queries that the departure date is any date that is greater than the first day of the week. The arrival date is any date less than or equal to the first day of the week +6. The biggest pitfall in these queries is the fact that the arrival date may be before the beginning date in the query. Also remember that the departure date is not included as a stay date. If my departure date is 13-Apr-2006, I am checking out on the 13th and not staying that night.
You can use the Case statement in SQL Server as well, and it is very similar
So what does the data look like? If we enter a date of 11-Apr-06, we get the following data:
| CUSTOMER_ID |
VENDOR_ID |
RES1 |
RES2 |
RES3 |
RES4 |
RES5 |
RES6 | >
RES7 |
| 234 |
9842 |
2 |
2 |
2 |
2 |
2 |
2 | >
2 |
| 638 |
9842 |
1 |
1 |
0 |
0 |
1 |
1 | >
1 |
If we enter a date of 22-Apr-06, we get the following data, picking up only 28-Apr-2006:
| CUSTOMER_ID |
VENDOR_ID |
RES1 |
RES2 |
RES3 |
RES4 |
RES5 |
RES6 | >
RES7 |
| 234 |
9842 |
0 |
0 |
0 |
0 |
0 |
0 | >
1 |
I hope that this is helpful.