Problem with Date logic

Hi OK, Here goes.
I have a booking table with a Checked In Date and a Checked Out Day. I have
a user input screen where a user Chooses a checked in date and checked out
date. I want to know if any rooms are available so I check against the
booking table. I've tried a between date but I have two dates going into th
e
Query and comparing it against the booking table which also has two dates.
Here is the stored procedure that works but only if I pass one date.
ALTER PROCEDURE dbo.Checkbookings
@.CheckIn datetime
AS
Insert Into booktbl select Book.ibook_site from tbl_booking as book
where @.Checkin between book.ibook_in_date and book.ibook_out_date
Select * from tbl_site where not exists(Select * from booktbl where
tbl_site.site_id = booktbl.ibook_site)
I'm trying not to loop in code through the time period the user has placed.
I would like to do the work on the SQL Server.
Any Help at all would be greatly appreciated.Vear
Please post DDL+ Sample data ?
Actually you can use
IF EXISTS (SELECT * FROM Booking WHERE dt>=@.date_in AND dt<
DATEADD(day,1,@.date_out)
Do something here
ELSE
Probably INSERT coomand
"Vear" <Vear@.discussions.microsoft.com> wrote in message
news:08FA718C-8B53-4E7C-977A-F2FFDCEBE01E@.microsoft.com...
> Hi OK, Here goes.
> I have a booking table with a Checked In Date and a Checked Out Day. I
> have
> a user input screen where a user Chooses a checked in date and checked out
> date. I want to know if any rooms are available so I check against the
> booking table. I've tried a between date but I have two dates going into
> the
> Query and comparing it against the booking table which also has two dates.
>
> Here is the stored procedure that works but only if I pass one date.
> ALTER PROCEDURE dbo.Checkbookings
> @.CheckIn datetime
> AS
> Insert Into booktbl select Book.ibook_site from tbl_booking as book
> where @.Checkin between book.ibook_in_date and book.ibook_out_date
> Select * from tbl_site where not exists(Select * from booktbl where
> tbl_site.site_id = booktbl.ibook_site)
> I'm trying not to loop in code through the time period the user has
> placed.
> I would like to do the work on the SQL Server.
> Any Help at all would be greatly appreciated.|||On Thu, 16 Mar 2006 18:47:28 -0800, Vear wrote:

>Hi OK, Here goes.
>I have a booking table with a Checked In Date and a Checked Out Day. I have
>a user input screen where a user Chooses a checked in date and checked out
>date. I want to know if any rooms are available so I check against the
>booking table. I've tried a between date but I have two dates going into t
he
>Query and comparing it against the booking table which also has two dates.
(snip)
Hi Vear,
You didn't post CREATE TABLE and INSERT statements to show how your
tables and data look like, so I'll have to make some assumptions. If you
have a fairly standard design for a reservations database, a query to
find rooms that are available in a give period would roughly look like
this:
SELECT r.RoomNo
FROM Rooms AS r
WHERE NOT EXISTS
(SELECT *
FROM Reservations AS res
WHERE res.RoomNo = r.RoomNo
AND res.EndDate > @.StartDate
AND res.StartDate < @.EndDate)
(Here, @.StartDate and @.EndDate are the period in which the room should
be free, and res.StartDate and res.EndDate are the start and end dates
of existing reservations).
Hugo Kornelis, SQL Server MVP|||Thanks for replying. I used the If Exists and it works great. I rotate
through the dates in the period I'm looking at and send it to a Temp table.
Thanks for your help
"Hugo Kornelis" wrote:

> On Thu, 16 Mar 2006 18:47:28 -0800, Vear wrote:
>
> (snip)
> Hi Vear,
> You didn't post CREATE TABLE and INSERT statements to show how your
> tables and data look like, so I'll have to make some assumptions. If you
> have a fairly standard design for a reservations database, a query to
> find rooms that are available in a give period would roughly look like
> this:
> SELECT r.RoomNo
> FROM Rooms AS r
> WHERE NOT EXISTS
> (SELECT *
> FROM Reservations AS res
> WHERE res.RoomNo = r.RoomNo
> AND res.EndDate > @.StartDate
> AND res.StartDate < @.EndDate)
> (Here, @.StartDate and @.EndDate are the period in which the room should
> be free, and res.StartDate and res.EndDate are the start and end dates
> of existing reservations).
> --
> Hugo Kornelis, SQL Server MVP
>