Output data swap

Hello all,
I've been working on a query for a little while now, and I'm coming across an issue where my data is duplicating. I'm trying to retrieve multiple accounts, but unfortunately, if an account has more than one version of the account associated with it (I.e. ESPN and ESPN2). Within my output I would get two rows. 1st row would show ESPN as the 1st account, and ESPN2 as 2nd account. The 2nd row would show ESPN2 as the 1st account, and ESPN as the 2nd account. Therefore, I end up with 2 rows, when I should really just have 1 row where it will show ESPN as the 1st account, and ESPN2 as the 2nd account with no other rows. Has anyone ever encountered this before, and if so, how were you able to resolve that issue? Any assistance would be greatly appreciated. Thank you.
Damian

what is your query? can you share? have you tried using "distinct" Select distinct .....

Hello Pasi,
Here is the code I'm using. Unfortunately, I cannot provide the output as it contains personal information for specific accounts.

IF OBJECT_ID('TEMPDB..#tmp1o') IS NOT NULL DROP TABLE #tmp1o
IF OBJECT_ID('TEMPDB..#tmp2o') IS NOT NULL DROP TABLE #tmp2o
IF OBJECT_ID('TEMPDB..#tmp3o') IS NOT NULL DROP TABLE #tmp3o

select distinct
case
when a.[CSGSYS] = 8495 THEN CONCAT(a.[CSGSYS],'-', a.[CSGPRIN])
else CAST(a.[CSGSYS] AS VARCHAR(32))
end as 'SYS_PRIN'
,a.[BUSNAME]
,a.[ACCT]
,a.[HSE_KEY]
,sb.[VIP_FLG_SBB]
,ct.descr_ctd
,sb.[LS_CYC_CHRG_SBB] MRC
,sb.[DISCO_DTE_SBB]
,a.[ADDR1]
,a.[ADDR2]
,a.[CITY]
,a.[STATE]
,(a.[ADDR1]+' '+ a.[ADDR2]+' '+ a.[CITY]+' '+ a.[STATE]+' '+ a.[ZIP]) as 'FULL_ADDRESS'
,a.[HOMEPHONE]
,a.[BUSPHONE]
,a.[ORDERNO]
,a.[ORDER_COMPLETE_DTE]
,tr.ORDERID WBORDERNO
,tr.SALE_DATE
,sp.NAME
,tr.[PERNR]
,tr.[NTLOGIN]
,tr.[REP_TYPE]
,tr.[ROLE]

into #tmp1o

from ExternalUser.[Vantage].[SBB_BASE] as sb (nolock)

inner join
(select distinct
a.[CSGSYS]
,a.[CSGPRIN]
,a.[BUSNAME]
,a.[ACCT]
,a.[ADDR1]
,a.[ADDR2]
,a.[CITY]
,a.[STATE]
,a.[ZIP]
,a.[HOMEPHONE]
,a.[BUSPHONE]
,a.[HSE_KEY]
,a.[ORDERNO]
,a.[ORDER_COMPLETE_DTE]
,Dense_Rank () OVER ( PARTITION BY ACCT ORDER BY a.[ORDERNO] ) D_Rank
from
RptCB.[Ext].[v_WSTCommConnect] as A (nolock)
where a.BUSNAME not like '%comcast%'
and a.BUSNAME not like '%test%'
and a.BUSNAME not like '%evac%'
and a.BUSNAME not like '%kiosk%'
and a.BUSNAME not like '%xfinity%'
) a
on a.[ACCT] = sb.[SUB_ACCT_NO_SBB]
and sb.[DISCO_DTE_SBB] = '0001-01-01'
and a.D_Rank = 1

inner join
(select distinct
ACCOUNT_NUMBER
,tr.ORDERID
,tr.SALE_DATE
,tr.[PERNR]
,tr.[NTLOGIN]
,tr.[REP_TYPE]
,tr.[ROLE]
,Dense_Rank () OVER ( PARTITION BY ACCOUNT_NUMBER ORDER BY tr.orderid ) D_Rank
from
[ExternalUser].[iCoMP].[t_WSTCBSTransactionData] tr (nolock)
where tr.ROLE not like '%Nation%'
and tr.REP_SCOPE <> 'National'
and CANCEL1 is null
) tr
on a.ACCT = tr.ACCOUNT_NUMBER
and tr.D_Rank = 1

inner join [ExternalUser].[EMS].[SAPCurrent] sp (nolock)
on tr.PERNR = sp.PERNR
and tr.NTLOGIN = sp.[LOGIN_ID]

