What records should be created for the following patient. Note, I need to be able to report on an estimated delivery date by using delivery_date where it is available and the most recent est_delivery_date where the actual delivery date is not known.
| patient_id | encounter_id | pregnancy_episode_id | encounter_date | est_delivery_date | delivery_date | outcome |
|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1967-01-28 | 1967-09-01 | ||
| 1 | 2 | 1 | 1967-02-20 | 1967-10-15 | ||
| 1 | 3 | 1 | 1967-03-15 | 1967-10-01 | ||
| 1 | 4 | 1 | 1967-11-07 | TERM | ||
| 1 | 5 | 2 | 1970-01-05 | 1970-09-05 | ||
| 1 | 6 | 2 | 1970-03-20 | SAB | ||
| 1 | 7 | 3 | 1970-06-15 | 1971-01-15 | ||
| 1 | 8 | 3 | 1970-09-22 | SAB | ||
| 1 | 9 | 4 | 1973-07-01 | 1973-12-01 |
Bonus points: What if your data actually looks something like this (true story).
ENCOUNTER
| patient_id | encounter_date | est_delivery_date |
|---|---|---|
| 1 | 1967-01-28 | 1967-09-01 |
| 1 | 1967-02-20 | 1967-10-15 |
| 1 | 1967-03-15 | 1967-10-01 |
| 1 | 1970-01-05 | 1970-09-05 |
| 1 | 1970-06-15 | 1971-01-15 |
| 1 | 1973-07-01 | 1973-12-01 |
PREG
| patient_id | encounter_date | delivery_date | outcome |
|---|---|---|---|
| 1 | 1967-11-07 | 1967-11-10 | TERM |
| 1 | 1970-03-20 | 1970-03-15 | SAB |
| 1 | 1970-09-22 | 1973-06-20 | SAB |