How to transform m to n relations and time series to star schema data

[Anonymous]

Hello clever people

I have a problem with transforming time series data into a star schema. What is the best solution? The fact table will consist of master data on PatientID. I want to be able to - on any given date to tell where the patient is hospitalized and which drug(s) they were prescripted.

My test data looks like this:

Table 1: Hospitalization					Table 2: PrescriptionDrug
PatientID	HospitalCity	HospitalizationStartDate	HospitalizationEndDate		PatientID	PrescriptionDrug	PrescriptionStartDate	PrescriptionEndDate
1	Copenhagen	12-aug-17	15-aug-17		1	Vicodin	01-feb-17	15-sep-17
1	Stockholm	27-sep-17	14-dec-17		1	Levoxyl	10-maj-17	31-dec-99
1	Berlin	09-jan-18	16-feb-18		2	Lipitor	10-okt-16	12-aug-19
2	Stockholm	22-apr-18	23-apr-18		2	Vicodin	10-okt-16	12-aug-19
2	Berlin	31-aug-19	14-sep-19		2	Amoxil	10-okt-16	25-nov-18
3	Copenhagen	02-apr-18	16-maj-18		2	Delasone	03-apr-17	15-sep-17
3	Oslo	05-nov-18	19-dec-18		2	Zestril	19-aug-19	31-dec-99
3	Berlin	03-sep-19	09-sep-19		3	Amoxil	03-sep-19	20-okt-19

[sturlaws · Accepted solution]

Hi @Anonymous 

here is how I would have done it: sample

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.