SQLTeam.com | Weblogs | Forums

Duplicate records


#1

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


#2

first of all, get rid of NOLOCK. It's not what you think. Second, when I have a problem like this here's what I do:

  1. rewrite the query (temporarily) like this:

    select * from table1 t1
    join table2 t2 on t1.col1 = t2.col1
    join table3 t3 on t2.col2 = t3.col2

etc. until I have all the tables joined up.

  1. Then, I (in ssms) select the query starting with just the SELECT, then adding the first JOIN, then the second, etc. to see where the numbers increase and/or duplicates appear.
  2. That usually leads to a realization of missing join predicates.

#3

Hi gbritton,
The first two temp tables are pulling from the same 2 tables, so the output is the exact same number of records. The third temp table is where I am comparing between the output of the first two temp tables, and supposed to only pull back accounts in which the primary account name matches the secondary account name, however, the order id's for both accounts are different. Placing the

cte1.ORDERID <> cte2.ORDERID2

portion within the join should reduce the records, which it is, but then there are some accounts, not all that are duplicating, and I've been unable to figure out why only a few accounts are duplicating, and not all of the accounts that I retrieve within the third temp table. I hope this explains the issue I'm dealing with a little better.
Also, I was instructed to add "NOLOCK" within all of my queries as there are many people that access these tables at the same time, and without the "NOLOCK" they would be unable to access tables that I may be accessing when running my queries.
Thank you for replying.


#4

that's not true. you've been given false information. Also the risk with NOLOCK is dirty reads. Know what you can get with dirty reads? Duplicates


#5

Hmm, I took your advice and removed the NOLOCK's, but I'm still receiving dupes. I've also gone through each temp table to see if I can find anything that might give me an indication as to why I'm getting dupes. I get the same number of records for each temp table, then I get a reduced number of records after running the last select which is joining the first two temp tables, however, this is where the dupes are coming in, only I can't figure out what it is about the join that is causing the dupes. So frustrated.


#6

I know the feeling! If I knew your data better I could figure it out. Can you make a small (a few rows) test case that creates the dupes and post it here? Then we can dig into it.


#7

gbritton,
I would like to provide examples of what I'm retrieving, unfortunately the data that within my results contains sensitive information, that I'm not allowed to share outside of my team. Essentially, I have two sets of data, and when I join both sets, I'm getting duplicate account numbers within the secondary data set. I've tried manipulating the with statement a number of different ways, as well as the join parameters within the final select statement, but that hasn't been able to resolve my duplication issue.


#8

see if you can recreate the problem with simulated data, then post that


#9

what's with the cake icons? making me hungry


#10

hover over it and read the popup message


#11

Here's a sample of the data I'm retrieving. The primary account numbers can have duplicate information, if there are multiple order id's that are linked to that initial order id and account number. However, all secondary account information should be unique, and currently, it isn't. You can determine duplicates by the last 8 numbers within the secondary account number field, and the secondary address field.

