Duplicate records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicate records

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2011-11-10 : 05:21:06

Hello,

The joins in the below script ending up with duplicates for PATNT_REFNO,i am wondering where i have done wrong in the join part!

SELECT
PATIENT.PATNT_REFNO, PATIENTS.PASID, PATIENTS.SURNAME,
PATIENTS.FORENAME, PATIENTS.DTTM_OF_BIRTH,
KRT_PROCEDURES.DGPRO_DTTM,KRT_PROCEDURES.CCSXT_CODE,
KRT_PROCEDURES.SORCE_REFNO,KRT_PROCEDURES.SORCE_CODE,
KRT_PROCEDURES.DGPRO_REFNO,KRT_PROCEDURES.MPLEV_REFNO,KRT_PROCEDURES.PROCA_REFNO,
REFERENCE_VALUES.RFVAL_REFNO,REFERENCE_VALUES.DESCRIPTION,PROF_CARERS.PROCA_REFNO,
REFERRALS.REFRL_REFNO,REFERRALS.REFTO_PROCA_REFNO,PROF_CARERS.PROCA_REFNO

FROM (((KRT_PROCEDURES
INNER JOIN (REFERRALS
INNER JOIN PATIENTS ON REFERRALS.PATNT_REFNO = PATIENTS.PATNT_REFNO)
ON (KRT_PROCEDURES.DGPRO_REFNO = REFERRALS.REFRL_REFNO))
INNER JOIN REFERENCE_VALUES ON KRT_PROCEDURES.MPLEV_REFNO = REFERENCE_VALUES.RFVAL_REFNO)
INNER JOIN PROF_CARERS ON REFERRALS.REFTO_PROCA_REFNO = PROF_CARERS.PROCA_REFNO)

WHERE
(((KRT_PROCEDURES.CCSXT_CODE)='ICPD')
AND(KRT_PROCEDURES.ARCHV_FLAG)
Is Null Or
(KRT_PROCEDURES.ARCHV_FLAG)='N');

Thanks

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 06:36:36

please show some sample data and then explain what you mean by duplicates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2011-11-10 : 09:27:31

Hello visak,

this is my join script

SELECT
PATIENT.PATNT_REFNO, PATIENTS.PASID, PATIENTS.SURNAME,
PATIENTS.FORENAME, PATIENTS.DTTM_OF_BIRTH,
KRT_PROCEDURES.DGPRO_DTTM,KRT_PROCEDURES.CCSXT_CODE,
KRT_PROCEDURES.SORCE_REFNO,KRT_PROCEDURES.SORCE_CODE,
KRT_PROCEDURES.DGPRO_REFNO,KRT_PROCEDURES.MPLEV_REFNO,KRT_PROCEDURES.PROCA_REFNO
FROM ((KRT_PROCEDURES
INNER JOIN (REFERRALS INNER JOIN PATIENTS ON REFERRALS.PATNT_REFNO = PATIENTS.PATNT_REFNO) ON (KRT_PROCEDURES.SORCE_REFNO = REFERRALS.REFRL_REFNO)
AND (KRT_PROCEDURES.PATNT_REFNO = PATIENTS.PATNT_REFNO))

OUTPUT:

PATNT_REFNO PASID FORENAME SURNAME DATE
110054 N0000196 ZAMEEN DFDFE 1943-04-12 00:00:00.000
110054 N0000196 ZAMEEN DFDFE 1943-04-12 00:00:00.000
110054 N0000196 ZAMEEN DFDFE 1943-04-12 00:00:00.000
112480 N0000524 FGDFF FDFVV 1941-02-23 00:00:00.000
112480 N0000524 FGDFF FDFVV 1941-02-23 00:00:00.000
120392 N0001505 IAN BARRON 1953-01-18 00:00:00.000
120392 N0001505 IAN BARRON 1953-01-18 00:00:00.000

these are the duplicates i am getting and i checked the join script couldnt able to trace where i done wrong.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 09:52:18

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PATIENT.PATNT_REFNO, PATIENTS.PASID, PATIENTS.SURNAME,
PATIENTS.FORENAME, PATIENTS.DTTM_OF_BIRTH,
KRT_PROCEDURES.DGPRO_DTTM,KRT_PROCEDURES.CCSXT_CODE,
KRT_PROCEDURES.SORCE_REFNO,KRT_PROCEDURES.SORCE_CODE,
KRT_PROCEDURES.DGPRO_REFNO,KRT_PROCEDURES.MPLEV_REFNO,KRT_PROCEDURES.PROCA_REFNO ORDER BY PATIENT.PATNT_REFNO) AS Rn,
PATIENT.PATNT_REFNO, PATIENTS.PASID, PATIENTS.SURNAME,
PATIENTS.FORENAME, PATIENTS.DTTM_OF_BIRTH,
KRT_PROCEDURES.DGPRO_DTTM,KRT_PROCEDURES.CCSXT_CODE,
KRT_PROCEDURES.SORCE_REFNO,KRT_PROCEDURES.SORCE_CODE,
KRT_PROCEDURES.DGPRO_REFNO,KRT_PROCEDURES.MPLEV_REFNO,KRT_PROCEDURES.PROCA_REFNO
FROM ((KRT_PROCEDURES
INNER JOIN (REFERRALS INNER JOIN PATIENTS ON REFERRALS.PATNT_REFNO = PATIENTS.PATNT_REFNO) ON (KRT_PROCEDURES.SORCE_REFNO = REFERRALS.REFRL_REFNO)
AND (KRT_PROCEDURES.PATNT_REFNO = PATIENTS.PATNT_REFNO))
)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2011-11-10 : 09:57:31

