This is where I am stuck at
fyi: outout columns are 1 byte flag - either 'Y' or 'N'
COUNT(OPTOUT_TELE) AS tele_optout,
COUNT(OPTOUT_EMAIL) AS email_optout,
COUNT(OPTOUT_MAIL) AS mail_optout
AND (LOC.COUNTRY = 'CANADA')
AND (CUST.OPTOUT_TELE = 'Y'
OR CUST.OPTOUT_EMAIL = 'Y'
OR CUST.OPTOUT_MAIL = 'Y');
You could do it using a "CASE, WHEN, THEN" statement within the SELECT clause then sum your totals. Note that I've used the "newer" style join statements as joining on the where clause has been depricated by Microsoft and may not be supported in future versions of MSSS.
SELECT b.loc_id, SUM(CASE WHEN a.tele_optout = 'Y' THEN 1 ELSE 0 END) tele_optout,
SUM(CASE WHEN a.email_optout = 'Y' THEN 1 ELSE 0 END) email_optout,
SUM(CASE WHEN a.mail_optout = 'Y' THEN 1 ELSE 0 END) mail_optout
FROM CUST a
ON a.loc_id = b.loc_id
AND b.country = 'CANADA'
GROUP BY b.loc_id
Last edited by marc_; 08-31-11 at 10:45.