Try this:
select
o.email as eml_addr,
max(cl.clicktime) as lst_clk_mnth,
max(o.opentime) as lst_opn_mnth
from
[open] o
inner join view_mailing m on o.mailingid=m.mailingid
inner join view_campaign c on c.campaignid=m.campaignid
left join click cl on o.mailingid=cl.mailingid and o.email=cl.email
inner join client c1 on c1.clientid = c.clientid
inner join client c2 on c2.sameclient = c1.sameclient
where
c2.clientid = 219
AND o.opentime >= '20130826'
group by
o.email;
1) distinct is not needed
2) with(nolock) - don't use hints unless you know what you are doing and what are the consequences
3) no formats in select list - don't format data in the database
4) cast & convert in where - not needed
5) rewrite nested SELECT-WHERE-IN as inner joins
6) check if there are indexes on PK and FK columns
7) view_mailing & view_campaign - if these are really views look inside them and check for possible performance killers
Submit an article or tip