Thanks for reply visakh,

its my fault sorry,those are not duplicates the date field for KRT PROCEDURES was different for those patients.
all i need to do is to add a SR KEY field to the table.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 10:06:24

you mean this?

SELECT  *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PATIENT.PATNT_REFNO, PATIENTS.PASID, PATIENTS.SURNAME, 
PATIENTS.FORENAME ORDER BY PATIENTS.DTTM_OF_BIRTH) AS Rn,
PATIENT.PATNT_REFNO, PATIENTS.PASID, PATIENTS.SURNAME, 
PATIENTS.FORENAME, PATIENTS.DTTM_OF_BIRTH,
KRT_PROCEDURES.DGPRO_DTTM,KRT_PROCEDURES.CCSXT_CODE,
KRT_PROCEDURES.SORCE_REFNO,KRT_PROCEDURES.SORCE_CODE, 
KRT_PROCEDURES.DGPRO_REFNO,KRT_PROCEDURES.MPLEV_REFNO,KRT_PROCEDURES.PROCA_REFNO
FROM ((KRT_PROCEDURES
INNER JOIN (REFERRALS INNER JOIN PATIENTS ON REFERRALS.PATNT_REFNO = PATIENTS.PATNT_REFNO) ON (KRT_PROCEDURES.SORCE_REFNO = REFERRALS.REFRL_REFNO) 
AND (KRT_PROCEDURES.PATNT_REFNO = PATIENTS.PATNT_REFNO))
)t
WHERE Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2011-11-10 : 10:26:23

Hello visakh,

i am trying with the below script,with inclusions of ur comments getting few syntax errors ,can you tell me why did u use Rn=1 in the script.

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PATIENT.PATNT_REFNO, PATIENTS.PASID, PATIENTS.SURNAME,
PATIENTS.FORENAME ORDER BY PATIENTS.DTTM_OF_BIRTH) AS Rn,

