join for get date - 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

SQL Server 2005 Forums

Transact-SQL (2005)

join for get date

Author

Topic

veronika.np
Starting Member

29 Posts

Posted - 2010-12-27 : 05:39:38

hi friends,
i have a reservation table .two of fields are checkin and checkout.
the user import fromdate and todate and click on search button.
i want to show users rooms that date isn`t betwwen checkin and checckout.
please help me.
thanks.

Sachin.Nand

2937 Posts

Posted - 2010-12-27 : 06:03:37

If they are date types then

your query....date Not between checkin and checkout

will do the job for you.

PBUH

veronika.np
Starting Member

29 Posts

Posted - 2010-12-27 : 06:17:08

thanks.my date is char type.i write it and work.
really thanks for your aid.


create  Procedure [dbo].[sp_room_testreserv]

@fromdate char(10),
@todate char(10)
As
Begin
	Select 
		r.id,
rv.id_customer,
rv.checkin,
rv.checkout,
rv.reservationnumber,
		r.roomnumber,
		r.hotelid,
		r.price,
		r.bedtype,
		r.smoking,
		r.roomlock,
		r.description,
		r.capacity,
		r.room_type

	From room r
join reservation rv
on rv.hotel_id=r.hotelid


where    @fromdate not between rv.checkin and rv.checkout
and @todate not between rv.checkin and rv.checkout

Sachin.Nand

2937 Posts

Posted - 2010-12-27 : 07:33:56

It is a bad design to save dates in columns with character datatypes.You should alwyas use datetime datatypes.

PBUH

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-27 : 07:53:37

I think you probably want

where @fromdate > rv.checkout
or @todate < rv.checkin

your query would show rooms where the from/to period included the booked room.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

veronika.np
Starting Member

29 Posts

Posted - 2010-12-27 : 12:39:44

@ dear nigelrivett :thanks.your solution give same result that i want and my query has same result .is myquery is wrong?
@@ dear Sachin.Nand:i want shamsi date therfore i force convert miladidate to shamsi date and i save it as char(10) type.

veronika.np
Starting Member

29 Posts

Posted - 2010-12-27 : 13:20:15

i have another problem with this query.
i have to table reservation and room.
i want show vacant to user base on date that user enter.
i write this query but it show room that register in reservation table.
perhaps a room is in room table but it isn`t in reservation table.
please help me.


create  Procedure [dbo].[sp_room_serachforreserve]
@hotelid int,
@fromdate char(10),
@todate char(10)
As
Begin
	Select 
 r.id,
rv.id_customer,
rv.checkin,
rv.checkout,
rv.reservationnumber,
		r.roomnumber,
		r.hotelid,
		r.price,
		r.bedtype,
		r.smoking,
		r.roomlock,
		r.description,
  roomtype=(case r.room_type
					when 1 then 'room'
					when 2 then 'swite'
					end ),
		r.capacity
		
					

	From room r 
  join reservation rv
on rv.roomnumber=r.roomnumber



where @fromdate > rv.checkout
or @todate < rv.checkin
--where    @fromdate not between rv.checkin and rv.checkout
--and @todate not between rv.checkin and rv.checkout
and r.capacity='2'
and r.hotelid=@hotelid
	


End

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-27 : 20:59:30

quote:
Originally posted by veronika.np

@ dear nigelrivett :thanks.your solution give same result that i want and my query has same result .is myquery is wrong?
@@ dear Sachin.Nand:i want shamsi date therfore i force convert miladidate to shamsi date and i save it as char(10) type.

I think so
if you had a booking 10 jan - 12 jan
then a request 5 jan - 15 jan
then your query would include the room but mine wouldn't.
Yours includes anything where both requested dates are outside the reserved period - i.e. not trying to check in or out while the room is reserved.
Mine where the room is free for the whole of the requested period.
If the bookings are always for the same duration theye will be the same but not if they are variable length.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-27 : 21:03:48

[code]
Select
r.id,
rv.id_customer,
rv.checkin,
rv.checkout,
rv.reservationnumber,
r.roomnumber,
r.hotelid,
r.price,
r.bedtype,
r.smoking,
r.roomlock,
r.description,
roomtype=(case r.room_type
when 1 then 'room'
when 2 then 'swite'
end ),
r.capacity

From room r
left join reservation rv
on rv.c=r.roomnumber
where
(@fromdate > rv.checkout
or @todate < rv.checkin
or rv.roomnumber is null
)
and r.capacity='2'
and r.hotelid=@hotelid

[/code]

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources