Compare date fields in SQL server

Question

I've a flat file that I cleaned the data out using SSIS, the output looks like that :

MEDICAL    ADMIT     PATIENT     PATIENT         DATE OF    DX 
REC NO     DATE      NUMBER        NAME        DISCHARGE   Code     DRG #

123613    02/16/09  12413209  MORIBALDI ,GEMMA  02/19/09   428.20     988    
130897    01/23/09  12407193  TINLEY ,PATRICIA  01/23/09   535.10     392    
139367    02/27/09  36262509  THARPE ,GLORIA    03/05/09   562.10     392 

141954    02/25/09  72779499  SHUMATE ,VALERIA  02/25/09   112.84     370   
141954    03/07/09  36271732  SHUMATE ,VALERIA  03/10/09   493.92     203  

145299    01/21/09  12406294  BAUGH ,MARIA      01/21/09   366.17     117  

and the report (final results) attached in the screen shot from the final excel report.

so what's happening is IF the same name or same account number is duplicate, that means the patient has entered the hospital again and needs to be included in the report. ![alt text][1]

what I need to do is...

Eliminate any rows that is NOT duplicate (not everybody in this file has been admitted again) and compare the dates to get the ReAdmitdate and ReDischargedate

I dumped the data into a SQL table and trying to compare the dates to figure out "ReAdmitdate" and "ReDischargedate"

any help is appreciated.

Answer 1

Is there a unique identifier for the patients? I'm assuming patient_number is the unique identifier for each patient, and medical_rec_no is incremental.

This query works by joining the patient records to records from the same patient that have a later admit date.

SELECT p1.patient_number, p1.admit_date,
  p2.admit_date as readmit_date, p2.discharge_date AS redischarge_date
FROM patient p1
  INNER JOIN patient p2
    ON p1.patient_number = p2.patient_number AND
      p2.medical_rec_no <> p1.medical_rec_no
  LEFT OUTER JOIN patient p3
    ON p3.patient_number = p1.patient_number AND
      p3.medical_rec_no <> p1.medical_rec_no AND
      p3.admit_date < p2.admit_date
WHERE p3.patient_number IS NULL
ORDER BY p1.patient_number, p1.admit_date, p1.medical_rec_no