IHSD
.IHS_2023
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
IHS_2023.PR_POPULATE_IHS_GARMIN_DATA
Parameters
Name
Type
Mode
Definition
procedure pr_populate_IHS_Garmin_data -- use AUTHID CURRENT_USER to execute with the privileges and -- schema context of the calling user AUTHID CURRENT_USER AS begin -------------------------------------------------------- -- 1 - Data insertion for Table GARMINACTIVITYSUMMARY -------------------------------------------------------- delete from GARMINACTIVITYSUMMARY where rowid in ( select o.rowid from stg_GARMINACTIVITYSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join GARMINACTIVITYSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.ACTIVITYID = o.ACTIVITYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS where o.INSERTEDDATE < TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 ) ; insert into GARMINACTIVITYSUMMARY ( PARTICIPANTID ,PARTICIPANTIDENTIFIER ,SUMMARYID ,ACTIVITYID ,STARTTIMEINSECONDS ,STARTTIMEOFFSETINSECONDS ,DURATIONINSECONDS ,ACTIVITYTYPE ,ACTIVITYNAME ,AVERAGEBIKECADENCEINROUNDSPERMINUTE ,AVERAGEHEARTRATEINBEATSPERMINUTE ,AVERAGERUNCADENCEINSTEPSPERMINUTE ,AVERAGESPEEDINMETERSPERSECOND ,AVERAGESWIMCADENCEINSTROKESPERMINUTE ,AVERAGEPACEINMINUTESPERKILOMETER ,ACTIVEKILOCALORIES ,DEVICENAME ,DISTANCEINMETERS ,MAXBIKECADENCEINROUNDSPERMINUTE ,MAXHEARTRATEINBEATSPERMINUTE ,MAXPACEINMINUTESPERKILOMETER ,MAXRUNCADENCEINSTEPSPERMINUTE ,MAXSPEEDINMETERSPERSECOND ,NUMBEROFACTIVELENGTHS ,STARTINGLATITUDEINDEGREE ,STARTINGLONGITUDEINDEGREE ,STEPS ,TOTALELEVATIONGAININMETERS ,TOTALELEVATIONLOSSINMETERS ,ISPARENT ,PARENTSUMMARYID ,MANUAL ,INSERTEDDATE ) select s.PARTICIPANTID ,s.PARTICIPANTIDENTIFIER ,s.SUMMARYID ,s.ACTIVITYID ,s.STARTTIMEINSECONDS ,s.STARTTIMEOFFSETINSECONDS ,s.DURATIONINSECONDS ,s.ACTIVITYTYPE ,s.ACTIVITYNAME ,s.AVERAGEBIKECADENCEINROUNDSPERMINUTE ,s.AVERAGEHEARTRATEINBEATSPERMINUTE ,s.AVERAGERUNCADENCEINSTEPSPERMINUTE ,s.AVERAGESPEEDINMETERSPERSECOND ,s.AVERAGESWIMCADENCEINSTROKESPERMINUTE ,s.AVERAGEPACEINMINUTESPERKILOMETER ,s.ACTIVEKILOCALORIES ,s.DEVICENAME ,s.DISTANCEINMETERS ,s.MAXBIKECADENCEINROUNDSPERMINUTE ,s.MAXHEARTRATEINBEATSPERMINUTE ,s.MAXPACEINMINUTESPERKILOMETER ,s.MAXRUNCADENCEINSTEPSPERMINUTE ,s.MAXSPEEDINMETERSPERSECOND ,s.NUMBEROFACTIVELENGTHS ,s.STARTINGLATITUDEINDEGREE ,s.STARTINGLONGITUDEINDEGREE ,s.STEPS ,s.TOTALELEVATIONGAININMETERS ,s.TOTALELEVATIONLOSSINMETERS ,s.ISPARENT ,s.PARENTSUMMARYID ,s.MANUAL ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_GARMINACTIVITYSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join GARMINACTIVITYSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.ACTIVITYID = o.ACTIVITYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; -------------------------------------------------------- -- 2 - Data insertion for Table GARMINDAILYSUMMARY -------------------------------------------------------- delete from GARMINDAILYSUMMARY where rowid in ( select o.rowid from stg_GARMINDAILYSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join GARMINDAILYSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where o.INSERTEDDATE < TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 ) ; insert into GARMINDAILYSUMMARY ( PARTICIPANTID ,PARTICIPANTIDENTIFIER ,SUMMARYID ,STARTTIMEINSECONDS ,STARTTIMEOFFSETINSECONDS ,DURATIONINSECONDS ,CALENDARDATE ,ACTIVEKILOCALORIES ,BMRKILOCALORIES ,STEPS ,DISTANCEINMETERS ,ACTIVETIMEINSECONDS ,MODERATEINTENSITYDURATIONINSECONDS ,VIGOROUSINTENSITYDURATIONINSECONDS ,FLOORSCLIMBED ,MINHEARTRATEINBEATSPERMINUTE ,MAXHEARTRATEINBEATSPERMINUTE ,AVERAGEHEARTRATEINBEATSPERMINUTE ,RESTINGHEARTRATEINBEATSPERMINUTE ,STEPSGOAL ,INTENSITYDURATIONGOALINSECONDS ,FLOORSCLIMBEDGOAL ,AVERAGESTRESSLEVEL ,MAXSTRESSLEVEL ,STRESSDURATIONINSECONDS ,RESTSTRESSDURATIONINSECONDS ,ACTIVITYSTRESSDURATIONINSECONDS ,LOWSTRESSDURATIONINSECONDS ,MEDIUMSTRESSDURATIONINSECONDS ,HIGHSTRESSDURATIONINSECONDS ,STRESSQUALIFIER ,INSERTEDDATE ) select s.PARTICIPANTID ,s.PARTICIPANTIDENTIFIER ,s.SUMMARYID ,s.STARTTIMEINSECONDS ,s.STARTTIMEOFFSETINSECONDS ,s.DURATIONINSECONDS ,to_date(s.CALENDARDATE, 'yyyy-mm-dd') as CALENDARDATE ,s.ACTIVEKILOCALORIES ,s.BMRKILOCALORIES ,s.STEPS ,s.DISTANCEINMETERS ,s.ACTIVETIMEINSECONDS ,s.MODERATEINTENSITYDURATIONINSECONDS ,s.VIGOROUSINTENSITYDURATIONINSECONDS ,s.FLOORSCLIMBED ,s.MINHEARTRATEINBEATSPERMINUTE ,s.MAXHEARTRATEINBEATSPERMINUTE ,s.AVERAGEHEARTRATEINBEATSPERMINUTE ,s.RESTINGHEARTRATEINBEATSPERMINUTE ,s.STEPSGOAL ,s.INTENSITYDURATIONGOALINSECONDS ,s.FLOORSCLIMBEDGOAL ,s.AVERAGESTRESSLEVEL ,s.MAXSTRESSLEVEL ,s.STRESSDURATIONINSECONDS ,s.RESTSTRESSDURATIONINSECONDS ,s.ACTIVITYSTRESSDURATIONINSECONDS ,s.LOWSTRESSDURATIONINSECONDS ,s.MEDIUMSTRESSDURATIONINSECONDS ,s.HIGHSTRESSDURATIONINSECONDS ,s.STRESSQUALIFIER ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_GARMINDAILYSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join GARMINDAILYSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; -------------------------------------------------------- -- 3 - Data insertion for Table GARMINDAILYSUMMARY_SAMPLES -------------------------------------------------------- insert into GARMINDAILYSUMMARY_SAMPLES ( SUMMARYID ,OFFSETINSECONDS ,HEARTRATE ) select s.SUMMARYID ,s.OFFSETINSECONDS ,s.HEARTRATE from stg_GARMINDAILYSUMMARY_SAMPLES s where REMAINDER(to_number(s.OFFSETINSECONDS), 60) = 0 --only insert heart rate data at a minute order by s.SUMMARYID, to_number(s.OFFSETINSECONDS) asc ; commit; -------------------------------------------------------- -- 4 - Data insertion for Table GARMINEPOCHSUMMARY -------------------------------------------------------- delete from GARMINEPOCHSUMMARY where rowid in ( select o.rowid from stg_GARMINEPOCHSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join GARMINEPOCHSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS where o.INSERTEDDATE < TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 ) ; insert into GARMINEPOCHSUMMARY ( PARTICIPANTID ,PARTICIPANTIDENTIFIER ,SUMMARYID ,STARTTIMEINSECONDS ,STARTTIMEOFFSETINSECONDS ,DURATIONINSECONDS ,ACTIVITYTYPE ,ACTIVETIMEINSECONDS ,STEPS ,DISTANCEINMETERS ,ACTIVEKILOCALORIES ,MET ,INTENSITY ,MEANMOTIONINTENSITY ,MAXMOTIONINTENSITY ,INSERTEDDATE ) select s.PARTICIPANTID ,s.PARTICIPANTIDENTIFIER ,s.SUMMARYID ,s.STARTTIMEINSECONDS ,s.STARTTIMEOFFSETINSECONDS ,s.DURATIONINSECONDS ,s.ACTIVITYTYPE ,s.ACTIVETIMEINSECONDS ,s.STEPS ,s.DISTANCEINMETERS ,s.ACTIVEKILOCALORIES ,s.MET ,s.INTENSITY ,s.MEANMOTIONINTENSITY ,s.MAXMOTIONINTENSITY ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_GARMINEPOCHSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join GARMINEPOCHSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; -------------------------------------------------------- -- 5 - Data insertion for Table GARMINHRVSUMMARY -------------------------------------------------------- delete from GARMINHRVSUMMARY where rowid in ( select o.rowid from stg_GARMINHRVSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join GARMINHRVSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where o.INSERTEDDATE < TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 ) ; insert into GARMINHRVSUMMARY ( PARTICIPANTID ,PARTICIPANTIDENTIFIER ,SUMMARYID ,STARTTIMEINSECONDS ,STARTTIMEOFFSETINSECONDS ,DURATIONINSECONDS ,CALENDARDATE ,LASTNIGHTAVG ,LASTNIGHT5MINHIGH ,INSERTEDDATE ) select s.PARTICIPANTID ,s.PARTICIPANTIDENTIFIER ,s.SUMMARYID ,s.STARTTIMEINSECONDS ,s.STARTTIMEOFFSETINSECONDS ,s.DURATIONINSECONDS ,to_date(s.CALENDARDATE, 'yyyy-mm-dd') as CALENDARDATE ,s.LASTNIGHTAVG ,s.LASTNIGHT5MINHIGH ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_GARMINHRVSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join GARMINHRVSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; -------------------------------------------------------- -- 6 - Data insertion for Table GARMINHRVSUMMARY_SAMPLES -------------------------------------------------------- insert into GARMINHRVSUMMARY_SAMPLES ( SUMMARYID ,OFFSETINSECONDS ,HRV_VALUE ) select s.SUMMARYID ,s.OFFSETINSECONDS ,VALUE from stg_GARMINHRVSUMMARY_SAMPLES s order by s.SUMMARYID, to_number(s.OFFSETINSECONDS) asc ; commit; -------------------------------------------------------- -- 7 - Data insertion for Table GARMINMOVEIQACTIVITYSUMMARY -------------------------------------------------------- delete from GARMINMOVEIQACTIVITYSUMMARY where rowid in ( select o.rowid from stg_GARMINMOVEIQACTIVITYSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join GARMINMOVEIQACTIVITYSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where o.INSERTEDDATE < TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 ) ; insert into GARMINMOVEIQACTIVITYSUMMARY ( PARTICIPANTID ,PARTICIPANTIDENTIFIER ,SUMMARYID ,STARTTIMEINSECONDS ,OFFSETINSECONDS ,DURATIONINSECONDS ,CALENDARDATE ,ACTIVITYTYPE ,ACTIVITYSUBTYPE ,INSERTEDDATE ) select s.PARTICIPANTID ,s.PARTICIPANTIDENTIFIER ,s.SUMMARYID ,s.STARTTIMEINSECONDS ,s.OFFSETINSECONDS ,s.DURATIONINSECONDS ,to_date(s.CALENDARDATE, 'yyyy-mm-dd') as CALENDARDATE ,s.ACTIVITYTYPE ,s.ACTIVITYSUBTYPE ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_GARMINMOVEIQACTIVITYSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join GARMINMOVEIQACTIVITYSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; -------------------------------------------------------- -- 8 - Data insertion for Table GARMINSLEEPSUMMARY -------------------------------------------------------- delete from GARMINSLEEPSUMMARY where rowid in ( select o.rowid from stg_GARMINSLEEPSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join GARMINSLEEPSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where o.INSERTEDDATE < TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 ) ; insert into GARMINSLEEPSUMMARY ( PARTICIPANTID ,PARTICIPANTIDENTIFIER ,SUMMARYID ,STARTTIMEINSECONDS ,STARTTIMEOFFSETINSECONDS ,DURATIONINSECONDS ,CALENDARDATE ,UNMEASURABLESLEEPINSECONDS ,DEEPSLEEPDURATIONINSECONDS ,LIGHTSLEEPDURATIONINSECONDS ,REMSLEEPINSECONDS ,AWAKEDURATIONINSECONDS ,VALIDATION ,OVERALLSLEEPSCOREVALUE ,OVERALLSLEEPSCOREQUALIFIERKEY ,INSERTEDDATE ) select s.PARTICIPANTID ,s.PARTICIPANTIDENTIFIER ,s.SUMMARYID ,s.STARTTIMEINSECONDS ,s.STARTTIMEOFFSETINSECONDS ,s.DURATIONINSECONDS ,to_date(s.CALENDARDATE, 'yyyy-mm-dd') as CALENDARDATE ,s.UNMEASURABLESLEEPINSECONDS ,s.DEEPSLEEPDURATIONINSECONDS ,s.LIGHTSLEEPDURATIONINSECONDS ,s.REMSLEEPINSECONDS ,s.AWAKEDURATIONINSECONDS ,s.VALIDATION ,s.OVERALLSLEEPSCOREVALUE ,s.OVERALLSLEEPSCOREQUALIFIERKEY ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_GARMINSLEEPSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join GARMINSLEEPSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; -------------------------------------------------------- -- 9 - Data insertion for Table GARMINSLEEPSUMMARY_DURATION -------------------------------------------------------- insert into GARMINSLEEPSUMMARY_DURATION ( SUMMARYID ,STARTTIMEINSECONDS ,ENDTIMEINSECONDS ,SLEEPLEVEL ) select s.SUMMARYID ,s.STARTTIMEINSECONDS ,s.ENDTIMEINSECONDS ,s.SLEEPLEVEL from stg_GARMINSLEEPSUMMARY_DURATION s ; commit; -------------------------------------------------------- -- 10 - Data insertion for Table GARMINSLEEPSUMMARY_SCORE -------------------------------------------------------- insert into GARMINSLEEPSUMMARY_SCORE ( SUMMARYID ,SCORETYPE ,QUALIFIERKEY ) select s.SUMMARYID ,s.SCORETYPE ,s.QUALIFIERKEY from stg_GARMINSLEEPSUMMARY_SCORE s ; commit; -------------------------------------------------------- -- 11 - Data insertion for Table GARMINSTRESSDETAILSUMMARY -------------------------------------------------------- delete from GARMINSTRESSDETAILSUMMARY where rowid in ( select o.rowid from stg_GARMINSTRESSDETAILSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join GARMINSTRESSDETAILSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where o.INSERTEDDATE < TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 ) ; insert into GARMINSTRESSDETAILSUMMARY ( PARTICIPANTID ,PARTICIPANTIDENTIFIER ,SUMMARYID ,STARTTIMEINSECONDS ,STARTTIMEOFFSETINSECONDS ,DURATIONINSECONDS ,CALENDARDATE ,INSERTEDDATE ) select s.PARTICIPANTID ,s.PARTICIPANTIDENTIFIER ,s.SUMMARYID ,s.STARTTIMEINSECONDS ,s.STARTTIMEOFFSETINSECONDS ,s.DURATIONINSECONDS ,to_date(s.CALENDARDATE, 'yyyy-mm-dd') as CALENDARDATE ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_GARMINSTRESSDETAILSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join GARMINSTRESSDETAILSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; -------------------------------------------------------- -- 12 - Data insertion for Table GARMINSTRESSDETAILSUMMARY_SAMPLES -------------------------------------------------------- insert into GARMINSTRESSDETAILSUMMARY_SAMPLES ( SUMMARYID ,OFFSETINSECONDS ,VALUE ,SAMPLETYPE ) select s.SUMMARYID ,s.OFFSETINSECONDS ,s.VALUE ,s.SAMPLETYPE from stg_GARMINSTRESSDETAILSUMMARY_SAMPLES s ; commit; -------------------------------------------------------- -- 13 - Data insertion for Table GARMINTHIRDPARTYDAILYSUMMARY -------------------------------------------------------- delete from GARMINTHIRDPARTYDAILYSUMMARY where rowid in ( select o.rowid from stg_GARMINTHIRDPARTYDAILYSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join GARMINTHIRDPARTYDAILYSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where o.INSERTEDDATE < TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 ) ; insert into GARMINTHIRDPARTYDAILYSUMMARY ( PARTICIPANTID ,PARTICIPANTIDENTIFIER ,SUMMARYID ,STARTTIMEINSECONDS ,STARTTIMEOFFSETINSECONDS ,DURATIONINSECONDS ,CALENDARDATE ,ACTIVEKILOCALORIES ,BMRKILOCALORIES ,STEPS ,DISTANCEINMETERS ,ACTIVETIMEINSECONDS ,MODERATEINTENSITYDURATIONINSECONDS ,VIGOROUSINTENSITYDURATIONINSECONDS ,FLOORSCLIMBED ,STEPSGOAL ,MINHEARTRATEINBEATSPERMINUTE ,MAXHEARTRATEINBEATSPERMINUTE ,AVERAGEHEARTRATEINBEATSPERMINUTE ,SOURCE ,INSERTEDDATE ) select s.PARTICIPANTID ,s.PARTICIPANTIDENTIFIER ,s.SUMMARYID ,s.STARTTIMEINSECONDS ,s.STARTTIMEOFFSETINSECONDS ,s.DURATIONINSECONDS ,to_date(s.CALENDARDATE, 'yyyy-mm-dd') as CALENDARDATE ,s.ACTIVEKILOCALORIES ,s.BMRKILOCALORIES ,s.STEPS ,s.DISTANCEINMETERS ,s.ACTIVETIMEINSECONDS ,s.MODERATEINTENSITYDURATIONINSECONDS ,s.VIGOROUSINTENSITYDURATIONINSECONDS ,s.FLOORSCLIMBED ,s.STEPSGOAL ,s.MINHEARTRATEINBEATSPERMINUTE ,s.MAXHEARTRATEINBEATSPERMINUTE ,s.AVERAGEHEARTRATEINBEATSPERMINUTE ,s.SOURCE ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_GARMINTHIRDPARTYDAILYSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join GARMINTHIRDPARTYDAILYSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and s.STARTTIMEINSECONDS = o.STARTTIMEINSECONDS and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; -------------------------------------------------------- -- 14 - Data insertion for Table GARMINTHIRDPARTYDAILYSUMMARY_SAMPLES -------------------------------------------------------- insert into GARMINTHIRDPARTYDAILYSUMMARY_SAMPLES ( SUMMARYID ,OFFSETINSECONDS ,HEARTRATE ) select s.SUMMARYID ,s.OFFSETINSECONDS ,s.HEARTRATE from stg_GARMINTHIRDPARTYDAILYSUMMARY_SAMPLES s ; commit; -------------------------------------------------------- -- 15 - Data insertion for Table GARMINUSERMETRICSSUMMARY -------------------------------------------------------- delete from GARMINUSERMETRICSSUMMARY where rowid in ( select o.rowid from stg_GARMINUSERMETRICSSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER join GARMINUSERMETRICSSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where o.INSERTEDDATE < TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) and length(s.PARTICIPANTIDENTIFIER) >= 13 ) ; insert into GARMINUSERMETRICSSUMMARY ( PARTICIPANTID ,PARTICIPANTIDENTIFIER ,SUMMARYID ,CALENDARDATE ,VO2MAX ,ENHANCED ,FITNESSAGE ,INSERTEDDATE ) select s.PARTICIPANTID ,s.PARTICIPANTIDENTIFIER ,s.SUMMARYID ,to_date(s.CALENDARDATE, 'yyyy-mm-dd') as CALENDARDATE ,s.VO2MAX ,s.ENHANCED ,s.FITNESSAGE ,TO_UTC_TIMESTAMP_TZ(s.INSERTEDDATE) as INSERTEDDATE from stg_GARMINUSERMETRICSSUMMARY s join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER left join GARMINUSERMETRICSSUMMARY o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER and s.SUMMARYID = o.SUMMARYID and to_date(s.CALENDARDATE, 'yyyy-mm-dd') = o.CALENDARDATE where length(s.PARTICIPANTIDENTIFIER) >= 13 and o.rowid is null ; commit; end;