Need sql query for this scenario

Posted by Balajikcp8 under Sql Server on 3/29/2012 | Points: 10 | Views : 2012 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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'

Attachment

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	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

0	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

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

Latest Posts