IHSD
.IHS_2023
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IHS_2023.PR_POPULATE_IHS_HEALTHKITSAMPLES_DATA
Parameters
Name
Type
Mode
Definition
procedure pr_populate_IHS_HEALTHKITSAMPLES_data (program_start_date varchar2 := '2023-04-01') -- use AUTHID CURRENT_USER to execute with the privileges and -- schema context of the calling user AUTHID CURRENT_USER AS begin -------------------------------------------------------- -- 17-1 - Data insertion for Table HEALTHKITSAMPLES_APPLEEXERCISETIME -------------------------------------------------------- --updated on 4/18/2022 to handle ORA-00001: unique constraint vaiolation on (IHS_2022.HEALTHKITSAMPLES_APPLEEXERCISETIME_PK) --(duplicated records inserted on different time) insert into HEALTHKITSAMPLES_APPLEEXERCISETIME ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,STARTDATE ,RECORD_DATE ,VALUE ,UNITS ,SOURCEIDENTIFIER ,SOURCENAME ,SOURCEVERSION ,SOURCEOPERATINGSYSTEMVERSION ,SOURCEPRODUCTTYPE ,DEVICENAME ,DEVICEMODEL ,DEVICEMANUFACTURER ,DEVICEHARDWAREVERSION ,DEVICESOFTWAREVERSION ,DEVICEFIRMWAREVERSION ,DEVICELOCALIDENTIFIER ,DEVICEFDAIDENTIFIER ,METADATA ,INSERTEDDATE ) with ranked_recs as ( select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s."DATE") as RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE ,ROW_NUMBER() OVER (PARTITION BY s.HEALTHKITSAMPLEKEY ORDER BY s.INSERTEDDATE desc) AS rn from stg_HEALTHKITSAMPLES_APPLEEXERCISETIME s where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date ) select s.HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,s.STARTDATE ,s.RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,s.INSERTEDDATE from ranked_recs s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_APPLEEXERCISETIME o on s.HEALTHKITSAMPLEKEY = o.HEALTHKITSAMPLEKEY where rn=1 and o.rowid is null ; commit; -------------------------------------------------------- -- 17-2 - Data insertion for Table HEALTHKITSAMPLES_HEARTRATE -------------------------------------------------------- --updated on 4/18/2022 to prevent ORA-00001: unique constraint vaiolation on (IHS_2022.HEALTHKITSAMPLES_HEARTRATE_PK) --(duplicated records could be inserted on different time) insert into HEALTHKITSAMPLES_HEARTRATE ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,STARTDATE ,RECORD_DATE ,VALUE ,UNITS ,SOURCEIDENTIFIER ,SOURCENAME ,SOURCEVERSION ,SOURCEOPERATINGSYSTEMVERSION ,SOURCEPRODUCTTYPE ,DEVICENAME ,DEVICEMODEL ,DEVICEMANUFACTURER ,DEVICEHARDWAREVERSION ,DEVICESOFTWAREVERSION ,DEVICEFIRMWAREVERSION ,DEVICELOCALIDENTIFIER ,DEVICEFDAIDENTIFIER ,METADATA ,INSERTEDDATE ) with ranked_recs as ( select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s."DATE") as RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE ,ROW_NUMBER() OVER (PARTITION BY s.HEALTHKITSAMPLEKEY ORDER BY s.INSERTEDDATE desc) AS rn from stg_HEALTHKITSAMPLES_HEARTRATE s where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date ) select s.HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,s.STARTDATE ,s.RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,s.INSERTEDDATE from ranked_recs s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_HEARTRATE o on s.HEALTHKITSAMPLEKEY = o.HEALTHKITSAMPLEKEY where rn=1 and o.rowid is null ; commit; -------------------------------------------------------- -- 17-3 - Data insertion for Table HEALTHKITSAMPLES_HEARTRATEVARIABILITY -------------------------------------------------------- --updated on 4/18/2022 to prevent ORA-00001: unique constraint vaiolation on (IHS_2022.HEALTHKITSAMPLES_HEARTRATEVARIABILITY_PK) --(duplicated records could be inserted on different time) insert into HEALTHKITSAMPLES_HEARTRATEVARIABILITY ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,STARTDATE ,RECORD_DATE ,VALUE ,UNITS ,SOURCEIDENTIFIER ,SOURCENAME ,SOURCEVERSION ,SOURCEOPERATINGSYSTEMVERSION ,SOURCEPRODUCTTYPE ,DEVICENAME ,DEVICEMODEL ,DEVICEMANUFACTURER ,DEVICEHARDWAREVERSION ,DEVICESOFTWAREVERSION ,DEVICEFIRMWAREVERSION ,DEVICELOCALIDENTIFIER ,DEVICEFDAIDENTIFIER ,METADATA ,INSERTEDDATE ) with ranked_recs as ( select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s."DATE") as RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE ,ROW_NUMBER() OVER (PARTITION BY s.HEALTHKITSAMPLEKEY ORDER BY s.INSERTEDDATE desc) AS rn from stg_HEALTHKITSAMPLES_HEARTRATEVARIABILITY s where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date ) select s.HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,s.STARTDATE ,s.RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,s.INSERTEDDATE from ranked_recs s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_HEARTRATEVARIABILITY o on s.HEALTHKITSAMPLEKEY = o.HEALTHKITSAMPLEKEY where rn=1 and o.rowid is null ; commit; -------------------------------------------------------- -- 17-4 - Data insertion for Table HEALTHKITSAMPLES_RESTINGHEARTRATE -------------------------------------------------------- --updated on 4/18/2022 to prevent ORA-00001: unique constraint vaiolation on (IHS_2022.HEALTHKITSAMPLES_RESTINGHEARTRATE_PK) --(duplicated records could be inserted on different time) insert into HEALTHKITSAMPLES_RESTINGHEARTRATE ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,STARTDATE ,RECORD_DATE ,VALUE ,UNITS ,SOURCEIDENTIFIER ,SOURCENAME ,SOURCEVERSION ,SOURCEOPERATINGSYSTEMVERSION ,SOURCEPRODUCTTYPE ,DEVICENAME ,DEVICEMODEL ,DEVICEMANUFACTURER ,DEVICEHARDWAREVERSION ,DEVICESOFTWAREVERSION ,DEVICEFIRMWAREVERSION ,DEVICELOCALIDENTIFIER ,DEVICEFDAIDENTIFIER ,METADATA ,INSERTEDDATE ) with ranked_recs as ( select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s."DATE") as RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE ,ROW_NUMBER() OVER (PARTITION BY s.HEALTHKITSAMPLEKEY ORDER BY s.INSERTEDDATE desc) AS rn from stg_HEALTHKITSAMPLES_RESTINGHEARTRATE s where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date ) select s.HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,s.STARTDATE ,s.RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,s.INSERTEDDATE from ranked_recs s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_RESTINGHEARTRATE o on s.HEALTHKITSAMPLEKEY = o.HEALTHKITSAMPLEKEY where rn=1 and o.rowid is null ; commit; -------------------------------------------------------- -- 17-5 - Data insertion for Table HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL -------------------------------------------------------- --updated on 4/2/2022 to handle ORA-00001: unique constraint vaiolation on (IHS_2022.HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_PK) --(duplicated records inserted on different time) insert into HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,STARTDATE ,RECORD_DATE ,VALUE ,UNITS ,SOURCEIDENTIFIER ,SOURCENAME ,SOURCEVERSION ,SOURCEOPERATINGSYSTEMVERSION ,SOURCEPRODUCTTYPE ,DEVICENAME ,DEVICEMODEL ,DEVICEMANUFACTURER ,DEVICEHARDWAREVERSION ,DEVICESOFTWAREVERSION ,DEVICEFIRMWAREVERSION ,DEVICELOCALIDENTIFIER ,DEVICEFDAIDENTIFIER ,METADATA ,INSERTEDDATE ) with ranked_recs as ( select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s."DATE") as RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE ,ROW_NUMBER() OVER (PARTITION BY s.HEALTHKITSAMPLEKEY ORDER BY s.INSERTEDDATE desc) AS rn from stg_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL s where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date ) select s.HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,s.STARTDATE ,s.RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,s.INSERTEDDATE from ranked_recs s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL o on s.HEALTHKITSAMPLEKEY = o.HEALTHKITSAMPLEKEY where rn=1 and o.rowid is null ; commit; -------------------------------------------------------- -- 17-6 - Data insertion for Table HEALTHKITSAMPLES_STEPS -------------------------------------------------------- --updated on 4/18/2022 to prevent ORA-00001: unique constraint vaiolation on (IHS_2022.HEALTHKITSAMPLES_STEPS_PK) --(duplicated records could be inserted on different time) insert into HEALTHKITSAMPLES_STEPS ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,STARTDATE ,RECORD_DATE ,VALUE ,UNITS ,SOURCEIDENTIFIER ,SOURCENAME ,SOURCEVERSION ,SOURCEOPERATINGSYSTEMVERSION ,SOURCEPRODUCTTYPE ,DEVICENAME ,DEVICEMODEL ,DEVICEMANUFACTURER ,DEVICEHARDWAREVERSION ,DEVICESOFTWAREVERSION ,DEVICEFIRMWAREVERSION ,DEVICELOCALIDENTIFIER ,DEVICEFDAIDENTIFIER ,METADATA ,INSERTEDDATE ) with ranked_recs as ( select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s."DATE") as RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE ,ROW_NUMBER() OVER (PARTITION BY s.HEALTHKITSAMPLEKEY ORDER BY s.INSERTEDDATE desc) AS rn from stg_HEALTHKITSAMPLES_STEPS s where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date ) select s.HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,s.STARTDATE ,s.RECORD_DATE ,s.VALUE ,s.UNITS ,s.SOURCEIDENTIFIER ,s.SOURCENAME ,s.SOURCEVERSION ,s.SOURCEOPERATINGSYSTEMVERSION ,s.SOURCEPRODUCTTYPE ,s.DEVICENAME ,s.DEVICEMODEL ,s.DEVICEMANUFACTURER ,s.DEVICEHARDWAREVERSION ,s.DEVICESOFTWAREVERSION ,s.DEVICEFIRMWAREVERSION ,s.DEVICELOCALIDENTIFIER ,s.DEVICEFDAIDENTIFIER ,s.METADATA ,s.INSERTEDDATE from ranked_recs s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_STEPS o on s.HEALTHKITSAMPLEKEY = o.HEALTHKITSAMPLEKEY where rn=1 and o.rowid is null ; commit; -------------------------------------------------------- -- 21-1 - Data insertion for Table HEALTHKITSTATISTICS_DAILYSTEPS -------------------------------------------------------- --updated on 7/16/2022 to remove outdated data: delete from HEALTHKITSTATISTICS_DAILYSTEPS where rowid in ( select o.rowid from stg_HEALTHKITSTATISTICS_DAILYSTEPS s --join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join HEALTHKITSTATISTICS_DAILYSTEPS o on fn_guid_to_raw(s.HEALTHKITSTATISTICKEY) = o.HEALTHKITSTATISTICKEY and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER where substr(s.STARTDATE,1,10) >= program_start_date --substr(s.STARTDATE,1,10) >= '2022-04-01' and round(to_number(s.value))!=o.value and TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE)>o.INSERTEDDATE and TO_UTC_TIMESTAMP_TZ(s.STARTDATE) = o.STARTDATE and TO_UTC_TIMESTAMP_TZ(s."DATE") = o.RECORD_DATE ) ; insert into HEALTHKITSTATISTICS_DAILYSTEPS ( HEALTHKITSTATISTICKEY ,PARTICIPANTIDENTIFIER ,STARTDATE ,RECORD_DATE ,VALUE ,UNITS ,INSERTEDDATE ) select fn_guid_to_raw(s.HEALTHKITSTATISTICKEY) as HEALTHKITSTATISTICKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s."DATE") as RECORD_DATE ,s.VALUE ,s.UNITS ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_HEALTHKITSTATISTICS_DAILYSTEPS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSTATISTICS_DAILYSTEPS o on fn_guid_to_raw(s.HEALTHKITSTATISTICKEY) = o.HEALTHKITSTATISTICKEY where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date and o.rowid is null ; commit; -------------------------------------------------------- -- 21-2 - Data insertion for Table HEALTHKITSTATISTICS_HOURLYSTEPS -------------------------------------------------------- --updated on 7/16/2022 to remove outdated data: delete from HEALTHKITSTATISTICS_HOURLYSTEPS where rowid in ( select o.rowid from stg_HEALTHKITSTATISTICS_HOURLYSTEPS s --join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join HEALTHKITSTATISTICS_HOURLYSTEPS o on fn_guid_to_raw(s.HEALTHKITSTATISTICKEY) = o.HEALTHKITSTATISTICKEY and s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER where substr(s.STARTDATE,1,10) >= program_start_date --substr(s.STARTDATE,1,10) >= '2022-04-01' and round(to_number(s.value))!=o.value and TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE)>o.INSERTEDDATE and TO_UTC_TIMESTAMP_TZ(s.STARTDATE) = o.STARTDATE and TO_UTC_TIMESTAMP_TZ(s."DATE") = o.RECORD_DATE ) ; --added on 7/1/2022: insert into HEALTHKITSTATISTICS_HOURLYSTEPS ( HEALTHKITSTATISTICKEY ,PARTICIPANTIDENTIFIER ,STARTDATE ,RECORD_DATE ,VALUE ,UNITS ,INSERTEDDATE ) select fn_guid_to_raw(s.HEALTHKITSTATISTICKEY) as HEALTHKITSTATISTICKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.STARTDATE) as STARTDATE ,TO_UTC_TIMESTAMP_TZ(s."DATE") as RECORD_DATE ,s.VALUE ,s.UNITS ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_HEALTHKITSTATISTICS_HOURLYSTEPS s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSTATISTICS_HOURLYSTEPS o on fn_guid_to_raw(s.HEALTHKITSTATISTICKEY) = o.HEALTHKITSTATISTICKEY where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.STARTDATE,1,10) >= program_start_date and o.rowid is null ; commit; -------------------------------------------------------- -- 22 - Data insertion for Table HEALTHKITSAMPLES_DELETED -------------------------------------------------------- --data_type = 1: stg_HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED insert into HEALTHKITSAMPLES_DELETED ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,DELETEDDATE ,DATA_TYPE ) select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.DELETEDDATE) as DELETEDDATE ,1 as DATA_TYPE from stg_HEALTHKITSAMPLES_APPLEEXERCISETIME_DELETED s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY and o.DATA_TYPE = 1 where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.DELETEDDATE,1,10) >= program_start_date and o.rowid is null ; commit; --data_type = 2: stg_HEALTHKITSAMPLES_HEARTRATE_DELETED insert into HEALTHKITSAMPLES_DELETED ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,DELETEDDATE ,DATA_TYPE ) select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.DELETEDDATE) as DELETEDDATE ,2 as DATA_TYPE from stg_HEALTHKITSAMPLES_HEARTRATE_DELETED s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY and o.DATA_TYPE = 2 where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.DELETEDDATE,1,10) >= program_start_date and o.rowid is null ; commit; --data_type = 3: stg_HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED insert into HEALTHKITSAMPLES_DELETED ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,DELETEDDATE ,DATA_TYPE ) select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.DELETEDDATE) as DELETEDDATE ,3 as DATA_TYPE from stg_HEALTHKITSAMPLES_HEARTRATEVARIABILITY_DELETED s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY and o.DATA_TYPE = 3 where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.DELETEDDATE,1,10) >= program_start_date and o.rowid is null ; commit; --data_type = 4: stg_HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED insert into HEALTHKITSAMPLES_DELETED ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,DELETEDDATE ,DATA_TYPE ) select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.DELETEDDATE) as DELETEDDATE ,4 as DATA_TYPE from stg_HEALTHKITSAMPLES_RESTINGHEARTRATE_DELETED s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY and o.DATA_TYPE = 4 where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.DELETEDDATE,1,10) >= program_start_date and o.rowid is null ; commit; --data_type = 5: stg_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED insert into HEALTHKITSAMPLES_DELETED ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,DELETEDDATE ,DATA_TYPE ) select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.DELETEDDATE) as DELETEDDATE ,5 as DATA_TYPE from stg_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL_DELETED s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY and o.DATA_TYPE = 5 where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.DELETEDDATE,1,10) >= program_start_date and o.rowid is null ; commit; --data_type = 6: stg_HEALTHKITSAMPLES_STEPS_DELETED insert into HEALTHKITSAMPLES_DELETED ( HEALTHKITSAMPLEKEY ,PARTICIPANTIDENTIFIER ,DELETEDDATE ,DATA_TYPE ) select fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) as HEALTHKITSAMPLEKEY ,s.PARTICIPANTIDENTIFIER ,TO_UTC_TIMESTAMP_TZ(s.DELETEDDATE) as DELETEDDATE ,6 as DATA_TYPE from stg_HEALTHKITSAMPLES_STEPS_DELETED s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join HEALTHKITSAMPLES_DELETED o on fn_guid_to_raw(s.HEALTHKITSAMPLEKEY) = o.HEALTHKITSAMPLEKEY and o.DATA_TYPE = 6 where length(s.PARTICIPANTIDENTIFIER) >= 13 and substr(s.DELETEDDATE,1,10) >= program_start_date and o.rowid is null ; commit; end;