Query Help Please - Consecutive Dates

[plaztik8]

Hello,

Can someone please help me with a query?

The table looks like this:

BookedRooms
===========

CustomerID RoomID BookDateID
1 1 20050701
1 1 20050702
1 1 20050703

1 1 20050709
1 1 20050710
1 1 20050711
1 1 20050712
Desired result:

CUSTOMER STAYS
==============

CustomerID RoomID ArriveDateID DepartDateID
1 1 20050701 20050703
1 1 20050709 20050712
Basically, this is for a hotel reservation system. Charges vary
nightly, and customer changes (shortening/extending stay, changing
rooms, etc) happen quite often. Therefore, the entire stay is booked
as a series of nights.

The length of the stay is never known, so it needs to be derived via
the Arrive and Depart Dates, based on the entries in the table.

Notice, customers often stay in the same room, but with gaps between,
so a simple MIN and MAX doesn't work. The output needs to show
consecutive nights grouped together, only.

I've researched this quite a bit, but I just can't seem to make it
work.

Any help would greatly be appreciated.

Thanks!

[Erland Sommarskog]

(pl******@yahoo.com) writes:

Can someone please help me with a query?

The table looks like this:

BookedRooms
===========

CustomerID RoomID BookDateID
1 1 20050701
1 1 20050702
1 1 20050703

1 1 20050709
1 1 20050710
1 1 20050711
1 1 20050712
Desired result:

CUSTOMER STAYS
==============

CustomerID RoomID ArriveDateID DepartDateID
1 1 20050701 20050703
1 1 20050709 20050712

OK, so normally we like you to include table definitions and data as
CREATE TABLE and INSERT statements, so we easily can copy and paste
into Query Analyzer. But since this its not possible to write a query
like this without testing, I had to do it myself this time. Here is
a query (with the CREATE and INSERT that I mentioned):

CREATE TABLE bookedrooms(custid int NOT NULL,
roomid int NOT NULL,
bkdate datetime NOT NULL,
PRIMARY KEY (custid, roomid, bkdate))
go
INSERT bookedrooms(custid, roomid, bkdate)
SELECT 1, 1, '20050701' UNION ALL
SELECT 1, 1, '20050702' UNION ALL
SELECT 1, 1, '20050703' UNION ALL
SELECT 1, 1, '20050709' UNION ALL
SELECT 1, 1, '20050710' UNION ALL
SELECT 1, 1, '20050711' UNION ALL
SELECT 1, 1, '20050712' UNION ALL
SELECT 1, 1, '20050810' UNION ALL
SELECT 1, 1, '20050811'
go
SELECT a.custid, a.roomid, arrivaldate = a.bkdate,
enddate = MIN(b.bkdate)
FROM (SELECT custid, roomid, bkdate
FROM bookedrooms b1
WHERE NOT EXISTS
(SELECT *
FROM bookedrooms b2
WHERE b1.custid = b2.custid
AND b1.roomid = b2.roomid
AND dateadd(DAY, -1, b1.bkdate) = b2.bkdate)) AS a
JOIN (SELECT custid, roomid, bkdate
FROM bookedrooms b1
WHERE NOT EXISTS (
SELECT *
FROM bookedrooms b2
WHERE b1.custid = b2.custid
AND b1.roomid = b2.roomid
AND dateadd(DAY, 1, b1.bkdate) = b2.bkdate)) AS b
ON a.custid = b.custid
AND a.roomid = b.roomid
AND b.bkdate > a.bkdate
GROUP BY a.custid, a.roomid, a.bkdate
go
DROP TABLE bookedrooms

First there are two derived tables that gives you all arrival date and
departure dates, simply by looking at the previous and next days. Then
these are join incompletly, so we get a mix of possible periods. We
sort out those we want with help of the MIN and GROUP BY.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Your design is fundamentally wrong. Try this.

CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
room_nbr INTEGER NOT NULL
CHECK (room_nbr BETWEEN 100 AND 999),
arrival_date DATETIME NOT NULL,
PRIMARY KEY (customer_id, room_nbr, arrival_date)
departure_date DATETIME,
...);

Your problem is called entity splitting. The enitty is a hotel stay
and therefore should be modeled with one and only one row in a table.

Another serious problem is absurd data element names. Why is
everything an "ID"?? No tag numbers? no measurements? The silliest was
"BookDateID" -- just think about it. An identifier is a unique nominal
scale; a date is a value on a ratio scale. Ergo, a data element has to
be either one or the other.

