Solved

how to find bookings between dates in the following sql table

Posted on 2013-01-08
6
265 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

0
Comment
Question by:mmalik15
6 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 38754286
Select * from booking_table
where ArrivalDate >= '16/01/2013 00:00'
and Departuredate <= '22/01/2013 00:00'
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38754366
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]
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38754784
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)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Expert Comment

by:Simone B
ID: 38754991
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

0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38755074
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.
0
 

Author Closing Comment

by:mmalik15
ID: 38809319
Perfect buddy
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 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

18 Experts available now in Live!

Get 1:1 Help Now