CODEWITHSUNDEEP
sql
Nitin
Nitin

Reputation: 65

Query is not giving correct result according to my condition

I have tables which name is given below

1)tblCarName
2)tblVehicle
3)tblModel
4)tblVehicleAvailability

Above three tables i have fetch result which vehicle is not booked and specific model and then one another condition vehicle available condition on particular date vehicle is available or not.
But i am getting result which car is not available. (Means those car which is in tblVehicleAvailability table) but I want those car which was not in tblVehicleAvailability table)

My Query

select 
    A.id,
    C.vsCarName+' ('+ A.EngineNum+' )' as vsCarName   
from tblCarName C 
join tblVehicle A on C.id = A.CarId
inner join tblModel B on A.ModelId = B.id 

--join tblVehicleAvailability VA on  VA.CarId = A.id

WHERE A.id not in 
(
    SELECT 
        VehicleId 
    FROM 
        tblBooking
    WHERE 
        A.ActiveInactive = 1
    AND 
        Convert(datetime,'2013-09-19 00:00:00.000') --start date
            BETWEEN FromDateWithStartTime AND ToDateWithEndTime
    OR 
        Convert(datetime,'2013-09-20 00:00:00.000')  --endDate
            BETWEEN FromDateWithStartTime AND ToDateWithEndTime
    OR 
        FromDateWithStartTime 
            BETWEEN Convert(datetime,'2013-09-19 00:00:00.000') --startdate  
          AND Convert(datetime,'2013-09-20 00:00:00.000')  --enddate  
    OR 
        ToDateWithEndTime 
            BETWEEN Convert(datetime,'2013-09-19 00:00:00.000')--start date  
            AND Convert(datetime,'2013-09-20 00:00:00.000')--enddate
) 
AND  
    A.ModelId='3' 
AND   
    Convert(datetime,'2013-09-19 00:00:00.000')  --start date  
    BETWEEN VA.leaveDate AND VA.leaveToDate  
OR 
    Convert(datetime,'2013-09-20 00:00:00.000') --endDate  
    BETWEEN VA.leaveDate AND VA.leaveToDate  
OR 
    VA.leaveDate 
    BETWEEN Convert(datetime,'2013-09-19 00:00:00.000') --startdate  
    AND Convert(datetime,'2013-09-20 00:00:00.000')  --enddate  
OR 
    VA.leaveToDate
    BETWEEN Convert(datetime,'2013-09-19 00:00:00.000') --start date
    AND Convert(datetime,'2013-09-20 00:00:00.000')

Upvotes: 1

Views: 109

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Try this instead:

select A.id ,C.vsCarName+' ('+ A.EngineNum+' )' as vsCarName   
from tblCarName C 
inner join tblVehicle A on C.id=A.CarId
inner join tblModel B on A.ModelId=B.id 
where not exists (
    select null from tblBooking
    where A.id = VehicleId and
    A.ActiveInactive = 1 and 
    '20130919' <= ToDateWithEndTime 
    and '20130920' >= FromDateWithStartTime
    ) 
and  A.ModelId='3' AND
'20130919' <= VA.leaveToDate  
and '20130920' >= VA.leaveDate

Upvotes: 1

Henrik Staun Poulsen
Henrik Staun Poulsen

Reputation: 13874

Normally when I see the "OR" statement, I start adding (), and that way I get what I want.

edit: But you may also want to look at using a LEFT JOIN tblVehicleAvailability

and an additional where clause:

WHERE CarID is NULL

Best regards, Henrik

Upvotes: 0

Related Questions