IHSD.IHS

Parameters

Name	Type	Mode

Definition

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

CNT_FITBIT_DAILYSTEP integer;
	CNT_APPLEWATCH_DAILYSTEP integer;
	CNT_FITBIT_RESTINGHEARTRATE integer;
	CNT_APPLEWATCH_RESTINGHEARTRATE integer;
	CNT_FITBIT_INTRADAYSTEPS integer;
	CNT_APPLEWATCH_INTRADAYSTEPS integer;
	CNT_FITBIT_INTRADAYHEARTRATE integer;
	CNT_APPLEWATCH_INTRADAYHEARTRATE integer;
	CNT_FITBIT_DAILYACTIVITY integer;
	CNT_FITBIT_INTRADAYSLEEP integer;
	CNT_FITBIT_DAILYSLEEP integer;
	CNT_HEALTHKIT_INTRADAYSLEEP integer;
	CNT_HEALTHKIT_DAILYSLEEP integer;
	CNT_HEALTHKIT_HEARTRATEVARIABILITY integer;
	CNT_HEALTHKIT_ACTIVITY integer;

record_today number(1);

begin

--1: CNT_Fitbit_dailystep
	select count(distinct s.ParticipantIdentifier) into CNT_Fitbit_dailystep
	from STUDYPARTICIPANTS p
	join stg_FITBITDAILYDATA s on p.ParticipantIdentifier = s.ParticipantIdentifier
    where s.TRACKERSTEPS > 0
	and substr(s."DATE",1,10) >= program_start_date
	;

--2: CNT_Applewatch_dailystep
	select count(distinct s.ParticipantIdentifier) into CNT_Applewatch_dailystep
	from STUDYPARTICIPANTS p
	join stg_HEALTHKITSTATISTICS_DAILYSTEPS s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where substr(s.STARTDATE,1,10) >= program_start_date
	;

--3: CNT_Fitbit_RestingHeartRate
	select count(distinct s.ParticipantIdentifier) into CNT_Fitbit_RestingHeartRate
	from STUDYPARTICIPANTS p
	join stg_FITBITRESTINGHEARTRATES s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where substr(s."DATE",1,10) >= program_start_date
	;

--4: CNT_Applewatch_RestingHeartRate
	select count(distinct s.ParticipantIdentifier) into CNT_Applewatch_RestingHeartRate
	from STUDYPARTICIPANTS p
	join stg_HEALTHKITSAMPLES_RESTINGHEARTRATE s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where substr(s.STARTDATE,1,10) >= program_start_date
	;

--5: CNT_Fitbit_IntradaySteps
	select count(distinct s.ParticipantIdentifier) into CNT_Fitbit_IntradaySteps
	from STUDYPARTICIPANTS p
	join stg_FITBIT_STEPS s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where s.RECORD_DATE >= program_start_date
	and s.record_time is not null
	;

--6: CNT_Applewatch_IntradaySteps
	select count(distinct s.ParticipantIdentifier) into CNT_Applewatch_IntradaySteps
	from STUDYPARTICIPANTS p
	join stg_HEALTHKITSAMPLES_STEPS s on p.ParticipantIdentifier = s.ParticipantIdentifier
    where s.DeviceModel = 'Watch'
	and substr(s."DATE",1,10) >= program_start_date
	;

--7: CNT_Fitbit_IntradayHeartRate
	select count(distinct s.ParticipantIdentifier) into CNT_Fitbit_IntradayHeartRate
	from STUDYPARTICIPANTS p
	join stg_FITBIT_HEARTRATE s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where s.RECORD_DATE >= program_start_date
	and s.record_time is not null
	;

--8: CNT_Applewatch_IntradayHeartRate
	select count(distinct s.ParticipantIdentifier) into CNT_Applewatch_IntradayHeartRate
	from STUDYPARTICIPANTS p
	join stg_HEALTHKITSAMPLES_HEARTRATE s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where s.DEVICEMODEL = 'Watch'
	and substr(s."DATE",1,10) >= program_start_date
	;

--9: CNT_Fitbit_DailyActivity
	select count(distinct s.ParticipantIdentifier) into CNT_Fitbit_DailyActivity
	from STUDYPARTICIPANTS p
	join stg_FITBITDAILYDATA s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where substr(s."DATE",1,10) >= program_start_date
	;

