Dear All
I'd be very grateful for a steer on the conundrum below. My table logs episodes of care in particular health services, so each record has (amongst other things):
- a patientID (denoting the unique person),
- a sequenceID (denoting the uninterrupted sequence of episodes of care for this person to which this record belongs, if there's a gap between episodes then a new sequence starts)
- a previousepisodeID (the ID of the record within this table for the immediately preceding episode of care for this person in this particular sequence of episodes, so for an isolated episode or a first episode within any sequence this is null)
What I would like to do is write a query to populate a new field in the table which denotes which episode within a given sequence this record is. I.e. for a first or isolated episode it returns 1, for a second episode it returns 2, for a fourteenth episodes it returns 14. Is it possible to calculate this on the basis of the fields provided above?
Thanks in advance!
[Edit for clarification] - the sequenceID number is always unique to a particular patient, you wouldn't have two patients with the same sequenceID number, so I suppose actually the patientID field is superfluous - since one can establish it from the sequenceID field.
I'd be very grateful for a steer on the conundrum below. My table logs episodes of care in particular health services, so each record has (amongst other things):
- a patientID (denoting the unique person),
- a sequenceID (denoting the uninterrupted sequence of episodes of care for this person to which this record belongs, if there's a gap between episodes then a new sequence starts)
- a previousepisodeID (the ID of the record within this table for the immediately preceding episode of care for this person in this particular sequence of episodes, so for an isolated episode or a first episode within any sequence this is null)
What I would like to do is write a query to populate a new field in the table which denotes which episode within a given sequence this record is. I.e. for a first or isolated episode it returns 1, for a second episode it returns 2, for a fourteenth episodes it returns 14. Is it possible to calculate this on the basis of the fields provided above?
Thanks in advance!
[Edit for clarification] - the sequenceID number is always unique to a particular patient, you wouldn't have two patients with the same sequenceID number, so I suppose actually the patientID field is superfluous - since one can establish it from the sequenceID field.
Last edited:
