IHSD.IHS

Parameters

Name	Type	Mode

Definition

procedure pr_FITBIT_intraday_activities (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

cursor c is
	select
		s.PARTICIPANTIDENTIFIER
		,to_date(s.record_date||' '||nvl(s.record_time, '00:00:00'), 'yyyy-mm-dd hh24:mi:ss') as record_date
		,s.metric_value
	from stg_fitbit_heartrate s
	join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER
	left join fitbit_heartrate o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER 
		and to_date(s.record_date||' '||nvl(s.record_time, '00:00:00'), 'yyyy-mm-dd hh24:mi:ss') = o.record_date
	where length(s.PARTICIPANTIDENTIFIER) >= 13
		and s.RECORD_DATE >= program_start_date
		and s.record_time is not null
		and o.rowid is null
    order by s.PARTICIPANTIDENTIFIER, s.record_date, s.record_time
	;

tmp_id varchar2(15) := NULL;
	pre_time date := NULL;

begin
	--------------------------------------------------------
	--  1 - Data insertion for Table fitbit_steps
	--------------------------------------------------------

insert into fitbit_steps (
		PARTICIPANTIDENTIFIER
		,RECORD_DATE
		,METRIC_VALUE
	)
	select
		s.PARTICIPANTIDENTIFIER
		,to_date(s.record_date||' '||nvl(s.record_time, '00:00:00'), 'yyyy-mm-dd hh24:mi:ss') as record_date
		,s.metric_value
	from stg_fitbit_steps s
	join STUDYPARTICIPANTS p on s.PARTICIPANTIDENTIFIER = p.PARTICIPANTIDENTIFIER
	left join fitbit_steps o on s.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER 
		and to_date(s.record_date||' '||nvl(s.record_time, '00:00:00'), 'yyyy-mm-dd hh24:mi:ss') = o.record_date
	where length(s.PARTICIPANTIDENTIFIER) >= 13 
		and s.RECORD_DATE >= program_start_date
		and s.record_time is not null
		and o.rowid is null
	order by s.PARTICIPANTIDENTIFIER, s.record_date, s.record_time
	;
    commit;

--------------------------------------------------------
	--  2 - Data insertion for Table fitbit_heartrate
	--------------------------------------------------------

for rec in c loop
		if nvl(tmp_id, 'x') <> rec.PARTICIPANTIDENTIFIER then
			--commit for the previous ID		
			commit;

--insert the first HeartRate record for a new ID
			insert into fitbit_heartrate (
				PARTICIPANTIDENTIFIER
				,RECORD_DATE
				,METRIC_VALUE
			)
			values (
				rec.PARTICIPANTIDENTIFIER
				,rec.RECORD_DATE
				,rec.METRIC_VALUE
			);

--track the ID and time
			tmp_id := rec.PARTICIPANTIDENTIFIER;
			pre_time := rec.record_date;
		elsif (rec.record_date - pre_time) * 24 * 60 * 60 >= 60 then 
			--record at every minute (60 seconds interval)
			insert into fitbit_heartrate (
				PARTICIPANTIDENTIFIER
				,RECORD_DATE
				,METRIC_VALUE
			)
			values (
				rec.PARTICIPANTIDENTIFIER
				,rec.RECORD_DATE
				,rec.METRIC_VALUE
			);

--reset pre_time
			pre_time := rec.record_date;
		end if;
	end loop;
	commit;

end;

IHS_2023.PR_FITBIT_INTRADAY_ACTIVITIES

Parameters

Definition