查询帮助请 - 连续日期 [英] Query Help Please - Consecutive Dates
问题描述
您好，
有人可以帮我查询一下吗？
表格如下：
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
预期结果：
CUSTOMER STAYS
==============
CustomerID RoomID ArriveDateID DepartDateID
1 1 20050701 20050703
1 1 20050709 20050712
基本上，这是一个酒店预订系统。每晚的费用各不相同，而且客户的变化（缩短/延长住宿，更改房间等等）经常发生。因此，整个住宿预订
作为一系列的夜晚。
住宿时间从未知晓，因此需要通过<根据表格中的条目，到达和离开日期。
注意，客户经常住在同一个房间，但有间隙，
因此简单的MIN和MAX不起作用。输出需要显示
连续组合在一起的夜晚。
我已经研究了很多，但我似乎无法想象使它成为
工作。
任何帮助都会非常感激。
谢谢！
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!
推荐答案
（pl ****** @ yahoo.com）写道：
(pl******@yahoo.com) writes:
有人可以帮助我查询？
表格如下：
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
预期结果：
客户住宿
==============
CustomerID RoomID ArriveDateID DepartDateID
1 1 20050701 20050703
1 1 20050709 20050712
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
好的，所以我们通常希望您将表格定义和数据包括在内。
CREATE TABLE和INSERT语句，因此我们可以轻松地将
复制并粘贴到查询分析器中。但由于这不可能在没有测试的情况下编写这样的查询
，这次我不得不自己做。这是
a查询（我提到的CREATE和INSERT）：
CREATE TABLE scheduledrooms（custid int NOT NULL，
roomid int NOT NULL，
bkdate datetime NOT NULL，
PRIMARY KEY（custid，roomid，bkdate））
go
INSERT预订房间（custid，roomid，bkdate）
SELECT 1,1，''20050701''UNION ALL
SELECT 1,1，'''20050702' 'UNION ALL
选择1,1，''20050703''UNION ALL
选择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（选择custid，roomid，bkdate
来自预订的房间b1
什么时候不存在
（SELECT *
来自预订的房间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
来自预订的房间b1
什么不存在（
SELECT *
来自预订的房间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 scheduledrooms
首先，有两个派生表，只需查看前一天和下一天，即可为您提供所有到达日期和
出发日期。然后
这些都是不完整的加入，所以我们混合了可能的时期。我们
在MIN和GROUP BY的帮助下整理出我们想要的东西。
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server SP3联机丛书
http://www.microsoft.com/ sql / techinf ... 2000 / books.asp
请发帖DDL，这样人们就不用猜猜钥匙了，
约束，声明性参照完整性，数据类型等
您的架构是什么。样本数据也是一个好主意，同时还有明确的
规格。当你不让我们这么做时，很难调试代码
看到它。
你的设计根本就错了。试试这个。
CREATE TABLE预订
（customer_id INTEGER NOT NULL
参考客户（customer_id），
room_nbr INTEGER NOT NULL
CHECK（room_nbr BETWEEN 100和999），
arrival_date DATETIME NOT NULL，
PRIMARY KEY（customer_id， room_nbr，arrival_date）
departure_date DATETIME，
...）;
您的问题称为实体拆分。 enitty是一个酒店住宿
，因此应该用一个表中的一行建模。
另一个严重的问题是荒谬的数据元素名称。为什么
一切都是ID？没有标签号码？没有测量？最愚蠢的是
" BookDateID" - 考虑一下。标识符是唯一的名义
比例;日期是比例尺度上的值。因此，数据元素必须是一个或另一个。
你需要得到一本关于数据建模的书。
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.
感谢您的回复。
Erland登陆桌子的DDL：
CREATE TABLE预订室（ custid int NOT NULL，
roomid int NOT NULL，
bkdate datetime NOT NULL，
PRIMARY KEY（custid，roomid，bkdate））
CustomerID是引用客户帐户的外键。
RoomID是引用房间的外键。
还有一个检查房间和日期的约束，以确保无法进行双重预订。
使用BookDateID代替DateTime以避免弄乱
时间等。入住时间是下午2:00。退房时间是中午12:00。因此，
20050719的预订房间1意味着客户在2月8日在b / b
登记入住，并在7/20中午退房。
记录ArriveDate和DepartDate在上一个
版本中被放弃了，因为客户调整在这个系统中经常发生。
再次，每个日期'费用根据该日期的入住人数而有所不同，所以
将住宿结合起来是不可行的。
人们也非常普遍在一个房间里一段时间，
然后再转到另一个房间（即接近迟到的
朋友）。因此，预订从抵达日期开始到离开
日期会导致会计噩梦。如果有人支付了5晚的费用，但
在剩下的3个之前只剩下2个，你需要一种方法来保持
一切都完好无损。
如果您取消集中预订，您将失去作为单独实体跟踪每个
之夜的能力。没有办法记录房间
在前2晚被占用，你必须同时处理所有5晚
。该产品为一个客户在一个房间内住一晚;不是
a日期和房间。
此外，如果客户决定再住一天，那么住宿是
组合在一起，然后添加的最后一天是孤儿。客户
将需要退房，然后在新的日期重新登记，因为
新的夜晚无法添加到之前的交易中。为BookedRooms添加一天并使用另一个
机制来跟踪停留时间，那就更容易和明智了，那就是取消旧的
5天预订，并重新创建一个新的6天预订，例如添加
天。
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.
这篇关于查询帮助请 - 连续日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
