SQL Case when help

Transact-SQL
1

This is my code and it works but how would i add multiple 3 character fields into each 'community' when, then statements?
for example: when community = ('MTP','MTN') then 'Joe'

select count(workorderid), ServiceRep

FROM (

select community, workorderid,

(case when community = 'MTD' then 'Kevin'
when community = 'MTP' then 'Joe'
when community = 'RDN' then 'Nate'
when community = 'LGZ' then 'Ben'
when community = 'RDU' then 'Patrick'
when community = 'NHL' then 'Gerry'

else 'Zero' end) as ServiceRep

from
tblcustomers as c

left outer join tblworkinprogress o
on c.customer_no = o.customer_no
left outer join customer_date d
on c.customer_no = d.customer_no

left outer join homes h
on c.customer_no = h.CustomerNumber

left outer join issues i
on h.homeID = i.homeID

left outer join workorders w
on i.issueID = w.issueID

where
cancelled = 0 and
job_no <> '' and
approved = 1 and
purchased = 1 and
job_no <> ''
and isnull(w.completeddate,'')=''
and warrantyexpirydate >= getdate()

) as prequery

group by ServiceRep

Blockquote

Blockquote

2

community IN ('MTP','MTN','ABC','DEF')

1 Like
3

Thanks, this is working great!