Need sql query for this scenario

Posted by Balajikcp8 under Sql Server on 3/29/2012 | Points: 10 | Views : 1062 | Status : [Member] | Replies : 5
i need sql query which return 2 for the below table..bcz the roomid 1 is locked for the date 01. it should return 3 if not locked on both the date 01 and 02..
bcz i'm going to book rooms for two days.

pls find attached document for table structure..

this my current query which returns 5 not 2

Declare @ToDate as DateTime ='04/02/2012'
Declare @FromDate as DateTime ='04/01/2012'
select COUNT(*) from eka_trn_tscheduletracker
where CONVERT(Varchar(10),scheduletracker_date ,102) between CONVERT(Varchar(10),@FromDate,102) and CONVERT(Varchar(10),@ToDate,102)
and scheduletracker_roomtype_gid=343 and
scheduletracker_status != 'L'




Responses

Posted by: Sksamantaray on: 3/29/2012 [Member] Silver | Points: 25

Up
0
Down
Hi,
There is no attachment

Thanks,
Sanjay

Balajikcp8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Balajikcp8 on: 3/29/2012 [Member] Starter | Points: 25

Up
0
Down
Pls find now
 Download source file

Balajikcp8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 3/29/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

According to your records, You don't have any matched data for the date given (2012-04-05 and 2012-04-06)!


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Balajikcp8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Balajikcp8 on: 3/29/2012 [Member] Starter | Points: 25

Up
0
Down
pls consider date as 01 to 02

Balajikcp8, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 3/29/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
I think, It would help U!

- Conversion format changed to 21 instead of 102 as given below

Declare @FromDate as DateTime ='04/01/2012' 

Declare @ToDate as DateTime ='04/02/2012'

;With CTE
As
(
Select scheduletracker_roomtype_gid, scheduletracker_roomid From eka_trn_tscheduletracker
Where CONVERT(Varchar(10),scheduletracker_date ,21) between CONVERT(Varchar(10),@FromDate,21) and CONVERT(Varchar(10),@ToDate,21)
and scheduletracker_roomtype_gid=343
and scheduletracker_status = 'L'
)

select Count(DISTINCT A.scheduletracker_roomid) from eka_trn_tscheduletracker A Left Join CTE C
On (A.scheduletracker_roomtype_gid = C.scheduletracker_roomtype_gid and
A.scheduletracker_roomid = C.scheduletracker_roomid)
where CONVERT(Varchar(10),A.scheduletracker_date ,21) between CONVERT(Varchar(10),@FromDate,21) and CONVERT(Varchar(10),@ToDate,21)
and A.scheduletracker_roomtype_gid=343
and A.scheduletracker_status != 'L'
AND C.scheduletracker_roomid IS NULL


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Balajikcp8, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response