I am trying to create new table (Table 2) based on an existing table (Table 1).
I have tried EARLIER, but do not know how to make it work if the patient is coming back?
Also, I tried to merge the two columns TypeOfVisit and Status (to something like Start---X, X---X, C---Closed etc..), but it didn’t help my progress either ;P.
Table 1, Contains a couple of visits to a physican
Patient | EventID | DateOfVisit | TypeOfVisit | Status |
A | 1 | 2018-01-01 | Start | X |
A | 2 | 2018-01-02 | X | X |
A | 3 | 2018-01-03 | X | Closed |
A | 4 | 2018-05-01 | Start | X |
A | 5 | 2018-06-01 | X | Closed |
B | 6 | 2018-01-01 | Start | Closed |
B | 7 | 2018-02-01 | Start | X |
Table 2
Patient | EpisodID | StartDate | EndDate |
A | A1 | 2018-01-01 | 2018-01-03 |
A | A2 | 2018-05-01 | 2018-06-01 |
B | B1 | 2018-01-01 | 2018-01-01 |
| B | B2 | 2018-02-01 | Ongoing |
For each patient;
StartDate: The date when column TypeOfVisit = ”Start” in Table 1
EndDate: The date when column Status = ”Closed” in Table 1
If a patient is reoccuring (i.e. gets another ”Start” in Table 1), a new episodID is to be created
/Ingrid