Primary Order ID Primary Name Primary Role Primary Account Name Primary Account Number Primary Full Address Primary Submit Order Date Primary Sale Date Primary Connect Date Primary Lead Assign Date seq D_Rank Secondary Order ID Secondary Name Secondary Role Secondary Account Name Secondary Account Number Secondary Full Address Secondary Submit Order Date Secondary Sale Date Secondary Connect Date Secondary Lead Assign Date seq D_Rank2
18706319 Warren Jr., Aaron Business Account Exec I A Better Bail Bond 1111234567003255 4240 HIGHWAY 6 N STE B HOUSTON TX 77084 1/26/2018 1/26/2018 1/29/2018 1/23/2018 1 1 18887210 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234567058390 4240 HIGHWAY 6 N STE B HOUSTON TX 77084 2/20/2018 2/20/2018 NULL 2/19/2018 1 1
18859390 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234511602506 10024 HOMESTEAD RD HOUSTON TX 77016 2/20/2018 2/20/2018 NULL 2/14/2018 1 1 18887210 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234567058390 4240 HIGHWAY 6 N STE B HOUSTON TX 77084 2/20/2018 2/20/2018 NULL 2/19/2018 1 1
18859499 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234587339836 1416 WASHINGTON AVE HOUSTON TX 77002 2/20/2018 2/20/2018 NULL 2/20/2018 1 1 18887210 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234567058390 4240 HIGHWAY 6 N STE B HOUSTON TX 77084 2/20/2018 2/20/2018 NULL 2/19/2018 1 1
18859610 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234587460368 5317 MALMEDY RD HOUSTON TX 77033 2/20/2018 2/20/2018 NULL 2/14/2018 1 1 18887210 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234567058390 4240 HIGHWAY 6 N STE B HOUSTON TX 77084 2/20/2018 2/20/2018 NULL 2/19/2018 1 1
18706319 Warren Jr., Aaron Business Account Exec I A Better Bail Bond 1111234567003255 4240 HIGHWAY 6 N STE B HOUSTON TX 77084 1/26/2018 1/26/2018 1/29/2018 1/23/2018 1 1 18859499 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234587339836 1416 WASHINGTON AVE HOUSTON TX 77002 2/20/2018 2/20/2018 NULL 2/20/2018 1 1
18859390 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234511602506 10024 HOMESTEAD RD HOUSTON TX 77016 2/20/2018 2/20/2018 NULL 2/14/2018 1 1 18859499 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234587339836 1416 WASHINGTON AVE HOUSTON TX 77002 2/20/2018 2/20/2018 NULL 2/20/2018 1 1
18706319 Warren Jr., Aaron Business Account Exec I A Better Bail Bond 1111234567003255 4240 HIGHWAY 6 N STE B HOUSTON TX 77084 1/26/2018 1/26/2018 1/29/2018 1/23/2018 1 1 18859610 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234587460368 5317 MALMEDY RD HOUSTON TX 77033 2/20/2018 2/20/2018 NULL 2/14/2018 1 1
18859390 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234511602506 10024 HOMESTEAD RD HOUSTON TX 77016 2/20/2018 2/20/2018 NULL 2/14/2018 1 1 18859610 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234587460368 5317 MALMEDY RD HOUSTON TX 77033 2/20/2018 2/20/2018 NULL 2/14/2018 1 1
18859499 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111234587339836 1416 WASHINGTON AVE HOUSTON TX 77002 2/20/2018 2/20/2018 NULL 2/20/2018 1 1 18859610 Martinez, Raul Sr Business Account Exec A Better Bail Bond 1111345687460368 5317 MALMEDY RD HOUSTON TX 77033 2/20/2018 2/20/2018 NULL 2/14/2018 1 1
18773770 Reeths, Bonni L Business Services Rep A BRIGHT FUTURE 1234678986495512 777 CAMPUS COMMONS RD STE 20061 SACRAMENTO CA 95825 2/1/2018 2/2/2018 2/5/2018 2/1/2018 1 1 19105440 Reeths, Bonni L Business Services Rep A BRIGHT FUTURE 3456789086012184 777 CAMPUS COMMONS RD STE 200 SACRAMENTO CA 95825 3/19/2018 3/19/2018 3/20/2018 3/19/2018 1 1
19014418 Peltz, Katherine Mariah Business Services Rep A BRIGHT FUTURE 1234678986538469 333 UNIVERSITY AVE STE 200 SACRAMENTO CA 95825 3/14/2018 3/14/2018 3/14/2018 3/6/2018 1 1 19105440 Reeths, Bonni L Business Services Rep A BRIGHT FUTURE 3456789086012184 777 CAMPUS COMMONS RD STE 200 SACRAMENTO CA 95825 3/19/2018 3/19/2018 3/20/2018 3/19/2018 1 1
18117034 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338242 460 E CASTLE HARBOUR DR FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 19063821 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1212343440338226 1414 S FRIENDSWOOD DR STE B316 FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 3/13/2018 1 1
18117076 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338259 16710 TOWNES RD FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 19063821 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1212343440338226 1414 S FRIENDSWOOD DR STE B316 FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 3/13/2018 1 1
18117251 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338234 523 GLENEAGLES DR FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 19063821 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1212343440338226 1414 S FRIENDSWOOD DR STE B316 FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 3/13/2018 1 1
18117302 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338275 806 MERRIBROOK LN FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 19063821 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1212343440338226 1414 S FRIENDSWOOD DR STE B316 FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 3/13/2018 1 1
18117447 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338267 208 STONEHENGE LN FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 19063821 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1212343440338226 1414 S FRIENDSWOOD DR STE B316 FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 3/13/2018 1 1
18117034 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338242 460 E CASTLE HARBOUR DR FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 18117251 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 3456123440338234 523 GLENEAGLES DR FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1
18117076 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338259 16710 TOWNES RD FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 18117251 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 3456123440338234 523 GLENEAGLES DR FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1
18117034 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338242 460 E CASTLE HARBOUR DR FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 18117447 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111345640338267 208 STONEHENGE LN FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1
18117076 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338259 16710 TOWNES RD FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 18117447 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111345640338267 208 STONEHENGE LN FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1
18117251 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338234 523 GLENEAGLES DR FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 18117447 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111345640338267 208 STONEHENGE LN FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1
18117302 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338275 806 MERRIBROOK LN FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 18117447 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111345640338267 208 STONEHENGE LN FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1
18117034 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338242 460 E CASTLE HARBOUR DR FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 18117302 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1212343440338275 806 MERRIBROOK LN FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1
18117076 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338259 16710 TOWNES RD FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 18117302 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1212343440338275 806 MERRIBROOK LN FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1
18117251 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1111234540338234 523 GLENEAGLES DR FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1 18117302 Borkovich, Braeden James Sr Business Account Exec A Treasure of Dreams 1212343440338275 806 MERRIBROOK LN FRIENDSWOOD TX 77546 3/20/2018 3/20/2018 NULL 10/24/2017 1 1

