with
pharm as (select distinct companyid as CEXID from accounts acc),
s70 as (
select companyid as cexid,action from actions join accounts on actions.accountid = accounts.accountid where serverid =70 and action in (0,10,100) ),
s72 as (
select companyid as cexid,action from actions join accounts on actions.accountid = accounts.accountid where serverid =72 and action in (0,10,100) ),
s77 as (
select companyid as cexid,action from actions join accounts on actions.accountid = accounts.accountid where serverid =77 and action in (0,10,100) ),
s80 as (
select companyid as cexid,action from actions join accounts on actions.accountid = accounts.accountid where serverid =80 and action in (0,10,100) ),
s79 as (
select companyid as cexid,action from actions join accounts on actions.accountid = accounts.accountid where serverid =79 and action in (0,10,100) )
select pharm.cexid ,
Uniphar = case when s70.action = 0 then 'on'
when s70.action in (10,100) then 'disabled'
else 'Not Setup'
end,
imed = case when s72.action = 0 then 'on'
when s72.action in (10,100) then 'disabled'
else 'Not Setup'
end,
UD = case when s77.action = 0 then 'on'
when s77.action in (10,100) then 'disabled'
else 'Not Setup'
end,
lexon = case when s80.action = 0 then 'on'
when s80.action in (10,100) then 'disabled'
else 'Not Setup'
end,
EuroDrug = case when s79.action = 0 then 'on'
when s79.action in (10,100) then 'disabled'
else 'Not Setup'
end
from pharm
left join s70 on s70.cexid = pharm.cexid
left join s72 on s72.cexid = pharm.cexid
left join s77 on s77.cexid = pharm.cexid
left join s80 on s80.cexid = pharm.cexid
left join s79 on s79.cexid = pharm.cexid