Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query that Groups On Column but Excludes Certain Rows

Posted on 2008-06-20
9
Medium Priority
?
190 Views
Last Modified: 2010-03-19
I have a RoomAvailability table for a Hotel booking engine.  Here's some sample data.  

1     6/24/2008 12:00:00 AM    0
1     6/25/2008 12:00:00 AM    1
1     6/26/2008 12:00:00 AM    0
1     6/27/2008 12:00:00 AM    0

Column 1 is the RoomNumber, Column 2 is a date, column 3 is a ReservationID.

Therefore, if someone needed to check in on 6/24 and check out  6/27 this particular room should not show up as a potential match because it is booked on 6/25.  So basically, I need a query that returns room numbers where that room has no reservationID on any day from the check-in to check-out dates.

I'm having a heck of a time thinking of a simple solution for this.  I can think of less elegant ways to bring back the correct data but I have a feeling that there is some T-Sql function or some recursive joining or something that will get me where i want to get.

I've attached my code so far.  Of course it doesn't work.   Anyone got any good ideas?
Select  RoomNumber
from    RoomAvailability
where   datediff(day,'06/24/2008',Date) >= 0 and 
        datediff(day,Date,'06/27/2008') >= 0
and ReservationID = 0
group by RoomNumber

Open in new window

0
Comment
Question by:davidcahan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 13

Expert Comment

by:rickchild
ID: 21834401
Something like this, where the room must be available for at least the correct number of days in the range?

SELECT RoomNumber FROM RoomAvailability
WHERE
Room IN
(
    SELECT RoomNumber
    FROM RoomAvailability 
    WHERE ReservationID = 0 AND Date <= '2008-06-27' AND Date >= '2008-06-24' 
    GROUP BY RoomNumber
    HAVING count(RoomNumber) >=  ( DATEDIFF("dd",'2008-06-24','2008-06-27') +1 )
)

Open in new window

0
 
LVL 13

Expert Comment

by:rickchild
ID: 21834422
Yeah should do it, just run that on some test data.  Just replace the dates with the correct variables.
0
 
LVL 13

Accepted Solution

by:
rickchild earned 2000 total points
ID: 21834748
Well, you don't really need the outside query ;-)
SELECT RoomNumber
    FROM RoomAvailability 
    WHERE ReservationID = 0 AND Date <= '2008-06-27' AND Date >= '2008-06-24' 
    GROUP BY RoomNumber
    HAVING count(RoomNumber) >=  ( DATEDIFF("dd",'2008-06-24','2008-06-27') +1 )

Open in new window

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:davidcahan
ID: 21835161
actually...those don't work.  but i figured out that all i had to do was "reverse" my thinking.  if i find all the rooms that have a reservation between those dates then i can exclude them either using a "not in" statement or left joining back on RoomNumber:




Select RoomNumber
from RoomAvailability
where datediff(day,'06/24/2008',Date) >= 0 and 
		           datediff(day,Date,'06/27/2008') >= 0
and ReservationID = 1
group by RoomNumber

Open in new window

0
 
LVL 13

Expert Comment

by:rickchild
ID: 21837027
Strange, I tested with the following which worked fine, maybe just seomthing to do with how I am setting the date in yyyy-mm-dd.  You are on SQL2005?
create table ##RoomAvailability (RoomNumber INT, Date datetime, ReservationID INT)
 
INSERT INTO ##RoomAvailability VALUES (1, '2008-06-24', 0)
INSERT INTO ##RoomAvailability VALUES (1, '2008-06-25', 1)
INSERT INTO ##RoomAvailability VALUES (1, '2008-06-26', 0)
INSERT INTO ##RoomAvailability VALUES (1, '2008-06-27', 0)
 
INSERT INTO ##RoomAvailability VALUES (2, '2008-06-24', 0)
INSERT INTO ##RoomAvailability VALUES (2, '2008-06-25', 0)
INSERT INTO ##RoomAvailability VALUES (2, '2008-06-26', 0)
INSERT INTO ##RoomAvailability VALUES (2, '2008-06-27', 0)
 
INSERT INTO ##RoomAvailability VALUES (3, '2008-06-24', 0)
INSERT INTO ##RoomAvailability VALUES (3, '2008-06-25', 1)
INSERT INTO ##RoomAvailability VALUES (3, '2008-06-26', 0)
INSERT INTO ##RoomAvailability VALUES (3, '2008-06-27', 0)
INSERT INTO ##RoomAvailability VALUES (3, '2008-06-28', 0)
 
INSERT INTO ##RoomAvailability VALUES (4, '2008-06-24', 0)
INSERT INTO ##RoomAvailability VALUES (4, '2008-06-25', 0)
INSERT INTO ##RoomAvailability VALUES (4, '2008-06-26', 0)
INSERT INTO ##RoomAvailability VALUES (4, '2008-06-27', 0)
INSERT INTO ##RoomAvailability VALUES (4, '2008-06-28', 0)
 
--- RETURNS ROOMS 2 & 4
SELECT RoomNumber
    FROM ##RoomAvailability 
    WHERE ReservationID = 0 AND Date <= '2008-06-27' AND Date >= '2008-06-24' 
    GROUP BY RoomNumber
    HAVING count(RoomNumber) >=  ( DATEDIFF("dd",'2008-06-24','2008-06-27') +1 )
 
 
--- RETURNS ROOM 4 ONLY
SELECT RoomNumber
    FROM ##RoomAvailability 
    WHERE ReservationID = 0 AND Date <= '2008-06-28' AND Date >= '2008-06-24' 
    GROUP BY RoomNumber
    HAVING count(RoomNumber) >=  ( DATEDIFF("dd",'2008-06-24','2008-06-28') +1 )

Open in new window

0
 
LVL 13

Expert Comment

by:rickchild
ID: 21837051
Good idea with the NOT IN anyway, should also do it.

Upset that my solution didn't work for you as I did test it as above!
0
 

Author Comment

by:davidcahan
ID: 21837449
let me try it again.  
0
 

Author Comment

by:davidcahan
ID: 21842251
that is really weird, but it doesn't work.  i have no clue why.  i'm gonna give you points cause you did come up with a solution.

David
0
 
LVL 13

Expert Comment

by:rickchild
ID: 21842281
OK thanks, must be some environment setting as it's a simple bit of testing!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question