• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

Query that Groups On Column but Excludes Certain Rows

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
davidcahan
Asked:
davidcahan
  • 6
  • 3
1 Solution
 
rickchildCommented:
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
 
rickchildCommented:
Yeah should do it, just run that on some test data.  Just replace the dates with the correct variables.
0
 
rickchildCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
davidcahanAuthor Commented:
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
 
rickchildCommented:
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
 
rickchildCommented:
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
 
davidcahanAuthor Commented:
let me try it again.  
0
 
davidcahanAuthor Commented:
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
 
rickchildCommented:
OK thanks, must be some environment setting as it's a simple bit of testing!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now