1 2 3 4 5 6 7 8 9 10 11 12 13 14
|
Select distinct C1.owner, C1.state,
ISNULL((select count(C2.userdef05)
from contact2 C2 inner join contact1 C1 on C2.accountno=C1.accountno and C1.key5 not like 'I%'
where C2.userdef05='Airport'),0) as 'Airport',
ISNULL((select count(C2.userdef05)
from contact2 C2 inner join contact1 C1 on C2.accountno=C1.accountno and C1.key5 not like 'I%'
where C2.userdef05='Amateur'),0) as 'Amateur',
ISNULL((select count(C2.userdef05)
from contact2 C2 inner join contact1 C1 on C2.accountno=C1.accountno and C1.key5 not like 'I%'
where C2.userdef05='Auto parts'),0) as 'Auto parts'
FROM contact1 C1 inner join contact2 C2 on C1.accountno=C2.accountno
WHERE C1.key5 not like 'I%' and C1.owner in ('12usa - ', '13usa - ', '7can - o', '10can - ', '8can - m', '1sales', '9can - o')
GROUP BY C1.owner, C1.state |
Partager