Using Case statement in and or statement

[renee35]

I am trying to create a or statement and what I have is a case statement to handle the 2nd half of it. This is what I need to accomplish:

anyone who has at least 90 days of employment between their earliest start date of all of their jobs, and their latest end date of any of their jobs (or the end date of the report [1/1/2009 – 4/1/2009 – ETC], if the quarter start is less than their end date, use the quarter start date), but not more than 179 days worked (So that they only show up on the report once).

This is what I have so far:

select employeeid, min(startdate) as startdate, max(enddate) as enddate
from SS_HTRNJ.dbo.woworkorder
where ((enddate is null or datediff(dd, enddate, startdate) <= 90) )
or
(Case when @quarterstart < enddate then datediff(dd, @quarterstart, startdate) <= 90)))
group by employeeid


I am getting an error:

Line 21: Incorrect syntax near '<'. Which points back to the Case statement, please help!!!


Thanks a bunch!!

-T

 

[IanWaterman]

YOU have to define a result for the case eg

(Case when @quarterstart < enddate then datediff(dd, @quarterstart, startdate) else 100 end) <= 90

Ian

 

[renee35]

Thanks a bunch!

Thanks a bunch!!

-T