IHSD
.IHS_2023
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IHS_2023.PR_POPULATE_IHS_DATA
Parameters
Name
Type
Mode
Definition
procedure pr_populate_IHS_data (program_start_date varchar2 := '2023-04-01') /*------------------------------------------------------ Project: Intern Health Study 2023 (Care Evolution Data) Description: Oracle procedure pr_populate_IHS_data Author: Siqing Hu (shu@med.umich.edu) Created: 2022-03-30 Updated: 2023-12-05 ------------------------------------------------------*/ -- use AUTHID CURRENT_USER to execute with the privileges and -- schema context of the calling user AUTHID CURRENT_USER AS --declare cursor c is with participants as ( select s.PARTICIPANTIDENTIFIER --,fn_guid_to_raw(s.GLOBALKEY) as GLOBALKEY --not in 2022 data ,s.EMAILADDRESS ,s.MIDDLENAME ,s.FIRSTNAME ,s.LASTNAME ,s.GENDER ,to_date(s.DATEOFBIRTH,'yyyy-mm-dd') as DATEOFBIRTH ,s.SECONDARYIDENTIFIER ,s.POSTALCODE ,TO_UTC_TIMESTAMP_TZ(s.ENROLLMENTDATE) as ENROLLMENTDATE ,s.EVENTDATES --json object, field changed from EVENTDATE to EVENTDATES (3/29/2022) ,s.CUSTOMFIELDS --json object --added on 7/1/2022: ,s.UTCOFFSET ,s.TIMEZONE ,s.PREFERREDLANGUAGE from stg_STUDYPARTICIPANTS s where length(s.PARTICIPANTIDENTIFIER) >= 13 ) select n.*, o.rowid old_row from participants n left join STUDYPARTICIPANTS o on n.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER order by case when o.rowid is not null then 1 else 2 end asc --old goes first --,n.PARTICIPANTIDENTIFIER asc ; begin -------------------------------------------------------- -- 1 - Data insertion for Table STUDYPARTICIPANTS -------------------------------------------------------- --updated on 7/1/2022: for rec in c loop if rec.old_row is not null then update STUDYPARTICIPANTS set EMAILADDRESS = rec.EMAILADDRESS ,MIDDLENAME = rec.MIDDLENAME ,FIRSTNAME = rec.FIRSTNAME ,LASTNAME = rec.LASTNAME ,GENDER = rec.GENDER ,DATEOFBIRTH = rec.DATEOFBIRTH ,SECONDARYIDENTIFIER = rec.SECONDARYIDENTIFIER ,POSTALCODE = rec.POSTALCODE ,ENROLLMENTDATE = rec.ENROLLMENTDATE ,EVENTDATES = rec.EVENTDATES ,CUSTOMFIELDS = rec.CUSTOMFIELDS ,UTCOFFSET = rec.UTCOFFSET ,TIMEZONE = rec.TIMEZONE ,PREFERREDLANGUAGE = rec.PREFERREDLANGUAGE where rowid = rec.old_row; else insert into STUDYPARTICIPANTS ( PARTICIPANTIDENTIFIER --,GLOBALKEY ,EMAILADDRESS ,MIDDLENAME ,FIRSTNAME ,LASTNAME ,GENDER ,DATEOFBIRTH ,SECONDARYIDENTIFIER ,POSTALCODE ,ENROLLMENTDATE ,EVENTDATES ,CUSTOMFIELDS ,UTCOFFSET ,TIMEZONE ,PREFERREDLANGUAGE ) VALUES ( rec.PARTICIPANTIDENTIFIER --,rec.GLOBALKEY ,rec.EMAILADDRESS ,rec.MIDDLENAME ,rec.FIRSTNAME ,rec.LASTNAME ,rec.GENDER ,rec.DATEOFBIRTH ,rec.SECONDARYIDENTIFIER ,rec.POSTALCODE ,rec.ENROLLMENTDATE ,rec.EVENTDATES ,rec.CUSTOMFIELDS ,rec.UTCOFFSET ,rec.TIMEZONE ,rec.PREFERREDLANGUAGE ); end if; end loop; commit; -------------------------------------------------------- -- 2 - Data insertion for Table SURVEYDEFINITIONS -------------------------------------------------------- --fully refreshed! EXECUTE IMMEDIATE 'truncate table SURVEYDEFINITIONS'; insert into SURVEYDEFINITIONS ( SURVEYDEFINITIONKEY ,SURVEYKEY ,SURVEYNAME ,VERSIONNUMBER ,INSERTEDDATE ,DEFINITION ) select fn_guid_to_raw(SURVEYDEFINITIONKEY) as SURVEYDEFINITIONKEY ,fn_guid_to_raw(SURVEYKEY) as SURVEYKEY ,SURVEYNAME ,VERSIONNUMBER ,TO_UTC_TIMESTAMP_TZ(INSERTEDDATE) as INSERTEDDATE ,DEFINITION from stg_SURVEYDEFINITIONS order by SURVEYNAME asc ,to_number(VERSIONNUMBER) desc ; commit; -------------------------------------------------------- -- 3 - Data insertion for Table SURVEYTASKS -------------------------------------------------------- delete from SURVEYTASKS where rowid in ( select o.rowid from stg_SURVEYTASKS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join SURVEYTASKS o on fn_guid_to_raw(s.SURVEYTASKKEY) = o.SURVEYTASKKEY where o.MODIFIEDDATE < TO_UTC_TIMESTAMP_TZ(s.MODIFIEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 --11/3/2021: modified to prevent function call error ) ; insert into SURVEYTASKS ( SURVEYTASKKEY ,PARTICIPANTIDENTIFIER ,SURVEYKEY ,SURVEYNAME ,STATUS ,DUEDATE ,INSERTEDDATE ,CREATEDBY ,MODIFIEDDATE ) select fn_guid_to_raw(s.SURVEYTASKKEY) as SURVEYTASKKEY ,s.PARTICIPANTIDENTIFIER ,fn_guid_to_raw(s.SURVEYKEY) as SURVEYKEY ,s.SURVEYNAME ,s.STATUS ,TO_UTC_TIMESTAMP_TZ(s.DUEDATE) as DUEDATE ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE ,s.CREATEDBY ,TO_UTC_TIMESTAMP_TZ(s.MODIFIEDDATE) as MODIFIEDDATE from stg_SURVEYTASKS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join SURVEYTASKS o on fn_guid_to_raw(s.SURVEYTASKKEY) = o.SURVEYTASKKEY where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; -------------------------------------------------------- -- 4 - Data insertion for Table SURVEYRESULTS -------------------------------------------------------- insert into SURVEYRESULTS ( SURVEYRESULTKEY ,SURVEYKEY ,SURVEYNAME ,SURVEYVERSION ,PARTICIPANTIDENTIFIER ,SURVEYTASKKEY ,TYPE ,STARTDATE ,ENDDATE ,DEVICEPLATFORM ,DEVICENAME ,DEVICEOSVERSION ,INSERTEDDATE ) select fn_guid_to_raw(s.SURVEYRESULTKEY) as SURVEYRESULTKEY ,fn_guid_to_raw(s.SURVEYKEY) as SURVEYKEY ,s.SURVEYNAME ,s.SURVEYVERSION ,s.PARTICIPANTIDENTIFIER ,fn_guid_to_raw(s.SURVEYTASKKEY) as SURVEYTASKKEY ,s.TYPE ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s.ENDDATE) as ENDDATE ,s.DEVICEPLATFORM ,s.DEVICENAME ,s.DEVICEOSVERSION ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_SURVEYRESULTS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join SURVEYTASKS f on fn_guid_to_raw(s.SURVEYTASKKEY) = f.SURVEYTASKKEY --SURVEYTASKKEY only partially populated in stg_SURVEYRESULTS left join SURVEYRESULTS o on fn_guid_to_raw(s.SURVEYRESULTKEY) = o.SURVEYRESULTKEY where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date --modified 3/30/2022 and o.rowid is null ; commit; -------------------------------------------------------- -- 5 - Data insertion for Table SURVEYSTEPRESULTS -------------------------------------------------------- insert into SURVEYSTEPRESULTS ( SURVEYSTEPRESULTKEY ,SURVEYRESULTKEY ,PARTICIPANTIDENTIFIER ,STEPIDENTIFIER ,STARTDATE ,ENDDATE ) select fn_guid_to_raw(s.SURVEYSTEPRESULTKEY) as SURVEYSTEPRESULTKEY ,fn_guid_to_raw(s.SURVEYRESULTKEY) as SURVEYRESULTKEY ,s.PARTICIPANTIDENTIFIER ,s.STEPIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s.ENDDATE) as ENDDATE from stg_SURVEYSTEPRESULTS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join SURVEYRESULTS f on fn_guid_to_raw(s.SURVEYRESULTKEY) = f.SURVEYRESULTKEY left join SURVEYSTEPRESULTS o on fn_guid_to_raw(s.SURVEYSTEPRESULTKEY) = o.SURVEYSTEPRESULTKEY where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date --modified 3/30/2022 and o.rowid is null ; commit; -------------------------------------------------------- -- 6 - Data insertion for Table SURVEYCONSENTSIGNATURERESULTS -------------------------------------------------------- insert into SURVEYCONSENTSIGNATURERESULTS ( SURVEYCONSENTSIGNATURERESULTKEY ,SURVEYSTEPRESULTKEY ,SURVEYRESULTKEY ,PARTICIPANTIDENTIFIER ,RESULTIDENTIFIER ,CONSENTED ,SIGNATUREIDENTIFIER ,SIGNATURETITLE ,SIGNATUREGIVENNAME ,SIGNATUREFAMILYNAME ,SIGNATUREDATE ,STARTDATE ,ENDDATE ) select fn_guid_to_raw(s.SURVEYCONSENTSIGNATURERESULTKEY) as SURVEYCONSENTSIGNATURERESULTKEY ,fn_guid_to_raw(s.SURVEYSTEPRESULTKEY) as SURVEYSTEPRESULTKEY ,fn_guid_to_raw(s.SURVEYRESULTKEY) as SURVEYRESULTKEY ,s.PARTICIPANTIDENTIFIER ,s.RESULTIDENTIFIER ,case when s.CONSENTED = 'True' then 'Y' end CONSENTED ,s.SIGNATUREIDENTIFIER ,s.SIGNATURETITLE ,s.SIGNATUREGIVENNAME ,s.SIGNATUREFAMILYNAME ,s.SIGNATUREDATE ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s.ENDDATE) as ENDDATE from stg_SURVEYCONSENTSIGNATURERESULTS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join SURVEYSTEPRESULTS f1 on fn_guid_to_raw(s.SURVEYSTEPRESULTKEY) = f1.SURVEYSTEPRESULTKEY join SURVEYRESULTS f2 on fn_guid_to_raw(s.SURVEYRESULTKEY) = f2.SURVEYRESULTKEY left join SURVEYCONSENTSIGNATURERESULTS o on fn_guid_to_raw(s.SURVEYCONSENTSIGNATURERESULTKEY) = o.SURVEYCONSENTSIGNATURERESULTKEY where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date --modified 3/30/2022 and o.rowid is null ; commit; -------------------------------------------------------- -- 7 - Data insertion for Table SURVEYQUESTIONRESULTS -------------------------------------------------------- insert into SURVEYQUESTIONRESULTS ( SURVEYQUESTIONRESULTKEY ,SURVEYSTEPRESULTKEY ,SURVEYRESULTKEY ,PARTICIPANTIDENTIFIER ,RESULTIDENTIFIER ,ANSWERS ,STARTDATE ,ENDDATE ) select fn_guid_to_raw(s.SURVEYQUESTIONRESULTKEY) as SURVEYQUESTIONRESULTKEY ,fn_guid_to_raw(s.SURVEYSTEPRESULTKEY) as SURVEYSTEPRESULTKEY ,fn_guid_to_raw(s.SURVEYRESULTKEY) as SURVEYRESULTKEY ,s.PARTICIPANTIDENTIFIER ,s.RESULTIDENTIFIER ,s.ANSWERS ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s.ENDDATE) as ENDDATE from stg_SURVEYQUESTIONRESULTS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join SURVEYSTEPRESULTS f1 on fn_guid_to_raw(s.SURVEYSTEPRESULTKEY) = f1.SURVEYSTEPRESULTKEY join SURVEYRESULTS f2 on fn_guid_to_raw(s.SURVEYRESULTKEY) = f2.SURVEYRESULTKEY left join SURVEYQUESTIONRESULTS o on fn_guid_to_raw(s.SURVEYQUESTIONRESULTKEY) = o.SURVEYQUESTIONRESULTKEY where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date --modified 3/30/2022 and o.rowid is null ; commit; -------------------------------------------------------- -- 8 - Data insertion for Table AUXILIARYDATA -------------------------------------------------------- --log to track the uploaded data files by the study team insert into AUXILIARYDATA ( AUXILIARYDATAKEY ,PARTICIPANTIDENTIFIER ,FILENAME ,UPLOADDATE ,UPLOADEDBY ,OBSERVATIONDATE ) select fn_guid_to_raw(s.AUXILIARYDATAKEY) as AUXILIARYDATAKEY ,s.PARTICIPANTIDENTIFIER ,s.FILENAME ,TO_UTC_TIMESTAMP_TZ(s.UPLOADDATE) as UPLOADDATE ,s.UPLOADEDBY ,TO_UTC_TIMESTAMP_TZ(s.OBSERVATIONDATE) as OBSERVATIONDATE from stg_AUXILIARYDATA s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join AUXILIARYDATA o on fn_guid_to_raw(s.AUXILIARYDATAKEY) = o.AUXILIARYDATAKEY where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; -------------------------------------------------------- -- 9 - Data insertion for Table FITBITACTIVITYLOGS -------------------------------------------------------- delete from FITBITACTIVITYLOGS where rowid in ( select o.rowid from stg_FITBITACTIVITYLOGS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join FITBITACTIVITYLOGS o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and TO_UTC_TIMESTAMP_TZ(s.STARTDATE) = o.STARTDATE and TO_UTC_TIMESTAMP_TZ(s.ENDDATE) = o.ENDDATE --11/3/2021: modified to enforce uniqueness and s.ACTIVITYTYPEID = o.ACTIVITYTYPEID where o.LASTMODIFIED < TO_UTC_TIMESTAMP_TZ(s.LASTMODIFIED) and length(s.PARTICIPANTIDENTIFIER) >= 13 --11/3/2021: modified to prevent function call error ) ; insert into FITBITACTIVITYLOGS ( PARTICIPANTIDENTIFIER ,STARTDATE --,STARTDATE_UTC ,ENDDATE ,ACTIVEDURATION ,ACTIVITYNAME ,ACTIVITYTYPEID ,AVERAGEHEARTRATE ,CALORIES ,DURATION ,LASTMODIFIED ,LOGTYPE ,MANUALVALUESPECIFIEDCALORIES ,MANUALVALUESPECIFIEDDISTANCE ,MANUALVALUESPECIFIEDSTEPS ,ORIGINALDURATION ,ORIGINALSTARTTIME ,STEPS ,HEARTRATEZONEOUTOFRANGEMAX ,HEARTRATEZONEOUTOFRANGEMIN ,HEARTRATEZONEOUTOFRANGEMINUTES ,HEARTRATEZONEFATBURNMAX ,HEARTRATEZONEFATBURNMIN ,HEARTRATEZONEFATBURNMINUTES ,HEARTRATEZONECARDIOMAX ,HEARTRATEZONECARDIOMIN ,HEARTRATEZONECARDIOMINUTES ,HEARTRATEZONEPEAKMAX ,HEARTRATEZONEPEAKMIN ,HEARTRATEZONEPEAKMINUTES ,SEDENTARYMINUTES ,LIGHTLYMINUTES ,FAIRLYMINUTES ,VERYMINUTES ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE --,TO_UTC_TIMESTAMP_TZ(s.STARTDATE_UTC) as STARTDATE_UTC ,TO_UTC_TIMESTAMP_TZ(s.ENDDATE) as ENDDATE ,s.ACTIVEDURATION ,s.ACTIVITYNAME ,s.ACTIVITYTYPEID ,s.AVERAGEHEARTRATE ,s.CALORIES ,s.DURATION ,TO_UTC_TIMESTAMP_TZ(s.LASTMODIFIED) as LASTMODIFIED ,s.LOGTYPE ,s.MANUALVALUESPECIFIEDCALORIES ,s.MANUALVALUESPECIFIEDDISTANCE ,s.MANUALVALUESPECIFIEDSTEPS ,s.ORIGINALDURATION ,TO_UTC_TIMESTAMP_TZ(s.ORIGINALSTARTTIME) as ORIGINALSTARTTIME ,s.STEPS ,s.HEARTRATEZONEOUTOFRANGEMAX ,s.HEARTRATEZONEOUTOFRANGEMIN ,s.HEARTRATEZONEOUTOFRANGEMINUTES ,s.HEARTRATEZONEFATBURNMAX ,s.HEARTRATEZONEFATBURNMIN ,s.HEARTRATEZONEFATBURNMINUTES ,s.HEARTRATEZONECARDIOMAX ,s.HEARTRATEZONECARDIOMIN ,s.HEARTRATEZONECARDIOMINUTES ,s.HEARTRATEZONEPEAKMAX ,s.HEARTRATEZONEPEAKMIN ,s.HEARTRATEZONEPEAKMINUTES ,s.SEDENTARYMINUTES ,s.LIGHTLYMINUTES ,s.FAIRLYMINUTES ,s.VERYMINUTES from stg_FITBITACTIVITYLOGS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join FITBITACTIVITYLOGS o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and TO_UTC_TIMESTAMP_TZ(s.STARTDATE) = o.STARTDATE and TO_UTC_TIMESTAMP_TZ(s.ENDDATE) = o.ENDDATE --11/3/2021: modified to enforce uniqueness and s.ACTIVITYTYPEID = o.ACTIVITYTYPEID --6/10/2021: modified to handle the duplicates logged at the same time where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date and o.rowid is null ; commit; -------------------------------------------------------- -- 10 - Data insertion for Table FITBITDAILYDATA -------------------------------------------------------- delete from FITBITDAILYDATA where rowid in ( select o.rowid from stg_FITBITDAILYDATA s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join FITBITDAILYDATA o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and to_date(substr(s."DATE",1,10), 'yyyy-mm-dd') = o.RECORD_DATE where o.MODIFIEDDATE < TO_UTC_TIMESTAMP_TZ(s.MODIFIEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 --11/3/2021: modified to prevent function call error ) ; insert into FITBITDAILYDATA ( PARTICIPANTIDENTIFIER ,RECORD_DATE ,ACTIVITYCALORIES ,BODYBMI ,BODYFAT ,BODYFATLOG ,BODYFATLOGSOURCE ,BODYWEIGHT ,BODYWEIGHTLOGBODYWEIGHT ,BODYWEIGHTLOGBMI ,BODYWEIGHTLOGFAT ,BODYWEIGHTLOGSOURCE ,CALORIES ,CALORIESBMR ,DISTANCE ,ELEVATION ,FLOORS ,FOODCALORIESIN ,HEARTRATEINTRADAYCOUNT ,HEARTRATEZONEOUTOFRANGECALORIESOUT ,HEARTRATEZONEOUTOFRANGEMAX ,HEARTRATEZONEOUTOFRANGEMIN ,HEARTRATEZONEOUTOFRANGEMINUTES ,HEARTRATEZONEFATBURNCALORIESOUT ,HEARTRATEZONEFATBURNMAX ,HEARTRATEZONEFATBURNMIN ,HEARTRATEZONEFATBURNMINUTES ,HEARTRATEZONECARDIOCALORIESOUT ,HEARTRATEZONECARDIOMAX ,HEARTRATEZONECARDIOMIN ,HEARTRATEZONECARDIOMINUTES ,HEARTRATEZONEPEAKCALORIESOUT ,HEARTRATEZONEPEAKMAX ,HEARTRATEZONEPEAKMIN ,HEARTRATEZONEPEAKMINUTES ,MINUTESFAIRLYACTIVE ,MINUTESLIGHTLYACTIVE ,MINUTESSEDENTARY ,MINUTESVERYACTIVE ,RESTINGHEARTRATE ,STEPS ,TRACKERACTIVITYCALORIES ,TRACKERCALORIES ,TRACKERDISTANCE ,TRACKERELEVATION ,TRACKERFLOORS ,TRACKERMINUTESFAIRLYACTIVE ,TRACKERMINUTESLIGHTLYACTIVE ,TRACKERMINUTESSEDENTARY ,TRACKERMINUTESVERYACTIVE ,TRACKERSTEPS ,WATER ,MODIFIEDDATE ,HEARTRATEINTRADAYMINUTECOUNT --new field added on 3/29/2022 --9/23/2022: add the below six fields ,BREATHINGRATE ,HRVDAILYRMSSD ,HRVDEEPRMSSD ,SPO2AVG ,SPO2MIN ,SPO2MAX ) select s.PARTICIPANTIDENTIFIER ,to_date(substr(s."DATE",1,10), 'yyyy-mm-dd') as RECORD_DATE ,s.ACTIVITYCALORIES ,s.BODYBMI ,s.BODYFAT ,s.BODYFATLOG ,s.BODYFATLOGSOURCE ,s.BODYWEIGHT ,s.BODYWEIGHTLOGBODYWEIGHT ,s.BODYWEIGHTLOGBMI ,s.BODYWEIGHTLOGFAT ,s.BODYWEIGHTLOGSOURCE ,s.CALORIES ,s.CALORIESBMR ,s.DISTANCE ,s.ELEVATION ,s.FLOORS ,s.FOODCALORIESIN ,s.HEARTRATEINTRADAYCOUNT ,s.HEARTRATEZONEOUTOFRANGECALORIESOUT ,s.HEARTRATEZONEOUTOFRANGEMAX ,s.HEARTRATEZONEOUTOFRANGEMIN ,s.HEARTRATEZONEOUTOFRANGEMINUTES ,s.HEARTRATEZONEFATBURNCALORIESOUT ,s.HEARTRATEZONEFATBURNMAX ,s.HEARTRATEZONEFATBURNMIN ,s.HEARTRATEZONEFATBURNMINUTES ,s.HEARTRATEZONECARDIOCALORIESOUT ,s.HEARTRATEZONECARDIOMAX ,s.HEARTRATEZONECARDIOMIN ,s.HEARTRATEZONECARDIOMINUTES ,s.HEARTRATEZONEPEAKCALORIESOUT ,s.HEARTRATEZONEPEAKMAX ,s.HEARTRATEZONEPEAKMIN ,s.HEARTRATEZONEPEAKMINUTES ,s.MINUTESFAIRLYACTIVE ,s.MINUTESLIGHTLYACTIVE ,s.MINUTESSEDENTARY ,s.MINUTESVERYACTIVE ,s.RESTINGHEARTRATE ,s.STEPS ,s.TRACKERACTIVITYCALORIES ,s.TRACKERCALORIES ,s.TRACKERDISTANCE ,s.TRACKERELEVATION ,s.TRACKERFLOORS ,s.TRACKERMINUTESFAIRLYACTIVE ,s.TRACKERMINUTESLIGHTLYACTIVE ,s.TRACKERMINUTESSEDENTARY ,s.TRACKERMINUTESVERYACTIVE ,s.TRACKERSTEPS ,s.WATER ,TO_UTC_TIMESTAMP_TZ(s.MODIFIEDDATE) as MODIFIEDDATE ,s.HEARTRATEINTRADAYMINUTECOUNT --9/23/2022: add the below six fields ,s.BREATHINGRATE ,s.HRVDAILYRMSSD ,s.HRVDEEPRMSSD ,s.SPO2AVG ,s.SPO2MIN ,s.SPO2MAX from stg_FITBITDAILYDATA s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join FITBITDAILYDATA o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and to_date(substr(s."DATE",1,10), 'yyyy-mm-dd') = o.RECORD_DATE where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s."DATE",1,10) >= program_start_date and o.rowid is null ; commit; -------------------------------------------------------- -- 11 - Data insertion for Table FITBITDEVICES -------------------------------------------------------- insert into FITBITDEVICES ( PARTICIPANTIDENTIFIER ,RECORD_DATE --,RECORD_DATE_UTC ,DEVICE ,BATTERY ,TYPE ) select distinct --2023-09-22: modified to remove duplicated record and fix ORA-00001 (unique constraint (IHS_2023.FITBITDEVICES_PK) violated) s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."DATE") as RECORD_DATE --,TO_UTC_TIMESTAMP_TZ(RECORD_DATE_UTC) as RECORD_DATE_UTC ,s.DEVICE ,s.BATTERY ,s.TYPE from stg_FITBITDEVICES s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join FITBITDEVICES o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and SYS_EXTRACT_UTC(TO_UTC_TIMESTAMP_TZ(s."DATE")) = o.RECORD_DATE_UTC where length(s.PARTICIPANTIDENTIFIER) >= 13 and s."DATE" is not null --2022-06-09: added to remove NULL date record and o.rowid is null ; commit; -------------------------------------------------------- -- 12 - Data insertion for Table FITBITRESTINGHEARTRATES -------------------------------------------------------- insert into FITBITRESTINGHEARTRATES ( PARTICIPANTIDENTIFIER ,RECORD_DATE ,RESTINGHEARTRATE ) select s.PARTICIPANTIDENTIFIER ,to_date(substr(s."DATE",1,10), 'yyyy-mm-dd') as RECORD_DATE ,s.RESTINGHEARTRATE from stg_FITBITRESTINGHEARTRATES s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join FITBITRESTINGHEARTRATES o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and to_date(substr(s."DATE",1,10), 'yyyy-mm-dd') = o.RECORD_DATE where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s."DATE",1,10) >= program_start_date and o.rowid is null ; commit; -------------------------------------------------------- -- 13 - Data insertion for Table FITBITSLEEPLOGDETAILS -------------------------------------------------------- insert into FITBITSLEEPLOGDETAILS ( PARTICIPANTIDENTIFIER ,SLEEPLOGDATE ,TYPE ,STARTDATE --,STARTDATE_UTC ,ENDDATE ,VALUE ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.SLEEPLOGDATE) as SLEEPLOGDATE ,s.TYPE ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE --,TO_UTC_TIMESTAMP_TZ(s.STARTDATE_UTC) as STARTDATE_UTC ,TO_UTC_TIMESTAMP_TZ(s.ENDDATE) as ENDDATE ,s.VALUE from stg_FITBITSLEEPLOGDETAILS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join FITBITSLEEPLOGDETAILS o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and SYS_EXTRACT_UTC(TO_UTC_TIMESTAMP_TZ(s.STARTDATE)) = o.STARTDATE_UTC and s.TYPE = o.TYPE where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date and o.rowid is null ; commit; -------------------------------------------------------- -- 14 - Data insertion for Table FITBITSLEEPLOGS -------------------------------------------------------- insert into FITBITSLEEPLOGS ( PARTICIPANTIDENTIFIER ,STARTDATE --,STARTDATE_UTC ,ENDDATE ,DURATION ,EFFICIENCY ,INFOCODE ,MINUTESAFTERWAKEUP ,MINUTESASLEEP ,MINUTESAWAKE ,MINUTESTOFALLASLEEP ,TIMEINBED ,TYPE ,SLEEPLEVELAWAKE ,SLEEPLEVELASLEEP ,SLEEPLEVELDEEP ,SLEEPLEVELLIGHT ,SLEEPLEVELREM ,SLEEPLEVELRESTLESS ,SLEEPLEVELWAKE ) select s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE --,TO_UTC_TIMESTAMP_TZ(s.STARTDATE_UTC) as STARTDATE_UTC ,TO_UTC_TIMESTAMP_TZ(s.ENDDATE) as ENDDATE ,s.DURATION ,s.EFFICIENCY ,s.INFOCODE ,s.MINUTESAFTERWAKEUP ,s.MINUTESASLEEP ,s.MINUTESAWAKE ,s.MINUTESTOFALLASLEEP ,s.TIMEINBED ,s.TYPE ,s.SLEEPLEVELAWAKE ,s.SLEEPLEVELASLEEP ,s.SLEEPLEVELDEEP ,s.SLEEPLEVELLIGHT ,s.SLEEPLEVELREM ,s.SLEEPLEVELRESTLESS ,s.SLEEPLEVELWAKE from stg_FITBITSLEEPLOGS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join FITBITSLEEPLOGS o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and SYS_EXTRACT_UTC(TO_UTC_TIMESTAMP_TZ(s.STARTDATE)) = o.STARTDATE_UTC where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date and o.rowid is null ; commit; -------------------------------------------------------- -- 15 - Data insertion for Table HEALTHKITACTIVITYSUMMARIES -------------------------------------------------------- insert into HEALTHKITACTIVITYSUMMARIES ( HEALTHKITACTIVITYSUMMARYKEY ,PARTICIPANTIDENTIFIER ,STARTDATE ,ENDDATE ,ACTIVEENERGYBURNED ,ACTIVEENERGYBURNEDGOAL ,APPLEEXERCISETIME ,APPLEEXERCISETIMEGOAL ,APPLESTANDHOURS ,APPLESTANDHOURSGOAL ,INSERTEDDATE ) select fn_guid_to_raw(s.HEALTHKITACTIVITYSUMMARYKEY) as HEALTHKITACTIVITYSUMMARYKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s.ENDDATE) as ENDDATE ,s.ACTIVEENERGYBURNED ,s.ACTIVEENERGYBURNEDGOAL ,s.APPLEEXERCISETIME ,s.APPLEEXERCISETIMEGOAL ,s.APPLESTANDHOURS ,s.APPLESTANDHOURSGOAL ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_HEALTHKITACTIVITYSUMMARIES s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITACTIVITYSUMMARIES o on fn_guid_to_raw(s.HEALTHKITACTIVITYSUMMARYKEY) = o.HEALTHKITACTIVITYSUMMARYKEY where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date and o.rowid is null ; commit; -------------------------------------------------------- -- 16 - Data insertion for Table PROJECTDEVICEDATA -------------------------------------------------------- --new data added on 2023-12-05: insert into PROJECTDEVICEDATA ( DEVICEDATAKEY ,IDENTIFIER ,DEVICEDATACONTEXTID ,PARTICIPANTID ,PARTICIPANTIDENTIFIER ,TYPE ,VALUE ,UNITS ,PROPERTIES ,STARTDATE ,OBSERVATIONDATE ,INSERTEDDATE ,MODIFIEDDATE ) select s.DEVICEDATAKEY ,s.IDENTIFIER ,s.DEVICEDATACONTEXTID ,s.PARTICIPANTID ,s.PARTICIPANTIDENTIFIER ,s.TYPE ,s.VALUE ,s.UNITS ,s.PROPERTIES ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s.OBSERVATIONDATE) as OBSERVATIONDATE ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE ,TO_UTC_TIMESTAMP_TZ(s.MODIFIEDDATE) as MODIFIEDDATE from stg_PROJECTDEVICEDATA s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER --left join PROJECTDEVICEDATA o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER -- and TO_UTC_TIMESTAMP_TZ(s.OBSERVATIONDATE) = o.OBSERVATIONDATE -- and TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) = o.INSERTEDDATE -- and TO_UTC_TIMESTAMP_TZ(s.MODIFIEDDATE) = o.MODIFIEDDATE where length(s.PARTICIPANTIDENTIFIER) >= 13 --and substr(s.STARTDATE,1,10) >= program_start_date --and o.rowid is null ; commit; /* --updated on 4/5/2023: no longer in the CareEv data export -------------------------------------------------------- -- 18 - Data insertion for Table NOTIFICATIONS -------------------------------------------------------- --changed again on 3/30/2022: insert into NOTIFICATIONS ( ID ,PARTICIPANTIDENTIFIER ,NOTIFY_DATE ,CONTENTVERSION ,IDENTIFIER ,NOTIFICATIONBODY --,NOTIFICATIONRECIPIENT --not in the 3/30/2022 data file --,NOTIFICATIONSUBJECT --not in the 3/30/2022 data file ,NOTIFICATIONTITLE ,NOTIFICATIONTYPE ,SCOPE ,STATUSCODE ) select fn_guid_to_raw(s.ID) as ID ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s."DATE") as NOTIFY_DATE ,s.CONTENTVERSION ,s.IDENTIFIER ,s.NOTIFICATIONBODY --,s.NOTIFICATIONRECIPIENT --not in the 3/30/2022 data file --,s.NOTIFICATIONSUBJECT --not in the 3/30/2022 data file ,s.NOTIFICATIONTITLE ,s.NOTIFICATIONTYPE ,fn_guid_to_raw(s.SCOPE) as SCOPE ,s.STATUSCODE from stg_NOTIFICATIONS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join NOTIFICATIONS o on fn_guid_to_raw(s.ID) = o.ID where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; */ /* -------------------------------------------------------- -- 0 - Data insertion for Table SURVEYDICTIONARY -------------------------------------------------------- --added for IHS 2023: --fully refreshed! EXECUTE IMMEDIATE 'truncate table SURVEYDICTIONARY'; insert into SURVEYDICTIONARY ( SURVEYNAME ,SURVEYKEY ,SURVEYVERSION ,STEPIDENTIFIER ,RESULTIDENTIFIER ,QUESTIONTEXT ,ANSWERFORMAT ,ANSWERCHOICES ) select SURVEYNAME ,fn_guid_to_raw(SURVEYKEY) as SURVEYKEY ,SURVEYVERSION ,STEPIDENTIFIER ,RESULTIDENTIFIER ,QUESTIONTEXT ,ANSWERFORMAT ,ANSWERCHOICES from stg_SurveyDictionary ; commit; */ end;