How to remove Duplicate rows retain latest LastDrugPickup

Abraham Olatubosun

9y1.2kSQL

Dear Code masters,

I believe this mail meet you all in good health, i am writting an SQL script that will generate all patient and their most current LastDrugPickupDate. my script is as below

SELECT Distinct a.Ptn_Pk, a.PatientEnrollmentID AS PatientID,
CONVERT(datetime, a.ARTStartDate, 103) AS ARTStartDate, CONVERT(datetime, c.DispensedByDate, 103) AS LastDrugPickup
,b.Duration,b.RegimenType, c.RegimenLine FROM dbo.VW_PatientDetail AS a left JOIN
dbo.VW_PatientPharmacy AS b ON a.Ptn_Pk = b.Ptn_pk left JOIN
dbo.ord_PatientPharmacyOrder AS c ON b.ptn_pk = c.ptn_pk
WHERE a.ARTStartDate IS NOT NULL And b.RegimenType is not NULL
ORDER BY c.DispensedByDate desc

the result is show below with alot of duplicate rows:

Please how can i remove the duplicate rows but retaining the most current LastDrugPickDate looking at the yellow shaded records.

thank you

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp9y ago
Accepted answer
Hi,

You may need to use rank() function. Please try below query -

SELECT * from(
SELECT Distinct a.Ptn_Pk, a.PatientEnrollmentID AS PatientID,
CONVERT(datetime, a.ARTStartDate, 103) AS ARTStartDate, CONVERT(datetime, c.DispensedByDate, 103) AS LastDrugPickup
,b.Duration,b.RegimenType, c.RegimenLine,
rank() over (partition by a.Ptn_Pk ORDER BY CONVERT(datetime, c.DispensedByDate, 103) DESC) AS Ranking
FROM dbo.VW_PatientDetail AS a left JOIN
dbo.VW_PatientPharmacy AS b ON a.Ptn_Pk = b.Ptn_pk left JOIN
dbo.ord_PatientPharmacyOrder AS c ON b.ptn_pk = c.ptn_pk
WHERE a.ARTStartDate IS NOT NULL And b.RegimenType is not NULL
ORDER BY c.DispensedByDate desc
) e WHERE e.ranking=1
Ramesh Barik9y ago
;
WITH QResult
AS (
SELECT ROW_NUMBER() OVER (
PARTITION BY a.Ptn_Pk
,a.PatientEnrollmentID ORDER BY c.DispensedByDate ASC
) AS Row
,a.Ptn_Pk
,a.PatientEnrollmentID AS PatientID
,CONVERT(DATETIME, a.ARTStartDate, 103) AS ARTStartDate
,CONVERT(DATETIME, c.DispensedByDate, 103) AS LastDrugPickup
,b.Duration
,b.RegimenType
,c.RegimenLine
FROM dbo.VW_PatientDetail AS a
LEFT JOIN dbo.VW_PatientPharmacy AS b ON a.Ptn_Pk = b.Ptn_pk
LEFT JOIN dbo.ord_PatientPharmacyOrder AS c ON b.ptn_pk = c.ptn_pk
WHERE a.ARTStartDate IS NOT NULL
AND b.RegimenType IS NOT NULL
)
DELETE P
FROM dbo.VW_PatientDetail P
INNER JOIN QResult ON P.Ptn_Pk = QResult.Ptn_Pk
AND P.PatientEnrollmentID = QResult.PatientID
where QResult.Row > 1;

Try this.
Rafnas T P9y ago
I think the result is correct because records are not duplicated,as you can see, each reacords are for each RegimenType
Rafnas T P9y ago
try giving
grouup by b.RegimenType before order by
Abraham Olatubosun9y ago
Many thanks Rafnas T P i remove the RegimenType as you instructed and it works but the database is for patient's on Drug and the RegimenType tells the Clinician what drug the patient is currently taking, so, it is very important for the RegimenType to be on the list out.

please kindly advice me.

thank you
Rafnas T P9y ago
if not mandatory RegimenType in select query then please remove it, you will be getting without duplicate values, here the RegimenType are different thats why you are getting duplicate values
Abraham Olatubosun9y ago
Many thanks Joseph Thomas, but that didn't work
Joseph Thomas9y ago
Try group by a.Ptn_Pk at the end..