Update table row by row

[dodgychris]

Hi,

I have a problem with a table, I need to compare multiple records and calculate a figure based on where specific fields match -

The table looks more or less as follows:

Patient_ID Admission Number Admit_date Discharge_date
5055 1 01/07/2002 05/07/2002
5055 2 09/07/2002 23/07/2002
3033 1 31/03/2002 null

What I need to do is - where the patient_ID's match and the admission number is incremented by 1, I need to know the number of days between the 1st Discharge date and the readmission date. (the later admit_date)

I have no clue...

Any ideas (apart from looking for a new job) would be greatly appreciated.

Thanks in advance,

Chris


Dodgy Chris
-----------------------------------

confucious say : better to save a mans life than to build 7 storey pagoda

 

[JBalcerzak]

I would select all records for that patient and order by admission number. Then just iterate through the table keeping track of the dates, if the admission number changes - then calculate the new date from the date of the last record you saw in the table.

Hope that helps.