DailyDeviceMetrics

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ParticipantIdentifier

varchar

100

√

null

PatientID

uniqueidentifier

16

√

null

DeIDLookup_Patients_DForger.PatientID

Implied Constraint

R

Type

varchar

16

null

Date

date

6

√

null

Value

int

4

√

null

DeviceType

varchar

210

√

null

Relationships

Close relationships within degrees of separation

View Definition

-- ==============================================================================
-- Author:		G.Siciliano
-- Date: 		02/24/2023
-- Description:	PROMPT DailyDeviceMetrics
-- ==============================================================================




CREATE VIEW [CareEv].[DailyDeviceMetrics]
AS 

/***************
 * Build Steps *
 ***************/

-- FitBit
SELECT DISTINCT a.[ParticipantIdentifier], b.PatientID
	  --,'PROMPT' AS Study
	  ,'Steps' AS Type
      ,CAST([Date] AS DATE) AS Date
      ,[Tracker.Steps] AS Value
	  ,'Fitbit' AS DeviceType
  FROM [PROMPT].[CareEv].[FitbitDailyData] a
  JOIN [PROMPT].[CareEv].[Metric_Lookup] b ON b.ParticipantIdentifier = a.ParticipantIdentifier
  WHERE [Tracker.Steps] IS NOT NULL AND [Tracker.Steps] > 0 --1,033,181

UNION

-- Apple Watch
SELECT DISTINCT a.[ParticipantIdentifier], b.PatientID
	  --,'PROMPT' AS Study
	  ,'Steps' AS Type
      ,CAST([StartDate] AS DATE) AS Date
	  ,SUM(CAST([Value] AS INT)) AS Value
	  ,'Apple Watch' AS DeviceType
  FROM [PROMPT].[CareEv].[HKSamples] a
  JOIN [PROMPT].[CareEv].[Metric_Lookup] b ON b.ParticipantIdentifier = a.ParticipantIdentifier
  WHERE [Device.Model] = 'Watch' AND [Type] = 'Steps' AND [Device.Manufacturer] LIKE 'Apple%' 
  GROUP BY CAST([StartDate] AS DATE),
           a.ParticipantIdentifier,
           b.PatientID

UNION

-- iPhone
SELECT DISTINCT a.[ParticipantIdentifier], b.PatientID
	  --,'PROMPT' AS Study
	  ,'Steps' AS Type
      ,CAST([StartDate] AS DATE) AS Date
	  ,SUM(CAST([Value] AS INT)) AS Value
	  ,'iPhone' AS DeviceType
  FROM [PROMPT].[CareEv].[HKSamples] a
  JOIN [PROMPT].[CareEv].[Metric_Lookup] b ON b.ParticipantIdentifier = a.ParticipantIdentifier
  WHERE [Device.Model] = 'iPhone' AND [Type] = 'Steps' AND [Device.Manufacturer] LIKE 'Apple%'
  GROUP BY CAST([StartDate] AS DATE),
           a.ParticipantIdentifier,
           b.PatientID

UNION

-- Other Devices
SELECT DISTINCT a.[ParticipantIdentifier], b.PatientID
	  --,'PROMPT' AS Study
	  ,'Steps' AS Type
      ,CAST([StartDate] AS DATE) AS Date
	  ,SUM(CAST([Value] AS INT)) AS Value
	  ,'OTHER: ' + [Device.Manufacturer] + ' | ' + [Device.Model] AS DeviceType
  FROM [PROMPT].[CareEv].[HKSamples] a
  JOIN [PROMPT].[CareEv].[Metric_Lookup] b ON b.ParticipantIdentifier = a.ParticipantIdentifier
  WHERE [Type] = 'Steps' AND [Device.Manufacturer] NOT LIKE 'Apple%'
  GROUP BY CAST([StartDate] AS DATE),
           'OTHER: ' + [Device.Manufacturer] + ' | ' + [Device.Model],
           a.ParticipantIdentifier,
           b.PatientID

UNION

-- Other Devices Gathered by Google
SELECT DISTINCT a.[ParticipantIdentifier], b.PatientID
	  --,'PROMPT' AS Study
	  ,'Steps' AS Type
      ,CAST([WindowStartDate] AS DATE) AS Date
	  ,SUM(CAST([Value] AS INT)) AS Value
	  ,'OTHER: ' + [OriginalDataSourceDeviceManufacturer] + ' | ' + [OriginalDataSourceDeviceType] AS DeviceType
  FROM [PROMPT].[CareEv].[GoogleFitSamples] a
  JOIN [PROMPT].[CareEv].[Metric_Lookup] b ON b.ParticipantIdentifier = a.ParticipantIdentifier
  WHERE [Type] = 'Steps' 
  GROUP BY CAST([WindowStartDate] AS DATE),
           'OTHER: ' + [OriginalDataSourceDeviceManufacturer] + ' | ' + [OriginalDataSourceDeviceType],
           a.ParticipantIdentifier,
           b.PatientID

UNION

/**********************************
 * Build RHR - Resting Heart Rate *
 **********************************/

-- FitBit
SELECT DISTINCT a.[ParticipantIdentifier], b.PatientID
	  --,'PROMPT' AS Study
	  ,'RestingHeartRate' AS Type
      ,CAST([Date] AS DATE) AS Date
      ,[RestingHeartRate] AS Value
	  ,'Fitbit' AS DeviceType
  FROM [PROMPT].[CareEv].[FitbitDailyData] a
  JOIN [PROMPT].[CareEv].[Metric_Lookup] b ON b.ParticipantIdentifier = a.ParticipantIdentifier
  WHERE [RestingHeartRate] IS NOT NULL --337,916

UNION

-- Apple Watch
SELECT DISTINCT a.[ParticipantIdentifier], b.PatientID
	  --,'PROMPT' AS Study
	  ,'RestingHeartRate' AS Type
      ,CAST([StartDate] AS DATE) AS Date
	  ,AVG(CAST(CAST([Value] AS FLOAT) AS INT)) AS Value
	  ,'Apple Watch' AS DeviceType
  FROM [PROMPT].[CareEv].[HKSamples] a
  JOIN [PROMPT].[CareEv].[Metric_Lookup] b ON b.ParticipantIdentifier = a.ParticipantIdentifier
  WHERE [Source.Name] LIKE '%Watch%' AND [Type] = 'RestingHeartRate' 
  GROUP BY CAST([StartDate] AS DATE),
           a.ParticipantIdentifier,
           b.PatientID

UNION

-- Other Devices (no resting heart rate for GoogleFit)
SELECT DISTINCT a.[ParticipantIdentifier], b.PatientID
	  --,'PROMPT' AS Study
	  ,'RestingHeartRate' AS Type
      ,CAST([StartDate] AS DATE) AS Date
	  ,AVG(CAST(CAST([Value] AS FLOAT) AS INT)) AS Value
	  ,'OTHER: HealthKit' AS DeviceType
  FROM [PROMPT].[CareEv].[HKSamples] a
  JOIN [PROMPT].[CareEv].[Metric_Lookup] b ON b.ParticipantIdentifier = a.ParticipantIdentifier
  WHERE [Source.Name] NOT LIKE '%Watch%' AND [Type] = 'RestingHeartRate' 
  GROUP BY  CAST([StartDate] AS DATE),
           a.ParticipantIdentifier,
           b.PatientID

Possibly Referenced Tables/Views

DailyDeviceMetrics