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'