Availabilty of Dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Availabilty of Dates

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-18 : 05:50:11

Hi Friends ,

I had two tables one Hotel Details and Other Hotel Booking Details

Tabel1-Hotel Details

HaId Name Price
1 MM 100
2 KK 250
3 SS 300
4 ND 150

Tabel2-Hotel Booking Details

BId HaId FromDate ToDate
1 2 19/5/2009 21/5/2009
2 3 20/5/2009 25/5/2009
3 1 18/5/2009 19/5/2009

When i search the dates From 21/5/2009 to 23/5/2009 ,here in table2 we can see that HaId - 2 and 3 are booked for the above dates from 21/5/2009 and 23/5/2009.But Haid 1 is booked fro some other date and Haid 4 in Table 1 are not even booked .So i need the details of HAid 1 and haid 4

Please help me regarding this.

Thanks in Advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-18 : 06:34:19

[code]
DECLARE @Hotel TABLE
(
HaId int,
Name varchar(10),
Price int
)
INSERT INTO @Hotel
SELECT 1, 'MM', 100 UNION ALL
SELECT 2, 'KK', 250 UNION ALL
SELECT 3, 'SS', 300 UNION ALL
SELECT 4, 'ND', 150

DECLARE @Booking TABLE
(
BId int,
HaId int,
FromDate datetime,
ToDate datetime
)
INSERT INTO @Booking
SELECT 1, 2, '2009-05-19', '2009-05-21' UNION ALL
SELECT 2, 3, '2009-05-20', '2009-05-25' UNION ALL
SELECT 3, 1, '2009-05-18', '2009-05-19'

DECLARE
@bookfrom datetime,
@bookto datetime

SELECT @bookfrom = '2009-05-21',
@bookto = '2009-05-23'

SELECT *
FROM @Hotel h
WHERE NOT EXISTS
(
SELECT *
FROM @Booking b
WHERE b.HaId = h.HaId
AND (
(b.FromDate <= @bookfrom AND b.ToDate >= @bookfrom)
OR (b.FromDate <= @bookto AND b.ToDate >= @bookto)
)
)

/*
HaId Name Price
----------- ---------- -----------
1 MM 100
4 ND 150

(2 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Kotti
Posting Yak Master

129 Posts

Posted - 2009-05-18 : 07:27:09

Hi Friend

Thank you for your reply.

I got the answer with your query.

Thanks once again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources