We help IT Professionals succeed at work.

how to find bookings between dates in the following sql table

309 Views
Last Modified: 2013-01-23
My sql server table is like below. Admin will select any start and end dates from calender and wants to see bookings between those dates. What will be the sql query to get only those bookings from the table below

BookingID	RoomCode	ArrivalDate	DepartureDate
100033	RM-3	16/01/2013 00:00	18/01/2013 00:00
100034	RM-1	16/01/2013 00:00	22/01/2013 00:00
100035	RM-1	27/01/2013 00:00	29/01/2013 00:00
100036	RM-14	20/01/2013 00:00	27/01/2013 00:00
100037	RM-5	27/01/2013 00:00	28/01/2013 00:00
100038	RM-6a	15/01/2013 00:00	20/01/2013 00:00

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Actually, if you want bookings where the Start Date is before or inside your "window", and want those where the end date is inside or after your window, you will need:

Select * FROM booking_table
WHERE ArrivalDate <= [WindowEndDate]
AND DepartureDate >= [WindowStartDate]
You have to be more specific ... I mean do you want the search for arrival date only or arrival and departure

Example will probably help (excel sheet format)
Simone BBI & Reporting Analyst

Commented:
If your Admin was choosing just one date, the select statement would be:

SELECT * FROM bookings
WHERE @date BETWEEN arrivaldate AND departuredate

However, because they want to find the bookings between a date range, you will have to loop through each date to get all results. Try using something like this (I had to set my date format, but you may not need to):

CREATE TABLE #TEMPTABLE 
(bookingID INT,
RoomCode VARCHAR(10),
ArrivalDate DATETIME,
DepartureDate DATETIME)

DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
DECLARE @date DATETIME

SET DATEFORMAT dmy

SET @startdate = '19/01/2013'
SET @enddate = '21/01/2013'

SET @date = @startdate

WHILE @date <= @enddate
BEGIN

INSERT INTO #temptable SELECT * FROM bookings
WHERE @date BETWEEN arrivaldate AND departuredate

SET @date = DATEADD(d,1,@date)
END

SELECT DISTINCT * FROM #temptable

Open in new window

Top Expert 2012

Commented:
Your problem is deceptively complex. There are SEVERAL scenarios to accomodate for.
1) arrival on or after Date1, departs on or before Date2
2) arrival prior to Date1, departure after Date2
3) arrival prior to Date1, departure before Date2
4) arrival after Date1, departure after Date2

With enough time, a person could determine every scenario and write a query with unions that would allow for each of them, but I would build a tally table based on the selected dates and join your data to that table.

Here is an example using your variable tables with your sample data:

declare @temp table (bookingid int, roomcode varchar(5), arrivaldate datetime, departuredate datetime)

insert into @temp values('100033',	'RM-3',	'01/16/2013',	'01/18/2013')
insert into @temp values('100034',	'RM-1',	'01/16/2013',	'01/22/2013')
insert into @temp values('100035',	'RM-1',	'01/27/2013',	'01/29/2013')
insert into @temp values('100036',	'RM-14',	'01/20/2013',	'01/27/2013')
insert into @temp values('100037',	'RM-5',	'01/27/2013',	'01/28/2013')
insert into @temp values('100038',	'RM-6a',	'01/15/2013',	'01/20/2013')

declare @startdate as datetime
declare @enddate as datetime

set @startdate = '01/17/2013'   
set @enddate = '01/21/2013'

declare @tally table (Dt datetime)

WHILE @startdate <= @enddate
Begin
insert into @tally values (@startdate)
set @startdate = dateadd(d,1,@startdate)
End

select 
distinct a.bookingid, a.roomcode, a.arrivaldate, a.departuredate
from @temp a, @tally b where b.dt between a.arrivaldate and a.departuredate 

Open in new window


It pulls back every booking where a person is in a room over the selected date range.

If you know the names of your tables I can post an example that you won't have to modify to run.

Author

Commented:
Perfect buddy

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.