Abraham Olatubosun

Abraham Olatubosun

  • NA
  • 471
  • 113.5k

How to remove Duplicate rows retain latest LastDrugPickup

Apr 19 2017 8:43 PM
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
  1. SELECT Distinct a.Ptn_Pk, a.PatientEnrollmentID AS PatientID,   
  2.  CONVERT(datetime, a.ARTStartDate, 103) AS ARTStartDate, CONVERT(datetime, c.DispensedByDate, 103) AS LastDrugPickup  
  3.  ,b.Duration,b.RegimenType, c.RegimenLine FROM dbo.VW_PatientDetail AS a left JOIN   
  4.  dbo.VW_PatientPharmacy AS b ON a.Ptn_Pk = b.Ptn_pk left JOIN   
  5.  dbo.ord_PatientPharmacyOrder AS c ON  b.ptn_pk = c.ptn_pk                        
  6.  WHERE a.ARTStartDate IS NOT NULL And b.RegimenType is not NULL    
  7.  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 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Answers (8)