IHSD.IHS

Parameters

Name	Type	Mode

Definition

procedure pr_update_apple_watch_devices (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 
	with aw_data as (
		select 
			PARTICIPANTIDENTIFIER
			,DEVICEHARDWAREVERSION
			,DEVICESOFTWAREVERSION
			,substr(STARTDATE,1,10) as start_date
		from STG_HEALTHKITSAMPLES_APPLEEXERCISETIME
		where DEVICENAME='Apple Watch'
		and substr(STARTDATE,1,10) >= program_start_date
		union all
		select 
			PARTICIPANTIDENTIFIER
			,DEVICEHARDWAREVERSION
			,DEVICESOFTWAREVERSION
			,substr(STARTDATE,1,10) as start_date
		from STG_HEALTHKITSAMPLES_HEARTRATE
		where DEVICENAME='Apple Watch'
		and substr(STARTDATE,1,10) >= program_start_date
		union all
		select 
			PARTICIPANTIDENTIFIER
			,DEVICEHARDWAREVERSION
			,DEVICESOFTWAREVERSION
			,substr(STARTDATE,1,10) as start_date
		from STG_HEALTHKITSAMPLES_HEARTRATEVARIABILITY
		where DEVICENAME='Apple Watch'
		and substr(STARTDATE,1,10) >= program_start_date
		union all
		select 
			PARTICIPANTIDENTIFIER
			,DEVICEHARDWAREVERSION
			,DEVICESOFTWAREVERSION
			,substr(STARTDATE,1,10) as start_date
		from STG_HEALTHKITSAMPLES_RESTINGHEARTRATE
		where DEVICENAME='Apple Watch'
		and substr(STARTDATE,1,10) >= program_start_date
		union all
		select 
			PARTICIPANTIDENTIFIER
			,DEVICEHARDWAREVERSION
			,DEVICESOFTWAREVERSION
			,substr(STARTDATE,1,10) as start_date
		from STG_HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL
		where DEVICENAME='Apple Watch'
		and substr(STARTDATE,1,10) >= program_start_date
		union all
		select 
			PARTICIPANTIDENTIFIER
			,DEVICEHARDWAREVERSION
			,DEVICESOFTWAREVERSION
			,substr(STARTDATE,1,10) as start_date
		from STG_HEALTHKITSAMPLES_STEPS
		where DEVICENAME='Apple Watch'
		and substr(STARTDATE,1,10) >= program_start_date
	),
	new_aw_data as (
		select 
			PARTICIPANTIDENTIFIER
			,DEVICEHARDWAREVERSION as Apple_Watch_Model
			,DEVICESOFTWAREVERSION as Apple_Watch_OS
			,min(to_date(start_date, 'yyyy-mm-dd')) as start_date
			,max(to_date(start_date, 'yyyy-mm-dd')) as end_date
		from aw_data
		group by 
			PARTICIPANTIDENTIFIER
			,DEVICEHARDWAREVERSION
			,DEVICESOFTWAREVERSION
	)
	------------------------------	
	select n.PARTICIPANTIDENTIFIER
	,n.APPLE_WATCH_MODEL
	,n.APPLE_WATCH_OS
	,n.START_DATE
	,n.END_DATE
	,o.rowid as old_row
	,o.start_date as old_start_date
	,o.end_date as old_end_date
	from new_aw_data n
	left join apple_watch_devices o on n.PARTICIPANTIDENTIFIER = o.PARTICIPANTIDENTIFIER
	and n.Apple_Watch_Model = o.Apple_Watch_Model
	and n.Apple_Watch_OS = o.Apple_Watch_OS
	order by case when o.rowid is not null then 1 else 2 end asc    --old goes first
	;

begin

for rec in c loop
		if rec.old_row is not null then
			if rec.end_date > rec.old_end_date then
				update apple_watch_devices set
					END_DATE = rec.END_DATE
				where rowid = rec.old_row;
			elsif rec.start_date < rec.old_start_date then   --Should have no case like this. But if there're a few rare cases arising, just handle them!
				update apple_watch_devices set
					START_DATE = rec.START_DATE
				where rowid = rec.old_row;
			end if;
		else  --new Apple Watch device
			insert into apple_watch_devices (
				PARTICIPANTIDENTIFIER
				,APPLE_WATCH_MODEL
				,APPLE_WATCH_OS
				,START_DATE
				,END_DATE
			) 
			VALUES (
				rec.PARTICIPANTIDENTIFIER
				,rec.APPLE_WATCH_MODEL
				,rec.APPLE_WATCH_OS
				,rec.START_DATE
				,rec.END_DATE
			);
		end if;
	end loop;	
	commit;

end;

IHS_2023.PR_UPDATE_APPLE_WATCH_DEVICES

Parameters

Definition