March 21, 2019 at 4:53 am
--SQLselect distinct s.subscription_id,
s.subscription_nm
from #tmp_deal_hdr adh
inner join al on al.lic_id = adh.lic_id
inner join avt_user u on u.userid=adh.salesperson_id
inner join dx on dx.deal_id = adh.deal_id and dx.revs_no = adh.revs_no and dx.rebuild_ind = 'N'
inner join mh on mh.entity_id = adh.deal_id and mh.revs_no = adh.revs_no and mh.entity_type = 3
inner join c on c.contact_id = mh.contact_primary
inner join adl on adl.deal_id=adh.deal_id and adl.revs_no=adh.revs_no
inner join ts on ts.title_status_cd = adl.title_status_cd
inner join d on d.status_cd = adh.status_cd
inner join s on s.subscription_id = d.subscription_id and
s.subscription_id = c.subscription_id and s.subscription_id = ts.subscription_id
inner join #tmp_subscriber sub on sub.subscription_id = s.subscription_id
where s.subscription_type = 'dist_contact'
and adl.date_licensed >= @cal_date
--and adh.revs_no = (SELECT MAX(adhx.revs_no) FROM adhx WHERE adh.deal_id = adhx.deal_id)
and s.subscription_id not in (select slog.subscription_id from slog
where slog.subscription_id = s.subscription_id
and slog.subscription_type = s.subscription_type
and slog.subscriber_userid = sub.userid
and slog.email_addr_txt = sub.email_addr_txt
and slog.deal_id = adh.deal_id
and slog.status_cd = adh.status_cd
and slog.data_cd1 = ts.title_status_cd
and slog.data_date1 = adl.date_licensed
and slog.data_cd2 = c.contact_id
and slog.key_id = adl.deal_line_no)
March 21, 2019 at 5:08 am
I would use NOT EXISTS, but I'm not sure if the performance would be any different.
select distinct s.subscription_id,
s.subscription_nm
from #tmp_deal_hdr adh
inner join al on al.lic_id = adh.lic_id
inner join avt_user u on u.userid=adh.salesperson_id
inner join dx on dx.deal_id = adh.deal_id and dx.revs_no = adh.revs_no and dx.rebuild_ind = 'N'
inner join mh on mh.entity_id = adh.deal_id and mh.revs_no = adh.revs_no and mh.entity_type = 3
inner join c on c.contact_id = mh.contact_primary
inner join adl on adl.deal_id=adh.deal_id and adl.revs_no=adh.revs_no
inner join ts on ts.title_status_cd = adl.title_status_cd
inner join d on d.status_cd = adh.status_cd
inner join s on s.subscription_id = d.subscription_id and
s.subscription_id = c.subscription_id and s.subscription_id = ts.subscription_id
inner join #tmp_subscriber sub on sub.subscription_id = s.subscription_id
where s.subscription_type = 'dist_contact'
and adl.date_licensed >= @cal_date
--and adh.revs_no = (SELECT MAX(adhx.revs_no) FROM adhx WHERE adh.deal_id = adhx.deal_id)
and not exists (select *
from slog
where slog.subscription_id = s.subscription_id
and slog.subscription_type = s.subscription_type
and slog.subscriber_userid = sub.userid
and slog.email_addr_txt = sub.email_addr_txt
and slog.deal_id = adh.deal_id
and slog.status_cd = adh.status_cd
and slog.data_cd1 = ts.title_status_cd
and slog.data_date1 = adl.date_licensed
and slog.data_cd2 = c.contact_id
and slog.key_id = adl.deal_line_no)
March 22, 2019 at 7:14 am
You may be able to use the EXCEPT clause. Read about it here: https://www.tutorialspoint.com/sql/sql-except-clause.htm
From their explanation: "The SQL EXCEPT clause/operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement."
March 25, 2019 at 6:45 am
I think EXCEPT is cleaner, but you could also use the subquery as a derived table and LEFT JOIN to it WHERE slog.subscription_id IS NULL.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply