Hello everyone,
I've created a query, and it's bringing me back the main records I want. Unfortunately, it's also duplicating many of the records, and I've been trying to figure out a way to get rid of the duplicates, but have been unsuccessful thus far. I was hoping perhaps someone on this forum might be able to take a look at the query, and let me know what might be causing my records to duplicate. Or perhaps a method in which I can remove the duplication. I believe the issue may lie within the last temp tables joins. I've just been unable to figure out what I need to change to remove the duplication. I appreciate any assistance anyone can offer me. Thank you. Here's the code I've written:
IF OBJECT_ID('TEMPDB..#tmp1o') IS NOT NULL DROP TABLE #tmp1o
IF OBJECT_ID('TEMPDB..#tmp2o') IS NOT NULL DROP TABLE #tmp2o
select distinct
A.[CORP_SYSPRIN]
,A.[REGION]
,A.[DIVISION]
,A.[SALE]
,A.[CONNECT1]
,A.[CANCEL1]
,A.[ORDERID]
,A.[PERNR]
,sp.name
,A.[NTLOGIN]
,A.[REP_TYPE]
,A.[REP_SCOPE]
,A.[ROLE]
,A.[ACCOUNT_NAME]
,A.[SALE_DATE]
,A.[CONNECT_DATE]
,A.[LEAD_ASSIGN_DATE]
,A.[ACCOUNT_NUMBER]
,A.[EVENT_DATE]
,A.[SUBMIT_ORDER_DATE]
,A.[EXISTING_CUSTOMER]
,Replace(concat(A.[ADDRESS_1],' ',A.[ADDRESS_2]),' ',' ') as 'Primary Address'
,A.CITY
,A.STATE
,Replace(concat(A.[ADDRESS_1],' ',A.[ADDRESS_2],' ',A.[CITY],' ',A.[STATE],' ',A.ZIP),' ',' ') as 'Full Address'
,Dense_Rank () over (partition by [ORDERID] order by A.[LEAD_ASSIGN_DATE] ) D_Rank
into #tmp1o --1276518
from [ExternalUser].[EMS].[SAPCurrent] sp (nolock) inner join
(select distinct
A.[CORP_SYSPRIN]
,A.[REGION]
,A.[DIVISION]
,A.[SALE]
,A.[CONNECT1]
,A.[CANCEL1]
,A.[ORDERID]
,A.[PERNR]
,A.[NTLOGIN]
,A.[REP_TYPE]
,A.[REP_SCOPE]
,A.[ROLE]
,A.[ACCOUNT_NAME]
,A.[SALE_DATE]
,A.[CONNECT_DATE]
,A.[LEAD_ASSIGN_DATE]
,A.[ACCOUNT_NUMBER]
,A.[EVENT_DATE]
,A.[SUBMIT_ORDER_DATE]
,A.[EXISTING_CUSTOMER]
,A.[ADDRESS_1]
,A.[ADDRESS_2]
,A.CITY
,A.STATE
,A.ZIP
,Dense_Rank () over (partition by [ORDERID] order by A.[SALE_DATE] ) D_Rank
from [ExternalUser].[iCoMP].[t_WSTCBSTransactionData] A (nolock)
where A.CANCEL1 is NULL
and A.DIVISION = 'West Division') a
on sp.pernr = a.pernr
--and A.ACCOUNT_NUMBER = '8778104019420176'
--select * from #tmp1o
select distinct
B.[CORP_SYSPRIN] sysprin2
,B.[REGION] region2
,B.[DIVISION] division2
,B.[SALE] sale2
,B.[CONNECT1] connect12
,B.[CANCEL1] cancel12
,B.[ORDERID] orderid2
,B.[PERNR] pernr2
,sp2.name name2
,B.[NTLOGIN] ntlogin2
,B.[REP_TYPE]rep_type2
,B.[REP_SCOPE] rep_scope2
,B.[ROLE] role2
,B.[ACCOUNT_NAME] account_name2
,B.[SALE_DATE] sale_date2
,B.[CONNECT_DATE] connect_date2
,B.[LEAD_ASSIGN_DATE] lead_assign_date2
,B.[ACCOUNT_NUMBER] account_number2
,B.[EVENT_DATE] event_date2
,B.[SUBMIT_ORDER_DATE] submit_order_date2
,B.[EXISTING_CUSTOMER] existing_customer2
,Replace(concat(B.[ADDRESS_1], ' ',B.[ADDRESS_2]),' ',' ') as 'Primary Address2'
,B.CITY city2
,B.STATE state2
,Replace(concat(B.[ADDRESS_1],' ',B.[ADDRESS_2],' ',B.[CITY],' ',B.[STATE],' ',B.ZIP),' ',' ') as 'Full Address2'
,Dense_Rank () over (partition by [ORDERID] order by B.[SUBMIT_ORDER_DATE] ) D_Rank2
into #tmp2o --1276518
from [ExternalUser].[EMS].[SAPCurrent] sp2 (nolock) inner join
(select distinct
B.[CORP_SYSPRIN]
,B.[REGION]
,B.[DIVISION]
,B.[SALE]
,B.[CONNECT1]
,B.[CANCEL1]
,B.[ORDERID]
,B.[PERNR]
,B.[NTLOGIN]
,B.[REP_TYPE]
,B.[REP_SCOPE]
,B.[ROLE]
,B.[ACCOUNT_NAME]
,B.[SALE_DATE]
,B.[CONNECT_DATE]
,B.[LEAD_ASSIGN_DATE]
,B.[ACCOUNT_NUMBER]
,B.[EVENT_DATE]
,B.[SUBMIT_ORDER_DATE]
,B.[EXISTING_CUSTOMER]
,B.[ADDRESS_1]
,B.[ADDRESS_2]
,B.CITY
,B.STATE
,B.ZIP
,Dense_Rank () over (partition by [ORDERID] order by B.[SALE_DATE] ) D_Rank2
from [ExternalUser].[iCoMP].[t_WSTCBSTransactionData] B (nolock)
where B.CANCEL1 is NULL
and B.DIVISION = 'West Division') b
on sp2.pernr = b.pernr
--and B.ACCOUNT_NUMBER = '8778104203428159'
--select * from #tmp2o
;with cte1 as
(
select *,seq=row_number() over (partition by orderid order by ACCOUNT_NAME ) from #tmp1o
)
, cte2 as
(
select *, seq=row_number() over (partition by orderid2 order by account_name2 ) from #tmp2o
)
select distinct
cte1.CORP_SYSPRIN 'Primary Sys Prin'
,cte1.REGION 'Primary Region'
,cte1.DIVISION 'Primary Division'
,cte1.SALE 'Primary Sale Status'
,isnull(cte1.CONNECT1, ' ') 'Primary Connect Status'
,isnull(cte1.CANCEL1, ' ') 'Primary Cancel Status'
,cte1.ORDERID 'Primary Order ID'
,cte1.PERNR 'Primary Pernr'
,cte1.NAME 'Primary Name'
,cte1.NTLOGIN 'Primary NTLogin'
,cte1.REP_TYPE 'Primary Rep Type'
,cte1.REP_SCOPE 'Primary Rep Scope'
,isnull(cte1.ROLE,' ') 'Primary Role'
,cte1.ACCOUNT_NAME 'Primary Account Name'
,isnull(cte1.ACCOUNT_NUMBER,' ') 'Primary Account Number'
,cte1.[Full Address] 'Primary Full Address'
,cte1.SUBMIT_ORDER_DATE 'Primary Submit Order Date'
,cte1.SALE_DATE 'Primary Sale Date'
,cte1.CONNECT_DATE'Primary Connect Date'
,cte1.LEAD_ASSIGN_DATE 'Primary Lead Assign Date'
,cte1.EVENT_DATE 'Primary Event Date'
,cte1.EXISTING_CUSTOMER 'Primary Existing Customer'
,cte1.seq
,cte1.D_Rank
,cte2.sysprin2 'Secondary Sys Prin'
,cte2.region2 'Secondary Region'
,cte2.division2 'Secondary Division'
,cte2.sale2 'Secondary Sale Status'
,isnull(cte2.connect12, ' ') 'Secondary Connect Status'
,isnull(cte2.cancel12, ' ') 'Secondary Cancel Status'
,cte2.orderid2 'Secondary Order ID'
,cte2.pernr2 'Secondary Pernr'
,cte2.name2 'Secondary Name'
,cte2.ntlogin2 'Secondary NTLogin'
,cte2.rep_type2 'Secondary Rep Type'
,cte2.rep_scope2 'Secondary Rep Scope'
,isnull(cte2.role2,' ') 'Secondary Role'
,cte2.account_name2 'Secondary Account Name'
,isnull(cte2.account_number2,' ') 'Secondary Account Number'
,cte2.[Full Address2] 'Secondary Full Address'
,cte2.submit_order_date2 'Secondary Submit Order Date'
,cte2.sale_date2 'Secondary Sale Date'
,cte2.connect_date2 'Secondary Connect Date'
,cte2.lead_assign_date2 'Secondary Lead Assign Date'
,cte2.event_date2 'Secondary Event Date'
,cte2.existing_customer2 'Secondary Existing Customer'
,cte2.seq
,cte2.D_Rank2
from cte1 inner join cte2
on cte1.ACCOUNT_NAME = cte2.account_name2
and cte1.ORDERID <> cte2.ORDERID2
and cte1.CITY = cte2.city2
and cte1.STATE = cte2.state2
and cte1.ORDERID < cte2.orderid2
and cte1.seq = cte2.seq
and cte1.ACCOUNT_NUMBER <> 'NULL'
--and cte2.seq > 1
where cte1.NTLOGIN <> 'ccdsbatch'
and cte2.ntlogin2 <> 'ccdsbatch'
and cte1.REP_SCOPE <> 'National'
and cte2.rep_scope2 <> 'National'
and cte1.SALE_DATE between '2017-11-22' and '2018-03-21'
and cte2.SALE_DATE2 between '2017-11-22' and '2018-03-21'
order by cte1.ACCOUNT_NAME