#12

we all join the same day?


#13

OK now can I ask you to submit this again as machine readable? I mean this:

CREATE TABLE ...
INSERT INTO ...


#14

Hi gbritton,
Are you referring to the code? Because I ended up starting from scratch, and creating a whole new code. The code is working better, but I was asked to make some changes, and it appears as though the records are swapping. Therefore, they aren't true duplicates in the sense as each row is unique, however, the data within the previous records are the opposite of the data within the subsequent records. I.e.

OrderID = 123456 Second Order ID = 654321
OrderID = 654321 Second Order ID = 123456

I've tried a few different things to try and get around this, but have been unsuccessful thus far. I was hoping you might be able to take a look at the new code I've written to see if there's anything you notice that I can change that will stop these "duplicates" from occurring. I appreciate any assistance you can give me.

IF OBJECT_ID('TEMPDB..#Transactions') IS NOT NULL DROP TABLE #Transactions
IF OBJECT_ID('TEMPDB..#ViolatorList') IS NOT NULL DROP TABLE #ViolatorList

select
b.ORDERID,
b.PERNR,
e.NAME,
b.[NTLOGIN],
b.[REP_TYPE],
b.[REP_SCOPE],
b.[ROLE],
b.ACCOUNT_NAME,
b.ACCOUNT_NUMBER,
s.HSE_KEY_SBB,
b.SALE_DATE,
b.CONNECT_DATE,
b.CANCEL_DATE,
Replace(concat(b.[ADDRESS_1],' ',b.[ADDRESS_2],' ',b.CITY,' ',b.STATE,' ',b.ZIP),' ',' ') as Full_Address

into #Transactions
from [ExternalUser].[iCoMP].[t_WSTCBSTransactionData] B
join externaluser.vantage.SBB_BASE as s
on cast(s.sub_acct_no_sbb as nvarchar(16)) = b.ACCOUNT_NUMBER
join wisdm.dim.house as h
on h.csgHouseKey = s.HSE_KEY_SBB
join wisdm.dim.employee as e
on e.pernr = b.pernr
and e.iscurrent = 1
where B.CANCEL1 is NULL
and CANCEL_DATE is NULL
and B.DIVISION = 'West Division'
and sale_date >= dateadd(m,-1,getdate())

--929347 in 22sec

select distinct
v.orderid ViolatorOrderID,
v.sale_date ViolatorSale,
v.PERNR Violator,
v.name ViolatorName,
v.ntlogin ViolatorNTlogin,
v.rep_type ViolatorRepType,
v.rep_scope ViolatorRepScope,
v.role ViolatorRole,
P.ORDERID PrimaryOrderID,
p.sale_date PrimarySaleDate,
p.PERNR PrimaryPernr,
p.name PrimaryName,
p.ntlogin PrimaryNTlogin,
p.rep_type PrimaryRepType,
p.rep_scope PrimaryRepScope,
p.role PrimaryRole,
p.ACCOUNT_NAME,
p.ACCOUNT_NUMBER,
p.HSE_KEY_SBB,
--p.SALE_DATE,
p.CONNECT_DATE,
p.Full_Address

into #ViolatorList
from #Transactions as v /violator/
join #Transactions as p /prior/
on v.HSE_KEY_SBB = p.HSE_KEY_SBB --and v.account_number = p.account_number
and p.sale_date between dateadd(d,-30,v.sale_date) and v.sale_date --and v.sale_date <> p.sale_date and p.pernr <> v.pernr
and p.ORDERID < v.ORDERID
--11512 in 7 sec

select distinct CASE WHEN violatorOrderID < PrimaryOrderID THEN 'ORDER IN FIRST' ELSE NULL END ValidateSale,*
from #ViolatorList

where ViolatorNTlogin <> 'CCDSBATCH'
and PrimaryName <> 'CCDSBATCH'
and ViolatorRepScope <> 'National'
and PrimaryRepScope <> 'National'
order by ACCOUNT_NAME

Thank you,
D.


#15

What I mean is, post some sample data that will I can use to run your query. post the data as CREATE TABLE and INSERT TABLE commands