You need to get a book on data modeling.

[Plaztik]

Thanks for the responses.

Erland landed the DDL for the table:
CREATE TABLE bookedrooms(custid int NOT NULL,
roomid int NOT NULL,
bkdate datetime NOT NULL,
PRIMARY KEY (custid, roomid, bkdate))

CustomerID is a foreign key referencing the Customer Account.
RoomID is a foreign key referencing the Room.
There is also a check constraint on room and date to ensure
double-bookings are not possible.

BookDateID is used in lieu of DateTime to avoid the need to mess with
the times, etc. Check-in is 2:00pm. Check-out is 12:00pm. Thus,
booking room 1 for 20050719 implies the customer checked in at 2 on
7/19 and is checking out at noon on 7/20.

Recording the ArriveDate and DepartDate was abandoned in the last
version, since customer adjustments happen so often in this system.
Again, each date's cost varies based on the occupancy for that date, so
lumping the stay together is not viable.

It is also extremely common for people to be in one room for a while,
and then switch to another later (ie. to be close to late-arriving
friends). Because of this, booking stays from arrival date to depart
date causes an accounting nightmare. If someone pays for 5 nights, but
stays only 2 before moving for the remaining 3, you need a way to keep
everything intact.

If you cancel a lumped reservation, you lose the ability to track each
night as a separate entity. There is no way to record that the room
was occupied the first 2 nights, and you have to deal with all 5 nights
at a time. The product is one night in one room for one customer; not
a mush of dates and rooms.

Also, if the customer decides to stay another day, and the stay is
grouped together, then the last day added is orphaned. The customer
would be required to check out and then back in on the new date, since
the new night cannot be added to a previous transaction. It's much
easier and sensible to add a day to the BookedRooms and use another
mechanism for tracking the length of stay, then it is to cancel the old
5 day reservation, and recreate a new 6 day reservation just to add a
day, for example.

[--CELKO--]

>> Thus, booking room 1 for 2005-07-19 implies the customer checked in at 02:00 on 2005-07-19 and is checking out at 12:00 on 2005-07-20. <<

That is called a DEFAULT value in SQL. They can be used on insertion
and in DRI actions.

Recording the ArriveDate and DepartDate was abandoned in the last version, since customer adjustments happen so often in this system. <<
That is called an UPDATE in SQL. So you started off with the ISO model
of time and threw it out. Interesting. Read the stuff by Rick
Snodgrass at the Universrtiy of Arizona. His entire career is in
temporal databases. After 20+ years he uses durations. So I am
inclined to thik that you are probably screwing up the model. This
falls into the same category as "I do not need to normalize my data"
postings.
It is also extremely common for people to be in one room for a while, and then switch to another later (ie. to be close to late-arriving friends). <<
So the (arrival, departure) model captures this fact exactly.
each date's cost varies based on the occupancy for that date, so lumping the stay together is not viable. <<
We can do that in a query which will sit in a VIEW. This is a standard
thing for costing inventory, etc. I would need to know the
computational rules.
if the customer decides to stay another day, and the stay is grouped together, then the last day added is orphaned. The customer would be required to check out and then back in on the new date, since the new night cannot be added to a previous transaction. <<
Unh? The last row would have (arrival_date, depart_date) =
('2005-07-12', NULL). We would use COALESCE (depart_date,
CURRENT_TIMESTAMP) in the VIEWS and code.
It's much easier and sensible to add a day to the BookedRooms and use another mechanism for tracking the length of stay, then it is to cancel the old 5 day reservation, and recreate a new 6 day reservation just to add a day, for example. <<

Why would you not just update the existing 5 day stay to a 6 day stay?
IOhave never had to sign-in twice when I extended a hotel stay.

[Plaztik]

Thanks anyway, Celko, but you just don't get it and I'm not going to
post every bit of code to open your eyes.

I asked a simple question, based on an existing system, and you used
the chip on your shoulder to beat everything to hell and avoid the
question at hand.

You totally assumed that I am responsible for the design of this
system, and I'm not. You assumed that the database is not normalized,
and it is. And you totally refuse to understand that some business
processes require systems to work differently than 'you' may have had
in mind.

Thanks again for responding, but your input was utterly useless and
misplaced criticism; much more so than it was an answer to a viable
question.

Lose the attitude, please. It's not becoming, nor is it helpful.

