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
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
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
cancelled = 0 and
job_no <> '' and
approved = 1 and
purchased = 1 and
job_no <> ''
and warrantyexpirydate >= getdate()
) as prequery
group by ServiceRep