Updating based on date range of another field 1

[briangriffin]

These requirements always leave me grasping for the most efficient approach. I have a table of hospital admissions and readmissions, each record contains an patient number that is unique to the patient and consistent across all admissions. Some visits are flagged in the database as readmissions:

ReadmissionDateTime         PatientAccount            AdmissionDateTime        IsReadmission
1/1/2015 13:00                999                     1/1/2015 13:00                 Y
1/15/2015 13:00               999                     1/15/2015 13:00                Y
null                          999                     1/10/2015 13:00                N
null                          999                     1/12/2015 13:00                N
null                          999                     1/16/2015 13:00                N

I need to update this table by filling in all the null ReadmissionDateTime fields - these are records with N as the IsReadmission value - with the AdmissionDateTime of the Redmission (IsReadmission value = Y) of the visit that is closest to but prior than the date of the non-readmission visit.

That is hard to decipher, so in the example data the first non-readmission visit occurred on 1/10 - that one needs to be updated with a value of 1/1 since the other readmission occurred after that date. The next visit on 1/12 would also be updated with 1/1.

The third non-readmission would be updated with a value of 1/15, since although there are two readmission visits prior to this visit, we want the one that occurred most recently.

I could probably bludgeon this into working, but I'd be most interested and appreciative if someone pointed out the 'correct' approach. Thanks.

 

[TamarGranor]

Basically, you need a self-join. In this case, you can do it in the field list.

UPDATE YourTable
   SET ReadmissionDate = (
         SELECT MAX(YT2.AdmissionDateTime) ReadmitDateTime
            FROM YourTable YT2
            WHERE YourTable.PatientAccount = YT2.PatientAccount
              AND YT2.IsReadmission = 'Y'
              AND YT2.AdmissionDateTime < YourTable.AdmissionDateTime) 
   WHERE ReadmissionDate IS NULL

Tamar

 

[briangriffin]

Thanks, understand conceptually but still getting some issues that I think are unrelated. Two stars (one virtual).