[Erland Sommarskog]

Plaztik (pl******@yahoo.com) writes:

Thanks anyway, Celko, but you just don't get it and I'm not going to
post every bit of code to open your eyes.

You committed a fatal mistake. You tried to explain a real-world system
with all its imperfections to Joe Celko.

Anyone who has designed a system that evolves over time, knows that
data modelling includes a great deals of trade-offs and compromising.

But some people has only written books about it. Books tends to, and
maybe not without reason, aim at a more perfect world. But it's
deceivable to confuse fact with fiction.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[AK]

because the OP has indicated that "customer changes
(shortening/extending stay, changing rooms" do happen, I would join on

ON a.custid = b.custid
AND b.bkdate > a.bkdate

rather than on

ON a.custid = b.custid
AND a.roomid = b.roomid
AND b.bkdate > a.bkdate

so that the customer's upgrade to another room would not break their
stay

[--CELKO--]

>> You totally assumed that I am responsible for the design of this system, and I'm not. <<

True. And I also assumed that you would want to fix it, even if you
did not create it. After 20 years with this language, I am convinced
that most query problems come from bad DDL. Did you notice that
Erland's 22-line, deeply nested, self-join query converted your
existing table into what I was telling you to do in the first place?
If a programmer that good hs to write that kind of code for a simple
query, isn't that a hint that something is wrong in the DDL?

You assumed that the database is not normalized, and it is. <<
No, it is not. If you don't like me telling you this, then Chris Date
will tell you the same thing. Or better yet, Fabian Pascal if you
think I am abrasive :)

Specifically, a row in a table is a single fact (see Cood, Date, et
al). In a temporal model, a fact has duration modeled by a half-open
interval (ISO Model, Snodgrass and almost every other TDB guru for the
last 30 years). You have split a fact (hotel stay) across multiple
rows, so it has to be re-asembled. If you had the table in 1NF, and
enforced temporal sequence with constriants you owuld not need to do
this on the fly.
And you totally refuse to understand that some business processes require systems to work differently than 'you' may have had in mind. <<

It is hard to work from specs that were not posted. (Hell, it is hard
to work from specs that are posted -- you cannot put a full RFP in a
newsgroup.) So people wind up making assumptions about the missing DDL
-- your narrative "The table looks like this: ". is not a spec of any
kind. And then we assume things from our own experiences if the
problem is a common event, like checking into a hotel.

[AK]

Mr Celko,

your design

CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
room_nbr INTEGER NOT NULL
CHECK (room_nbr BETWEEN 100 AND 999),
arrival_date DATETIME NOT NULL,
PRIMARY KEY (customer_id, room_nbr, arrival_date)
departure_date DATETIME,
...);

is just plain wrong, because it does not meet the requirement that a
guest may change a room during his stay.

[Mark D Powell]

Ak, I believe the problem in your fix to not breaking the stay is that
the room rates could be different. The customer could change into or
out of a room with a hot tub or into a suite for a room with different
features and hence a different charge. Though your would be good if
billing is not a consideration for the question being posed such as
when did Mr X stay at the hotel last?

Fortunately Erland seems to have understood the problem as stated and
provided a solution.

-- Mark D Powell --

[AK]

Mark, to my best knowledge, room rates may be different even if you
stay in one and the same room. That's what the OP wrote: "Charges vary
nightly".
The way I understand the problem they want to present the departing
guest with one and only one bill, even if he changed a room. So, we
need first to determine ArriveDateID and DepartDateID, then to
calculate the totals

select sum(night_charge) from ... where customerID = ?
and BookDateID between ArriveDateID and DepartDateID

roomID is irrelevant to billing. A customer could be occupying/paying
for more that one room for any night and still get one and only one
bill.

Makes sense?

[Plaztik]

For clarity, here is some background information about the system that
is outside the scope of the original question...

1. Room rates vary nightly based on occupancy and season. If I am to
be in Room 1 from 7/1/2006 - 7/5/2006, we first look at the season,
(which, in this case, is peak season) which determines base room rate.
Next, we look at the total occupancy for all rooms for each day that is
to be booked. On the date this reservation was made, 7/1 may have
total occupancy of 65%, 7/2 may be 74%, 7/3 may be 88%, 7/4 may be 93%,
and 7/5 may be 85%. The base room rate is then modified based on the
total occupancy of each day to get the reservation room rate.