--10: CNT_Fitbit_IntradaySleep
	select count(distinct s.ParticipantIdentifier) into CNT_Fitbit_IntradaySleep
	from STUDYPARTICIPANTS p
	join stg_FITBITSLEEPLOGDETAILS s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where substr(s.STARTDATE,1,10) >= program_start_date
	;

--11: CNT_Fitbit_DailySleep
	select count(distinct s.ParticipantIdentifier) into CNT_Fitbit_DailySleep
	from STUDYPARTICIPANTS p
	join stg_FITBITSLEEPLOGS s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where substr(s.STARTDATE,1,10) >= program_start_date
	;

--12: CNT_HealthKit_IntradaySleep
	select count(distinct s.ParticipantIdentifier) into CNT_HealthKit_IntradaySleep
	from STUDYPARTICIPANTS p
	join stg_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL s on p.ParticipantIdentifier = s.ParticipantIdentifier
    where s.VALUE in ('InBed', 'Asleep')
	and substr(s.STARTDATE,1,10) >= program_start_date
	;

--13: CNT_HealthKit_DailySleep
	select count(distinct s.ParticipantIdentifier) into CNT_HealthKit_DailySleep
	from STUDYPARTICIPANTS p
	join stg_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL s on p.ParticipantIdentifier = s.ParticipantIdentifier
    where s.VALUE in ('InBed', 'Asleep')
	and substr(s.STARTDATE,1,10) >= program_start_date
	;

--14: CNT_HealthKit_HeartRateVariability
	select count(distinct s.ParticipantIdentifier) into CNT_HealthKit_HeartRateVariability
	from STUDYPARTICIPANTS p
	join stg_HEALTHKITSAMPLES_HEARTRATEVARIABILITY s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where substr(s.STARTDATE,1,10) >= program_start_date
	;

--15: CNT_HealthKit_Activity
	select count(distinct s.ParticipantIdentifier) into CNT_HealthKit_Activity
	from STUDYPARTICIPANTS p
	join stg_HEALTHKITSAMPLES_APPLEEXERCISETIME s on p.ParticipantIdentifier = s.ParticipantIdentifier
	where substr(s.STARTDATE,1,10) >= program_start_date
	;

select count(*) into record_today from daily_wearable_datacounts 
	where trunc(insert_date) = trunc(sysdate)
	;

if record_today = 0 then 
		insert into daily_wearable_datacounts (
			INSERT_DATE
			,CNT_FITBIT_DAILYSTEP
			,CNT_APPLEWATCH_DAILYSTEP
			,CNT_FITBIT_RESTINGHEARTRATE
			,CNT_APPLEWATCH_RESTINGHEARTRATE
			,CNT_FITBIT_INTRADAYSTEPS
			,CNT_APPLEWATCH_INTRADAYSTEPS
			,CNT_FITBIT_INTRADAYHEARTRATE
			,CNT_APPLEWATCH_INTRADAYHEARTRATE
			,CNT_FITBIT_DAILYACTIVITY
			,CNT_FITBIT_INTRADAYSLEEP
			,CNT_FITBIT_DAILYSLEEP
			,CNT_HEALTHKIT_INTRADAYSLEEP
			,CNT_HEALTHKIT_DAILYSLEEP
			,CNT_HEALTHKIT_HEARTRATEVARIABILITY
			,CNT_HEALTHKIT_ACTIVITY
		)
		values (
			sysdate
			--,INSERT_DATE
			,CNT_FITBIT_DAILYSTEP
			,CNT_APPLEWATCH_DAILYSTEP
			,CNT_FITBIT_RESTINGHEARTRATE
			,CNT_APPLEWATCH_RESTINGHEARTRATE
			,CNT_FITBIT_INTRADAYSTEPS
			,CNT_APPLEWATCH_INTRADAYSTEPS
			,CNT_FITBIT_INTRADAYHEARTRATE
			,CNT_APPLEWATCH_INTRADAYHEARTRATE
			,CNT_FITBIT_DAILYACTIVITY
			,CNT_FITBIT_INTRADAYSLEEP
			,CNT_FITBIT_DAILYSLEEP
			,CNT_HEALTHKIT_INTRADAYSLEEP
			,CNT_HEALTHKIT_DAILYSLEEP
			,CNT_HEALTHKIT_HEARTRATEVARIABILITY
			,CNT_HEALTHKIT_ACTIVITY
		)
		;
		commit;
	end if;

end;

IHS_2023.PR_DAILY_WEARABLE_DATACOUNTS

Parameters

Definition