PATIENTS.PATNT_REFNO, PATIENTS.PASID, PATIENTS.FORENAME, PATIENTS.SURNAME, PATIENTS.DTTM_OF_BIRTH,
REFERRALS.REFRL_REFNO, REFERRALS.CLOSR_DATE, REFERRALS.RECVD_DTTM,
PROF_CARERS.PROCA_REFNO,
KRT_PROCEDURES.DGPRO_REFNO,KRT_PROCEDURES.DGPRO_DTTM, KRT_PROCEDURES.SORCE_CODE,
[Level].DESCRIPTION,
KRT_PROCEDURES.CCSXT_CODE, KRT_PROCEDURES.ARCHV_FLAG,KRT_PROCEDURES.CODE AS OPERATION_CODE,
KRT_PROCEDURES.USER_MODIF,KRT_PROCEDURES.CREATE_DTTM, KRT_PROCEDURES.MODIF_DTTM
FROM (((KRT_PROCEDURES
INNER JOIN (REFERRALS INNER JOIN PATIENTS ON REFERRALS.PATNT_REFNO = PATIENTS.PATNT_REFNO) ON (KRT_PROCEDURES.SORCE_REFNO = REFERRALS.REFRL_REFNO)
AND (KRT_PROCEDURES.PATNT_REFNO = PATIENTS.PATNT_REFNO))
INNER JOIN REFERENCE_VALUES AS [Level] ON KRT_PROCEDURES.MPLEV_REFNO = [Level].RFVAL_REFNO)
)t
WHERE
(((REFERRALS.CLOSR_DATE) Is Null)
AND (([Level].DESCRIPTION)='Main Problem Diagnosis')
AND ((KRT_PROCEDURES.CCSXT_CODE)='I10')
AND ((KRT_PROCEDURES.ARCHV_FLAG) Is Null
Or (KRT_PROCEDURES.ARCHV_FLAG)='N'))
and Rn=1,
ORDER BY PATIENTS.PASID;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 10:50:34

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PATIENT.PATNT_REFNO, PATIENTS.PASID, PATIENTS.SURNAME,
PATIENTS.FORENAME ORDER BY PATIENTS.DTTM_OF_BIRTH) AS Rn,

PATIENTS.PATNT_REFNO, PATIENTS.PASID, PATIENTS.FORENAME, PATIENTS.SURNAME, PATIENTS.DTTM_OF_BIRTH,
REFERRALS.REFRL_REFNO, REFERRALS.CLOSR_DATE, REFERRALS.RECVD_DTTM,
PROF_CARERS.PROCA_REFNO,
KRT_PROCEDURES.DGPRO_REFNO,KRT_PROCEDURES.DGPRO_DTTM, KRT_PROCEDURES.SORCE_CODE,
[Level].DESCRIPTION,
KRT_PROCEDURES.CCSXT_CODE, KRT_PROCEDURES.ARCHV_FLAG,KRT_PROCEDURES.CODE AS OPERATION_CODE,
KRT_PROCEDURES.USER_MODIF,KRT_PROCEDURES.CREATE_DTTM, KRT_PROCEDURES.MODIF_DTTM
FROM (((KRT_PROCEDURES
INNER JOIN (REFERRALS INNER JOIN PATIENTS ON REFERRALS.PATNT_REFNO = PATIENTS.PATNT_REFNO) ON (KRT_PROCEDURES.SORCE_REFNO = REFERRALS.REFRL_REFNO)
AND (KRT_PROCEDURES.PATNT_REFNO = PATIENTS.PATNT_REFNO))
INNER JOIN REFERENCE_VALUES AS [Level] ON KRT_PROCEDURES.MPLEV_REFNO = [Level].RFVAL_REFNO)
WHERE
(((REFERRALS.CLOSR_DATE) Is Null)
AND (([Level].DESCRIPTION)='Main Problem Diagnosis')
AND ((KRT_PROCEDURES.CCSXT_CODE)='I10')
AND ((KRT_PROCEDURES.ARCHV_FLAG) Is Null
Or (KRT_PROCEDURES.ARCHV_FLAG)='N'))
)t
WHERE Rn=1
ORDER BY PASID;
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2011-11-10 : 11:00:59

Thanks visakh,

got the expected result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 11:04:00

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources