OUTER APPLY REMOVE DUPLICATES

Question

I have used outer apply to pull the most recent careID and diagnosis date and then used aggregation function to count the total. The code worked, however as soon as I added another join which is:

LEFT JOIN dbo.tblDEFINITIVE_TREATMENT Tr ON Ref.CARE_ID=Tr.CARE_ID

I get duplicate care_id in my output. How can I remove duplicate CARE_id without removing this join? I have tried using aliases, however it did not solve the issue.

LEFT JOIN dbo.ltblDIAGNOSIS diag1 ON ref.N4_2_DIAGNOSIS_CODE=diag1.DIAG_CODELEFT
JOIN tblDEMOGRAPHICS dem ON ref.PATIENT_ID=dem.PATIENT_IDLEFT
JOIN dbo.tblDEFINITIVE_TREATMENT Tr ON Ref.CARE_ID=Tr.CARE_IDLEFT
JOIN dbo.ltblDEFINITIVE_TYPE Def ON Tr.TREATMENT=Def.TREAT_CODE



OUTER APPLY (SELECT TOP 1  N4_1_DIAGNOSIS_DATE, diag1.CARE_ID
             FROM DIAGNOSIS diag1
             WHERE ref.CARE_ID=diag1.CARE_ID
             ORDER BY N4_1_DIAGNOSIS_DATE  DESC) diag

I have tried using aliases, however it did not solve the issue (to remove duplicate CARE_ID)

Answer 1

You don't show the whole query which makes it hard to give a good answer. You also don't say what platform you are using, so I can't use a platform specific answer but here is the problem:

When you join to the table tblDEFINITIVE_TREATMENT you get two rows for each CARE_IDLEFT value. So the easy way to fix that is to do a group by like this:

Instead of

JOIN dbo.tblDEFINITIVE_TREATMENT Tr ON Ref.CARE_ID=Tr.CARE_IDLEFT

do

JOIN (
   SELECT CARE_IDLEFT, MIN(START_DATE) AS START_DATE, MIN(DELAY_COMMENTS)
   FROM dbo.tblDEFINITIVE_TREATMENT
   GROUP BY CARE_IDLEFT
) Tr ON Ref.CARE_ID=Tr.CARE_IDLEFT

Note, I used MIN to pick which value to use for start_Date and delay_comments. This may not be the best values -- it depends on the business rules for your situation which row to use from this table.