0

Here is the query

`select 
p.occid,
p.occdate,
f.fillername,
badgeID= null,
p.mc
p.mctime,
problemcode = isnull(p.problemcode,400),
pc.problemdesc,
p.timerresolved,
p.duration,
e.employeename,
pl.durationinminutes,
pl.timein,
pl.timeout,
problemdescription = datediff(minute,occdate,timeresolved)

from problem as p
left join filler as f
on p.fillerid = f.fillerid
left join problemlog as pl
on p.occid = pl.occid
left join problemcode as pc
on p.problemcode = pc.problemcode
left join employee as e 
on pl.badgeid = e.id

where f.intials = @fillerselect and
occdate between dateadd(day, datediff(day, '19000101', @startdate), 
cast(@starttime as datetime2(7))) and 
dateadd(day, datediff(day, '19000101', cast @endtime as datetime2(7))) 
and p.problemcode in (@problemcode) 
and @Duration <= datediff(minute, occdate, timeresolved) 
and case when @reportparameter like 'false' 
         then e.employeename in  (@employee)`

My issue is in the case clause. I get an error stating "incorrect syntax near the keyword 'in'. Does anyone have any ideas how to overcome this?

I should have mentioned that @employee can have multiple selections. When @reportparameter is 'False', then @employee is 'None', stating we are not filtering by employee. When @reportparameter is 'true', we are filtering by employee and @employee may or may not have multiple selections.

Kenny
  • 11
  • 2
  • 1
    You can't use `CASE` like that. See http://stackoverflow.com/questions/8785209/case-statement-within-where-clause-in-sql-server-2008 – SQLChao Dec 09 '15 at 17:22

3 Answers3

0

I think, your 2 last lines (and case when then) should be something like this:

and ( @reportparameter like 'false' and e.employeename in (@employee) )
nabuchodonossor
  • 2,095
  • 20
  • 18
0

It looks like you want to conditionally check e.employeename against @employee when the @reportparameter = 'false'

I wouldn't recommend using a case statement for this action. Rather just use an OR condition. The SQL Server Optimizer will process the OR faster than a CASE statement within the WHERE clause (in general.)

WHERE f.intials = @fillerselect
  AND occdate BETWEEN dateadd(DAY, datediff(DAY, '19000101', @startdate), cast(@starttime AS datetime2(7))) AND dateadd(DAY, datediff(DAY, '19000101', CAST @endtime AS datetime2(7)))
  AND p.problemcode IN (@problemcode)
  AND @Duration <= datediff(MINUTE, occdate, timeresolved)
  AND ((@reportparameter = 'false'
        AND e.employeename IN (@employee))
       OR (@reportparameter <> 'false'
           AND e.employeename IN (@somethingelse)))

There look to be other things you may want to change in your WHERE clause as well. I'm not 100% sure you're using the IN condition appropriately. If @employee only contains one employee, you should be using = instead of IN. I'm assuming @ReportParameter is a string variable but using 'false' as a value is unusual. Ususally you would use a BIT and check against 1 or 0 for True vs. False.

Eric Isaacs
  • 199
  • 2
  • 5
  • Eric, employee can be a multiple selection. My goal with this part of the filter is to be able to filter by employee if desired, or to not filter by employee if desired. Reportparameter is a Boolean type parameter, that's why it's True or False. However, the end user sees it as Yes or No. – Kenny Dec 11 '15 at 16:06
0

i think your second dateadd function is in complete

dateadd(day, datediff(day, '19000101', cast @endtime as datetime2(7)))

here i can see only 2 parameters, i think 3rd one is missing, adding 3rd parameter may resolve issue

Bijith T
  • 54
  • 4
  • Bijith, this part functions properly. I am adding to the where clause with the employee filter. My goal is two fold: I want to filter by employee if desired, and to not filter by employee if desired. – Kenny Dec 11 '15 at 16:09