2. Reservations for this system are PAID IN ADVANCE, when the
reservation is made. This is a big point that I left out, and which
may have avoided some of the confusion in this thread (but, as
mentioned before, is outside the scope of the original question).
Therefore, if I make a reservation for 7/1/2006 - 7/5/2006 and pay for
it now as required, accounting closes this sale. Updating the row is
not an option, because it corrupts the historical data that the
accounting system references. I'm sure people will agree that once
booked in accounting, sales are never edited.

3. The product being sold is one night's stay in one room. The
arrival date and depart date are rarely fixed. If the reserved rooms
are dealt with on a daily basis and a change needs to occur, then we
can just cancel that reservation day (return the product), issue
credits based on the return, and proceed without tampering with our
closed/posted historical data. The original sale stays intact, which
will not happen with durations. If they were lumped together, you
would have to cancel/return the entire stay (duration product) and then
create a brand new duration. to keep accounting straight.

4. Hospitality tax laws require that we file a report detailing the
customer name and tax paid, by day, for each day occupied. If I
reserve 7/1 - 7/5, I pay for tax on each night. If I don't show until
7/2, the tax I paid for 7/1 is forfeited, but law does not require that
it be reported as tax paid, since the room was not occupied. Again,
grouping the stay duration together is not feasible for the business
requirements.

5. We are placing reservations at a very popular vacation site.
Room/Reservation changes occur far more frequently than in usual
hotels. Lots of families/friends make reservations individually (and
pay for them, as required), but when the arrive they decide to change
rooms to be closer to each other. Due to occupancy, they may have to
be 2 rooms apart for the first 5 days, at which point they will move to
adjacent rooms. Over the course of their duration, the room number
certainly changes, and the room type (base fee structure) may change
(ie suite, double).

Consider the following example: (not table data)

Customer Room Date Rate
-------------- ---------- ---------
-------
1 1 7/1/2006 $200
1 2 7/2/2006 $225
1 2 7/3/2006 $240
1 3 7/4/2006 $320
1 3 7/5/2006 $300
We want to know the customer's ArriveDate and DepartDate (volatile,
derived data), just because it is easier for humans to work with. This
view that people may like to see cannot dictate how the system deals
with transactional, historical, and accounting data under the covers,
though. The example above is extremely common and even much simpler
than others that occur daily. Things are the way they are for a
reason.

Hopefully that cleared some things up.

Thanks again to everyone for the responses and help.

[Erland Sommarskog]

Plaztik (pl******@yahoo.com) writes:

We want to know the customer's ArriveDate and DepartDate (volatile,
derived data), just because it is easier for humans to work with. This
view that people may like to see cannot dictate how the system deals
with transactional, historical, and accounting data under the covers,
though. The example above is extremely common and even much simpler
than others that occur daily. Things are the way they are for a
reason.

Hopefully that cleared some things up.

Thanks for taking your time to explain your business a little more,
although it's none of our business (pardon the pun). It's a great example
to show that there are a lot more in a system that from what you can see
in a query. For instance, the need to be able to cancel one day within
a reservation is one that may not be obvious to someone who only knows SQL
well, but have no experience of accounting or don't realise the relation
to accounting.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

True. And I also assumed that you would want to fix it, even if you
did not create it. After 20 years with this language, I am convinced
that most query problems come from bad DDL. Did you notice that
Erland's 22-line, deeply nested, self-join query converted your
existing table into what I was telling you to do in the first place?
If a programmer that good hs to write that kind of code for a simple
query, isn't that a hint that something is wrong in the DDL?

Drop dead, Celko. You may know SQL, but you have clearly demonstrated
that you don't understand the hotel business - nor any willing to understand
it.

The DDL was bad for this particular query. But this system consists of a
whole lot more than this query. From what Plaztik has related, it seems
to me clear that he has made the correct trade-off of where to have the
complexity in his system.

A good professional attitude is respect people for what they are doing.
You may have a suggestion for a design that you think is better, but
be humble and realise that you don't know the entire business domain.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Plaztik]

> Thanks for taking your time to explain your business a little more,

although it's none of our business (pardon the pun). It's a great example
to show that there are a lot more in a system that from what you can see
in a query. For instance, the need to be able to cancel one day within
a reservation is one that may not be obvious to someone who only knows SQL
well, but have no experience of accounting or don't realise the relation
to accounting.

It's my pleasure. I really appreciate it Erland.

Your solution worked beautifully.