Duplication Record

UDA
Enthusiast

Duplication Record

Hi Guys

I have created a query below and it giving me duplicates,
what Im looking for is a unique customer record based on account_num or customer_number, i.e. need to report the latest account_num per customer_number, example

Cust_No Account_Num Account_modifier_Num Account_Open_Dt
2624208 5222625460055473 10 2003/12/29
2624208 5221008460884344 10 2006/08/24
2624208 40206556000 1 2008/08/20
2624208 248831496001 28 2007/08/08
2624208 48969664000 1 2004/03/27
2624208 144970201000 21 2008/12/15

Problem, Cust_Num is duplicated and I also have the same situation where the account_num is duplicated for different customer_number, how do I resolve this issue? I need to report the cust_num,account_num that have the latest Account_Open_Dt, I have tried qualify it not working.
See the SQL below

SELECT
TAPY.TCRM_Party_Id AS Cust_Num
,TAPY.Account_Num
,TAPY.Account_Modifier_Num
,TAPY.Account_Open_Dt

FROM DEVTCRMEIW.TCRM_ACCT_PARTY TAPY

INNER JOIN EIW.AGREEMENT AGRMNT
ON
TAPY.Account_Num = AGRMNT.Account_Num
AND TAPY.Account_Modifier_Num = AGRMNT.Account_Modifier_Num
AND TAPY.Account_Open_Dt = AGRMNT.Account_Open_Dt
AND AGRMNT.Account_Close_Dt IS NULL
WHERE TAPY.Party_Id = '2624208'
AND TAPY.Account_Party_Role_Cd = 1
AND TAPY.Account_Party_End_Dt IS NULL

AND (TAPY.Account_Num,TAPY.Account_Open_Dt ,TAPY.Party_Id) IN (
SELECT
DISTINCT

Account_Num

,MAX(CAST( Account_Open_Dt AS DATE FORMAT 'YYYY/MM/DD')) Account_Open_Dt
,Party_Id
FROM DEVTCRMEIW.TCRM_ACCT_PARTY
GROUP BY Account_Num,Party_Id
)

2 REPLIES

Re: Duplication Record

this should get you the results you need from tapy. It utilizes an inline view, which I believe is supported by TD, I ran this on a mysql db. You should be able to build upon this and add the agreement joins:

select a.cust_num, a.acct_num, a.acct_open_date, a.acct_modifier_num
from tapy1 a, (select cust_num, max(acct_open_date) acct_open_date from tapy1 group by cust_num) b
where a.cust_num = b.cust_num
and a.acct_open_date = b.acct_open_date;
Enthusiast

Re: Duplication Record

Couldn't you use partition by

DATA
Cust_No Account_Num Account_modifier_Num Account_Open_Dt
2624208 5222625460055473 10 2003/12/29
2624208 5221008460884344 10 2006/08/24
2624208 40206556000 1 2008/08/20
2624208 248831496001 28 2007/08/08
2624208 48969664000 1 2004/03/27
2624208 144970201000 21 2008/12/15

QUERY USED
SELECT
TAPY.TCRM_Party_Id AS Cust_Num
,TAPY.Account_Num
,TAPY.Account_Modifier_Num
,TAPY.Account_Open_Dt

FROM DEVTCRMEIW.TCRM_ACCT_PARTY TAPY

INNER JOIN EIW.AGREEMENT AGRMNT
ON
TAPY.Account_Num = AGRMNT.Account_Num
AND TAPY.Account_Modifier_Num = AGRMNT.Account_Modifier_Num
AND TAPY.Account_Open_Dt = AGRMNT.Account_Open_Dt
AND AGRMNT.Account_Close_Dt IS NULL
WHERE TAPY.Party_Id = '2624208'
AND TAPY.Account_Party_Role_Cd = 1
AND TAPY.Account_Party_End_Dt IS NULL

QUALIFY ROW_NUMBER () OVER (PARTITION BY TAPY.TCRM_Party_Id, TAPY.Account_Num ORDER BY TAPY.Account_Open_Dt DESC) = 1