Solved

Query that Groups On Column but Excludes Certain Rows

Posted on 2008-06-20
9
150 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
  • 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 500 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now