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

how to find bookings between dates in the following sql table

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
mmalik15
Asked:
mmalik15
1 Solution
 
Pratima PharandeCommented:
Select * from booking_table
where ArrivalDate >= '16/01/2013 00:00'
and Departuredate <= '22/01/2013 00:00'
0
 
Dale FyeCommented:
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
 
RehanYousafCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Simone BSenior E-Commerce AnalystCommented:
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
 
Jared_SCommented:
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
 
mmalik15Author Commented:
Perfect buddy
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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