left outer join ExternalUser.[Vantage].[CTD_DISPLAY] ct (nolock)
on sb.[VIP_FLG_SBB] = ct.cde_value_ctd
and ct.cde_tbl_no_ctd = '24'
and ct.SPA_FLG_CTD = 'S'
and ct.DESCR_CTD <> 'VOD'
and ct.DESCR_CTD not like '%non%'

--select * from #tmp1o

select distinct
case
when a2.[CSGSYS] = 8495 THEN CONCAT(a2.[CSGSYS],'-', a2.[CSGPRIN])
else CAST(a2.[CSGSYS] AS VARCHAR(32))
end as 'SYS_PRIN2'
,a2.[BUSNAME] BUSNAME2
,a2.[ACCT] ACCT2
,a2.[HSE_KEY] HSE_KEY2
,sb2.[VIP_FLG_SBB] VIP_FLG_SBB2
,ct2.descr_ctd descr_ctd2
,sb2.[LS_CYC_CHRG_SBB] MRC2
,sb2.[DISCO_DTE_SBB] DISCO_DTE_SBB2
,a2.[ADDR1] ADDR3
,a2.[ADDR2] ADDR4
,a2.[CITY] CITY2
,a2.[STATE] STATE2
,(a2.[ADDR1]+' '+ a2.[ADDR2]+' '+ a2.[CITY]+' '+ a2.[STATE]+' '+ a2.[ZIP]) as 'FULL_ADDRESS2'
,a2.[HOMEPHONE] HOMEPHONE2
,a2.[BUSPHONE] BUSPHONE2
,a2.[ORDERNO] ORDERNO2
,a2.[ORDER_COMPLETE_DTE] ORDER_COMPLETE_DTE2
,tr2.ORDERID WBORDERNO2
,tr2.SALE_DATE SALE_DATE2
,sp2.NAME NAME2
,tr2.[PERNR] PERNR2
,tr2.[NTLOGIN] NTLOGIN2
,tr2.[REP_TYPE] REPTYPE2
,tr2.[ROLE] ROLE2

into #tmp2o

from ExternalUser.[Vantage].[SBB_BASE] as sb2 (nolock)

inner join
(select distinct
a2.[CSGSYS]
,a2.[CSGPRIN]
,a2.[BUSNAME]
,a2.[ACCT]
,a2.[ADDR1]
,a2.[ADDR2]
,a2.[CITY]
,a2.[STATE]
,a2.[ZIP]
,a2.[HOMEPHONE]
,a2.[BUSPHONE]
,a2.[HSE_KEY]
,a2.[ORDERNO]
,a2.[ORDER_COMPLETE_DTE]
,Dense_Rank () OVER ( PARTITION BY ACCT ORDER BY a2.[ORDERNO] ) D_Rank
from
RptCB.[Ext].[v_WSTCommConnect] as a2 (nolock)
where a2.BUSNAME not like '%comcast%'
and a2.BUSNAME not like '%test%'
and a2.BUSNAME not like '%evac%'
and a2.BUSNAME not like '%kiosk%'
and a2.BUSNAME not like '%xfinity%'
) a2
on a2.[ACCT] = sb2.[SUB_ACCT_NO_SBB]
and sb2.[DISCO_DTE_SBB] = '0001-01-01'
and a2.D_Rank = 1

inner join
(select distinct
ACCOUNT_NUMBER
,tr2.orderid
,tr2.SALE_DATE
,tr2.[PERNR]
,tr2.[NTLOGIN]
,tr2.[REP_TYPE]
,tr2.[ROLE]
,Dense_Rank () OVER ( PARTITION BY ACCOUNT_NUMBER ORDER BY tr2.orderid ) D_Rank
from
[ExternalUser].[iCoMP].[t_WSTCBSTransactionData] tr2 (nolock)
where tr2.ROLE not like '%Nation%'
and tr2.REP_SCOPE <> 'National'
and CANCEL1 is NULL
) tr2

on a2.[ACCT] = tr2.ACCOUNT_NUMBER
and tr2.D_Rank = 1

inner join [ExternalUser].[EMS].[SAPCurrent] sp2 (nolock)
on tr2.PERNR = sp2.PERNR
and tr2.NTLOGIN = sp2.[LOGIN_ID]

