Re: Query Help Please - Consecutive Dates

(plaztik8@xxxxxxxxx) 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@xxxxxxxxxxxxx

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

Relevant Pages

  • Re: Booking System - Vacant room report
    ... (SELECT DISTINCT RoomID ... If a room is booked for any one night in the range in the sub query, ... generate a report based on the top 30 records of any table but having ...
    (comp.databases.ms-access)
  • Re: Booking System - Vacant room report
    ... The temp table was filled by some query, ... select * from Customers ... RoomID and certain dates, then for a given date range, how do I create ...
    (comp.databases.ms-access)
  • Re: changing a recordset through a form
    ... in the query -- I'd check to make sure that the RoomID field that the users ... BuildingID ... > It is based on a query which joins the necessary fields from each table. ... > "Jeff Boyce" wrote: ...
    (microsoft.public.access.forms)
  • Re: Query using two columns
    ... >I now want to try an construct a query that will show a row for every ... CREATE TABLE Numbers (Number int not null primary key) ... DECLARE @MaxNumber int ... WHERE RoomID = 'Big Room' ...
    (microsoft.public.sqlserver.programming)