Solved

how to find bookings between dates in the following sql table

Posted on 2013-01-08
6
274 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OSQL to execute sql command 26 25
Nested Case statement 4 39
RAISERROR WITH NOWAIT 2 18
SQL - Curser to do an insert based on a select 2 13
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

821 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