Cloyd, the description is indeed confusing.
I'll state some assumptions and work on them. Please, shall you consider this worth, comment out and I'll keep editing the answer until we get 'there'. Deal?
Let's go:
the update
statement has as condition facility_id='X'
the sub-query at the assignment has no condition at all (except pat_seq
)
therefore, I'm assuming and 'forecasting' that:
pat_seq
is key for identification of a patient
both admit_dt
and discharge_dt
are of type date/time, not just date
the facility_id
column at patient
table keeps track of the several facilities within which the patient has been sent during it's (sad?) stay
for any patient, for each facility where it's been sent to, there has to be a row keeping track of date/time of admission and discharging, like:
pat_seq: 0015, facility_id: AABB, admit_dt: 15:35, discharge_dt:
16:45
pat_seq: 0015, facility_id: CCA1, admit_dt:
16:45, discharge_dt:
17:10
pat_seq: 0015, facility_id: AAHD, admit_dt:
17:10, discharge_dt: NULL
just the admitances are registered, hence for any new admitance registered there has to be an update action upon the imediate previous entry for which the discharge time will be assumed as the admitance time within the next facility
bullseye?
Well, for this scenario, you shall do, assuming a batch, scheduled task:
UPDATE patient
SET discharge_dt = (
/*
For every entry past this, retrieve the least admit_dt, which shall identify the moment the pat_seq moved into the next facility
*/
SELECT MIN(admit_dt)
FROM patient pat_next_admit
WHERE pat_next_admit.pat_seq = patient.pat_seq
AND pat_next_admit.admit_dt > patient.admit_dt
)
WHERE discharge_dt IS NULL /* No need to process rows already processed! */