IHS_2023.PR_RECENT_HEALTHKIT_DAILYDATA

procedure pr_recent_Healthkit_dailydata (program_start_date date := '01-APR-2023')

-- use AUTHID CURRENT_USER to execute with the privileges and 

-- schema context of the calling user

AUTHID CURRENT_USER AS

​

begin

    execute immediate 'truncate table healthkit_dailydata';

    insert into healthkit_dailydata

        select

            PARTICIPANTIDENTIFIER

            ,trunc(RECORD_DATE) as record_date

            ,'sleep' as type

        from HEALTHKITSAMPLES_SLEEPANALYSISINTERVAL

        where VALUE = 'InBed'

        and STARTDATE >= program_start_date     --4/6/2022: added to limit data from the program start date

        and RECORD_DATE > STARTDATE

        and trunc(RECORD_DATE) > trunc(sysdate) - 90

    union all

        select

            PARTICIPANTIDENTIFIER

            ,trunc(StartDATE) as record_date

            ,'step' as type

        from HEALTHKITSTATISTICS_DAILYSTEPS

        --where TYPE = 'DailySteps'

        where VALUE > 0

        and STARTDATE >= program_start_date     --4/6/2022: added to limit data from the program start date

        and trunc(StartDATE) > trunc(sysdate) - 90

    ;

    commit;

end;