Left Join Issue

[checkai]

Here is my query...I want all employees regardless if they have any PTO (paid time off) time logged...when I leave out the WHERE statement it works correctly...however I want to put between a certain date into this query...but if I put it in the case statement I think it will take VERY long (we have millions of WIP records)

any ideas...?
THANKS...

DLC


select sl.serviceline,
s.staffref,
s.surname+', '+s.forename as EmpName,
s.pto as 'PTO Accrued',
sum(case when j.name = 'NC - PTO' then isnull(w.units,0)else 0 end)as 'PTO Used'
from tblserviceline sl left join tblstaff s on sl.servicelineid=s.servicelineid
left join tblWIP w on s.staffid=w.staffid
left join tblJob J on w.jobid=j.jobid
--WHERE W.date>=@start and W.date<=@end
group by sl.serviceline,s.staffref,s.surname+', '+s.forename,s.pto

 

[Morpheus1981]

I cannot understant your question, can you I little bit more clear?

 

[swampBoogie]

Put the condition in the on clause

select  sl.serviceline,
    s.staffref,
    s.surname+', '+s.forename as EmpName,
    s.pto as 'PTO Accrued',
    sum(case when j.name = 'NC - PTO' then isnull(w.units,0)else 0 end)as 'PTO Used'
from     tblserviceline sl left join tblstaff s on sl.servicelineid=s.servicelineid
    left join tblWIP w on s.staffid=w.staffid 
    and W.date>=@start and W.date<=@end
    left join tblJob J on w.jobid=j.jobid
group by sl.serviceline,s.staffref,s.surname+', '+s.forename,s.pto

 

[checkai]

I figured it out...I needed to add the is null statement to get all records...that i wanted...

DLC


select sl.serviceline,
s.staffref,
s.surname+', '+s.forename as EmpName,
s.pto as 'PTO Accrued',
sum(case when j.name = 'NC - PTO' then isnull(w.units,0)else 0 end)as 'PTO Used'
from tblserviceline sl left join tblstaff s on sl.servicelineid=s.servicelineid
left join tblWIP w on s.staffid=w.staffid
left join tblJob J on w.jobid=j.jobid
WHERE (W.date>=@start and W.date<=@end) or w.date is null
group by sl.serviceline,s.staffref,s.surname+', '+s.forename,s.pto