left outer join ExternalUser.[Vantage].[CTD_DISPLAY] ct2 (nolock)
on sb2.[VIP_FLG_SBB] = ct2.cde_value_ctd
and ct2.cde_tbl_no_ctd = '24'
and ct2.SPA_FLG_CTD = 'S'
and ct2.DESCR_CTD not like '%non%'

--select * from #tmp2o

select distinct c., d.

into #tmp3o

from #tmp1o c inner join #tmp2o d
on c.HSE_KEY = d.HSE_KEY2
and c.[ACCT] <> d.ACCT2
and c.[STATE] = d.STATE2
and c.[CITY] = d.CITY2

where c.WBORDERNO = (select min(WBORDERNO) WBORDERNO from #tmp1o c2
where c.WBORDERNO = c2.WBORDERNO
and c.SALE_DATE = c2.SALE_DATE)

and c.ORDERNO = (select min(ORDERNO) ORDERNO from #tmp1o c3
where c.ORDERNO = c3.ORDERNO)

and d.WBORDERNO2 = (select min(WBORDERNO2) WBORDERNO2 from #tmp2o d2
where d.WBORDERNO2 = d2.WBORDERNO2
and d.SALE_DATE2 = d2.SALE_DATE2)

and d.ORDERNO2 = (select min(ORDERNO2) ORDERNO2 FROM #tmp2o d3
where d.ORDERNO2 = d3.ORDERNO2)

select * from #tmp3o
order by [HSE_KEY]

I hope this provides you the information you need in order to provide some suggestions I might be able to use to update and possibly fix this query. Thank you.
D.

Hi

did I understand you correctly ?

Col1 Col2
ESPN1 ESPN2
ESPN2 ESPN1

Its possible to use CASE statement
Case col1 when 'ESPN2' Then 'ESPN1'

Am I correct in my understanding ?????
Does this look like something you want ?

where t1.account < t2.account
That will exclude the second row.
You might need to make it a left join and put this in the on clause to cater for those where there aren't duplicate accounts.

If there can be more than 2 versions you might consider ordering them using row_number() to selact the rows you want in the output.

Hi,
Currently it appears as though all of my rows are duplicating in the final output. I should be retrieving 355 records for the data that I'm trying to pull and instead I'm getting back 710 rows. It isn't only occurring with accounts that have similar names. The companies can be completely different, yet I will get a duplicate row with my account 1 now being shown as account 2, and my account 2 now being shown as my account 1. I'm not sure if a case statement would rectify this.
D.

Hi Nigel,
That code snippet worked! Thank you very much. However, in reviewing the data, I've noticed that if an account has more than 2 accounts associated with it, I again am dealing with the same issue where now the 3rd or 4th accounts within the main account are swapping. The fix you provided me seems to work like a charm on any rows of data where there are truly only 2 accounts within one row. However, if there are more than 2 accounts, I now have repeating lines for the 3rd and/or 4th accounts. I tried to use the same logic, but it only seems to work on a 1 to 1 basis. It doesn't seem to be working if there are more than 2 accounts. Any thoughts?
D.

How do you want the result to appear?
If you have ac1, ac2, ac3, ac4
do you want
ac1, ac2
ac1, ac3
ac1, ac4

or

ac1, ac2
ac3, ac4

or something else

In both cases probably the easiest solution (but not the fastest) is to number the rows then join including the row number - that can be slow but it makes it easy to alter for changing requirements.

something like
;with cte1 as
(
select id1, id2, act, seq=row_number() over (partition by id1, id2 order by act) from t1
)
, cte2 as
(
select id1, id2, act, seq=row_number() over (partition by id1, id2 order by act) from t2
)
select *
from cte1
left join cte2
on cte1.id1 = cte2.id1
and cte1.id2 = cte2.id2
and cte2.seq=cte1.seq+1
and cte1.seq % 2 = 1

select *
from cte1
left join cte2
on cte1.id1 = cte2.id1
and cte1.id2 = cte2.id2
and cte2.seq=cte1.seq+1
and cte1.seq = 1

Hi Nigel,
Just to confirm I understand. The first select statement in the code snippet you sent me is referring to the example
Act1 = Act2
Act1 = Act3
Act1 = Act4
and the second refers to the second example you referenced. The first example is the one I would need, because I want to make sure that if an account has more than 1 other account associated to it, the main account will remain Account 1, and the others will come up as account 2 field. Does that make sense?
D.

Hi Nigel,
Another question, would I add this to my 3rd temp table, and if so, where exactly would I place this with statement?
D.