The current version does not add records to the patient_dimension and therefore the demographic breakdown query returns no results. So we ETLed demographic information and then directly ran an insert query. At this point we are only adding age. Consider automating this. Here is the SQL statement for postgreSQL:
WITH cteRowNumber AS ( SELECT patient_num , nval_num , (date '2023-04-01' - nval_num * interval '1 year') as birthdate, ROW_NUMBER() OVER(PARTITION BY patient_num ORDER BY start_date DESC) AS RowNum FROM observation_fact f WHERE f.concept_cd = 'DEM_AGE' ) INSERT INTO patient_dimension (patient_num, age_in_years_num, patient_dimension.birth_date) SELECT patient_num , nval_num, bithdate FROM cteRowNumber -- into patient_dimension WHERE RowNum = 1
The current version does not add records to the patient_dimension and therefore the demographic breakdown query returns no results. So we ETLed demographic information and then directly ran an insert query. At this point we are only adding age. Consider automating this. Here is the SQL statement for postgreSQL:
WITH cteRowNumber AS ( SELECT patient_num , nval_num , (date '2023-04-01' - nval_num * interval '1 year') as birthdate, ROW_NUMBER() OVER(PARTITION BY patient_num ORDER BY start_date DESC) AS RowNum FROM observation_fact f WHERE f.concept_cd = 'DEM_AGE' ) INSERT INTO patient_dimension (patient_num, age_in_years_num, patient_dimension.birth_date) SELECT patient_num , nval_num, bithdate FROM cteRowNumber -- into patient_dimension WHERE RowNum = 1