|
|
Posted by Erland Sommarskog on 07/14/05 01:19
(plaztik8@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|