Constant Field Values

Contents

lib.aide.*

org.diabetestechnology.*

  • org.diabetestechnology.drh.service.http.util.MetricsQueries
    Modifier and Type
    Constant Field
    Value
    public static final String
    agp_optim_maxim_query
    "WITH date_range AS (\n SELECT DATE(?) AS start_date, DATE(?) AS end_date\n),\nglucose_data AS (\n SELECT\n gr.participant_id,\n gr.Date_Time AS timestamp,\n strftime(\'%Y-%m-%d %H\', gr.Date_Time) AS hourValue,\n gr.CGM_Value AS glucose_level\n FROM\n {dbName}.combined_cgm_tracing_cached gr\n WHERE\n gr.participant_id = ? \n AND DATE(gr.Date_Time) BETWEEN (SELECT start_date FROM date_range) AND (SELECT end_date FROM date_range)\n),\nranked_data AS (\n SELECT\n participant_id,\n hourValue,\n glucose_level,\n ROW_NUMBER() OVER (PARTITION BY hourValue ORDER BY glucose_level) AS row_num,\n COUNT(*) OVER (PARTITION BY hourValue) AS total_count\n FROM\n glucose_data\n),\npercentiles AS (\n SELECT\n hourValue AS hour,\n MAX(CASE WHEN row_num = CAST(0.05 * total_count AS INT) THEN glucose_level END) AS p5,\n MAX(CASE WHEN row_num = CAST(0.25 * total_count AS INT) THEN glucose_level END) AS p25,\n MAX(CASE WHEN row_num = CAST(0.50 * total_count AS INT) THEN glucose_level END) AS p50,\n MAX(CASE WHEN row_num = CAST(0.75 * total_count AS INT) THEN glucose_level END) AS p75,\n MAX(CASE WHEN row_num = CAST(0.95 * total_count AS INT) THEN glucose_level END) AS p95\n FROM\n ranked_data\n GROUP BY\n hour\n),\nhourly_averages AS (\nSELECT\n SUBSTR(hour, 1, 10) AS date,\n SUBSTR(hour, 12) AS hour,\n COALESCE(AVG(p5), 0) AS p5,\n COALESCE(AVG(p25), 0) AS p25,\n COALESCE(AVG(p50), 0) AS p50,\n COALESCE(AVG(p75), 0) AS p75,\n COALESCE(AVG(p95), 0) AS p95\nFROM\n percentiles\nGROUP BY\n hour)\nSELECT\nhour,\nCOALESCE(AVG(p5), 0) AS p5,\nCOALESCE(AVG(p25), 0) AS p25,\nCOALESCE(AVG(p50), 0) AS p50,\nCOALESCE(AVG(p75), 0) AS p75,\nCOALESCE(AVG(p95), 0) AS p95\nFROM hourly_averages \nGROUP BY\nhour \nORDER BY\n hour;"
    public static final String
    agp_Query
    "WITH date_range AS (\n SELECT DATE(?) AS start_date, DATE(?) AS end_date\n ),\n all_hours AS (\n SELECT DATETIME(date_range.start_date, \'+\' || (t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) || \' hours\') AS hour\n FROM date_range,\n (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION\n SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t0,\n (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION\n SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,\n (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION\n SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2,\n (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION\n SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3,\n (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION\n SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4\n WHERE hour BETWEEN date_range.start_date AND date_range.end_date\n ),\n glucose_data AS (\n SELECT\n gr.participant_id,\n gr.Date_Time AS timestamp,\n strftime(\'%Y-%m-%d\', gr.Date_Time) AS dayValue,\n strftime(\'%H\', gr.Date_Time) AS hourValue,\n gr.CGM_Value AS glucose_level\n FROM\n {dbName}.combined_cgm_tracing_cached gr\n JOIN\n all_hours ah ON strftime(\'%Y-%m-%d %H\', gr.Date_Time) = strftime(\'%Y-%m-%d %H\', ah.hour)\n WHERE\n gr.participant_id = ?\n ),\n ranked_data AS (\n SELECT\n participant_id,\n strftime(\'%Y-%m-%d %H\', timestamp) AS hour,\n glucose_level,\n ROW_NUMBER() OVER (PARTITION BY strftime(\'%Y-%m-%d %H\', timestamp) ORDER BY glucose_level) AS row_num,\n COUNT(*) OVER (PARTITION BY strftime(\'%Y-%m-%d %H\', timestamp)) AS total_count\n FROM\n glucose_data\n ),\n percentiles AS (\n SELECT\n hour,\n MAX(CASE WHEN row_num = CAST(0.05 * total_count AS INT) THEN glucose_level END) AS p5,\n MAX(CASE WHEN row_num = CAST(0.25 * total_count AS INT) THEN glucose_level END) AS p25,\n MAX(CASE WHEN row_num = CAST(0.50 * total_count AS INT) THEN glucose_level END) AS p50,\n MAX(CASE WHEN row_num = CAST(0.75 * total_count AS INT) THEN glucose_level END) AS p75,\n MAX(CASE WHEN row_num = CAST(0.95 * total_count AS INT) THEN glucose_level END) AS p95\n FROM\n ranked_data\n GROUP BY\n hour\n ),\n final_percentiles AS (\n SELECT\n SUBSTR(hour, 1, 10) AS date,\n SUBSTR(hour, 12) AS hour,\n AVG(p5) AS p5,\n AVG(p25) AS p25,\n AVG(p50) AS p50,\n AVG(p75) AS p75,\n AVG(p95) AS p95\n FROM\n percentiles\n GROUP BY\n date, hour\n )\n SELECT\n date,\n hour,\n COALESCE(p5, 0) AS p5,\n COALESCE(p25, 0) AS p25,\n COALESCE(p50, 0) AS p50,\n COALESCE(p75, 0) AS p75,\n COALESCE(p95, 0) AS p95\n FROM\n final_percentiles\n ORDER BY\n date, hour"
    public static final String
    all_studies_vanity_metrics_query
    "WITH all_participants AS ( {unionQuery} ) SELECT COUNT(DISTINCT participant_id) AS total_number_of_participants, FLOOR((CAST(SUM(CASE WHEN gender = \'F\' THEN 1 ELSE 0 END) AS FLOAT) / COUNT(*)) * 100) AS percent_female, FLOOR(AVG(age)) AS average_age FROM all_participants;"
    public static final String
    average_daily_risk
    "WITH DailyRisk AS ( \n SELECT \n participant_id, \n DATE(date_time) AS day, \n MAX(CGM_Value) - MIN(CGM_Value) AS daily_range \n FROM \n {dbName}.combined_cgm_tracing_cached cct \n WHERE \n DATE(date_time) BETWEEN DATE(?) AND DATE(?) \n GROUP BY \n participant_id, \n DATE(date_time) \n), \nAverageDailyRisk AS ( \n SELECT \n participant_id, \n AVG(daily_range) AS average_daily_risk \n FROM \n DailyRisk \n GROUP BY \n participant_id \n) \nSELECT \n average_daily_risk \nFROM \n AverageDailyRisk \nWHERE participant_id = ?"
    public static final String
    check_records
    "SELECT COUNT(*) AS record_count FROM {dbName}.combined_cgm_tracing_cached WHERE participant_id = ? AND DATE(Date_Time) BETWEEN ? AND ?"
    public static final String
    combined_all_metrics
    "WITH risk_scores AS (\n SELECT \n participant_id,\n CGM_Value,\n CASE\n WHEN CGM_Value < 90 THEN 10 * (5 - (CGM_Value / 18.0)) * (5 - (CGM_Value / 18.0))\n WHEN CGM_Value > 180 THEN 10 * ((CGM_Value / 18.0) - 10) * ((CGM_Value / 18.0) - 10)\n ELSE 0\n END AS risk_score\n FROM {dbName}.combined_cgm_tracing\n WHERE \n participant_id = \'{participantId}\'\n AND DATE(Date_Time) BETWEEN \'{startDate}\' AND \'{endDate}\'\n),\naverage_risk AS (\n SELECT \n participant_id,\n AVG(risk_score) AS avg_risk_score\n FROM risk_scores\n GROUP BY participant_id\n),\namplitude_data AS (\n SELECT \n participant_id,\n ABS(MAX(CGM_Value) - MIN(CGM_Value)) AS amplitude\n FROM {dbName}.combined_cgm_tracing\n WHERE \n participant_id = \'{participantId}\'\n AND DATE(Date_Time) BETWEEN \'{startDate}\' AND \'{endDate}\'\n GROUP BY DATE(Date_Time)\n),\nmean_amplitude AS (\n SELECT \n AVG(amplitude) AS mean_amplitude\n FROM amplitude_data\n),\nparticipant_min_max AS (\n SELECT \n participant_id,\n MIN(CGM_Value) AS min_glucose,\n MAX(CGM_Value) AS max_glucose,\n MIN(DATETIME(Date_Time)) AS start_time,\n MAX(DATETIME(Date_Time)) AS end_time\n FROM {dbName}.combined_cgm_tracing\n WHERE \n participant_id = \'{participantId}\' \n AND DATETIME(Date_Time) BETWEEN DATETIME(\'{startDate}\', \'00:00:00\') AND DATETIME(\'{endDate}\', \'23:59:59\')\n GROUP BY participant_id\n),\nm_value AS (\n SELECT \n (max_glucose - min_glucose) / ((strftime(\'%s\', end_time) - strftime(\'%s\', start_time)) / 60.0) AS m_value\n FROM participant_min_max\n),\ndaily_risk AS (\n SELECT \n participant_id,\n DATE(Date_Time) AS day,\n MAX(CGM_Value) - MIN(CGM_Value) AS daily_range\n FROM {dbName}.combined_cgm_tracing\n WHERE DATE(Date_Time) BETWEEN DATE(\'{startDate}\') AND DATE(\'{endDate}\')\n GROUP BY participant_id, DATE(Date_Time)\n),\naverage_daily_risk AS (\n SELECT \n participant_id,\n AVG(daily_range) AS average_daily_risk\n FROM daily_risk\n GROUP BY participant_id\n),\nglucose_stats AS (\n SELECT\n participant_id,\n AVG(CGM_Value) AS mean_glucose,\n (AVG(CGM_Value * CGM_Value) - AVG(CGM_Value) * AVG(CGM_Value)) AS variance_glucose\n FROM {dbName}.combined_cgm_tracing\n WHERE \n participant_id = \'{participantId}\'\n AND DATE(Date_Time) BETWEEN \'{startDate}\' AND \'{endDate}\'\n GROUP BY participant_id\n),\nlbgi_hbgi AS (\n SELECT \n ROUND(SUM(CASE WHEN (CGM_Value - 2.5) / 2.5 > 0 THEN ((CGM_Value - 2.5) / 2.5) * ((CGM_Value - 2.5) / 2.5) ELSE 0 END) * 5, 2) AS lbgi, \n ROUND(SUM(CASE WHEN (CGM_Value - 9.5) / 9.5 > 0 THEN ((CGM_Value - 9.5) / 9.5) * ((CGM_Value - 9.5) / 9.5) ELSE 0 END) * 5, 2) AS hbgi\n FROM {dbName}.combined_cgm_tracing\n WHERE \n participant_id = \'{participantId}\'\n AND DATE(Date_Time) BETWEEN \'{startDate}\' AND \'{endDate}\'\n),\ndaily_diffs AS (\n SELECT\n participant_id,\n DATE(Date_Time) AS date,\n CGM_Value,\n CGM_Value - LAG(CGM_Value) OVER (PARTITION BY participant_id ORDER BY DATE(Date_Time)) AS daily_diff\n FROM {dbName}.combined_cgm_tracing\n WHERE \n participant_id = \'{participantId}\'\n AND DATE(Date_Time) BETWEEN \'{startDate}\' AND \'{endDate}\'\n),\nmean_daily_diff AS (\n SELECT\n participant_id,\n AVG(daily_diff) AS mean_daily_diff\n FROM daily_diffs\n WHERE daily_diff IS NOT NULL\n GROUP BY participant_id\n),\nlag_values AS (\n SELECT \n participant_id,\n Date_Time,\n CGM_Value,\n LAG(CGM_Value) OVER (PARTITION BY participant_id ORDER BY Date_Time) AS lag_CGM_Value\n FROM {dbName}.combined_cgm_tracing\n WHERE \n participant_id = \'{participantId}\'\n AND DATE(Date_Time) BETWEEN \'{startDate}\' AND \'{endDate}\'\n),\nconga_hourly AS (\n SELECT \n participant_id,\n SQRT(\n AVG(\n (CGM_Value - lag_CGM_Value) * (CGM_Value - lag_CGM_Value)\n ) OVER (PARTITION BY participant_id ORDER BY Date_Time)\n ) AS conga_hourly\n FROM lag_values\n WHERE lag_CGM_Value IS NOT NULL\n),\nliability_index AS (\n SELECT\n SUM(CASE WHEN CGM_Value < 70 THEN 1 ELSE 0 END) AS hypoglycemic_episodes, \n SUM(CASE WHEN CGM_Value BETWEEN 70 AND 180 THEN 1 ELSE 0 END) AS euglycemic_episodes, \n SUM(CASE WHEN CGM_Value > 180 THEN 1 ELSE 0 END) AS hyperglycemic_episodes, \n ROUND(CAST(\n (SUM(CASE WHEN CGM_Value < 70 THEN 1 ELSE 0 END) + SUM(CASE WHEN CGM_Value > 180 THEN 1 ELSE 0 END))\n AS REAL\n ) / COUNT(*), 2) AS liability_index\n FROM {dbName}.combined_cgm_tracing\n WHERE \n participant_id = \'{participantId}\'\n AND DATE(Date_Time) BETWEEN \'{startDate}\' AND \'{endDate}\'\n GROUP BY participant_id\n),\nj_index AS (\n SELECT\n ROUND(0.001 * (mean_glucose + sqrt(variance_glucose)) * (mean_glucose + sqrt(variance_glucose)), 2) AS j_index\n FROM glucose_stats\n),\ntime_in_tight_range AS (\n SELECT \n (SUM(CASE WHEN CGM_Value BETWEEN 70 AND 140 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) AS time_in_tight_range_percentage,\n SUM(CASE WHEN CGM_Value BETWEEN 70 AND 140 THEN 1 ELSE 0 END) AS time_in_tight_range \n FROM {dbName}.combined_cgm_tracing\n WHERE \n participant_id = \'{participantId}\'\n AND DATE(Date_Time) BETWEEN \'{startDate}\' AND \'{endDate}\'\n GROUP BY participant_id\n)\nSELECT\n COALESCE((SELECT time_in_tight_range_percentage FROM time_in_tight_range), 0) AS time_in_tight_range_percentage,\n COALESCE((SELECT liability_index FROM liability_index), 0) AS liability_index,\n COALESCE((SELECT hypoglycemic_episodes FROM liability_index), 0) AS hypoglycemic_episodes,\n COALESCE((SELECT euglycemic_episodes FROM liability_index), 0) AS euglycemic_episodes,\n COALESCE((SELECT hyperglycemic_episodes FROM liability_index), 0) AS hyperglycemic_episodes, \n COALESCE((SELECT m_value FROM m_value), 0) AS m_value,\n COALESCE((SELECT mean_amplitude FROM mean_amplitude), 0) AS mean_amplitude,\n COALESCE((SELECT average_daily_risk FROM average_daily_risk WHERE participant_id = \'{participantId}\'), 0) AS average_daily_risk,\n COALESCE((SELECT j_index FROM j_index), 0) AS j_index,\n COALESCE((SELECT lbgi FROM lbgi_hbgi), 0) AS lbgi,\n COALESCE((SELECT hbgi FROM lbgi_hbgi), 0) AS hbgi, \n COALESCE((SELECT AVG(avg_risk_score) FROM average_risk), 0) AS grade,\n COALESCE((SELECT AVG(conga_hourly) FROM conga_hourly), 0) AS conga,\n COALESCE((SELECT mean_daily_diff FROM mean_daily_diff), 0) AS mean_daily_diff;"
    public static final String
    daily_profile_query
    "SELECT \nstrftime(\'%Y-%m-%dT%H:00:00\', Date_Time) as date_time, \nstrftime(\'%Y-%m-%d\', Date_Time) AS date, \nstrftime(\'%H\', Date_Time) AS hour, \nROUND(AVG(CGM_Value),2) AS glucose \nFROM {dbName}.combined_cgm_tracing_cached \nWHERE participant_id = ? \nAND Date_Time BETWEEN ? AND ? \nGROUP BY date, hour \nORDER BY date, hour"
    public static final String
    optim_agp_query
    "WITH RECURSIVE date_range AS (\n SELECT DATE(?) AS start_date, DATE(?) AS end_date\n),\nall_hours AS (\n SELECT start_date AS hour\n FROM date_range\n UNION ALL\n SELECT DATETIME(hour, \'+1 hour\')\n FROM all_hours, date_range\n WHERE hour < end_date\n),\nglucose_data AS (\n SELECT\n gr.participant_id,\n gr.Date_Time AS timestamp,\n strftime(\'%Y-%m-%d\', gr.Date_Time) AS dayValue,\n strftime(\'%H\', gr.Date_Time) AS hourValue,\n gr.CGM_Value AS glucose_level\n FROM\n {dbName}.combined_cgm_tracing_cached gr\n JOIN\n all_hours ah ON strftime(\'%Y-%m-%d %H\', gr.Date_Time) = strftime(\'%Y-%m-%d %H\', ah.hour)\n WHERE\n gr.participant_id = ?\n),\nranked_data AS (\n SELECT\n participant_id,\n strftime(\'%Y-%m-%d %H\', timestamp) AS hour,\n glucose_level,\n ROW_NUMBER() OVER (PARTITION BY strftime(\'%Y-%m-%d %H\', timestamp) ORDER BY glucose_level) AS row_num,\n COUNT(*) OVER (PARTITION BY strftime(\'%Y-%m-%d %H\', timestamp)) AS total_count\n FROM\n glucose_data\n),\npercentiles AS (\n SELECT\n hour,\n MAX(CASE WHEN row_num = CAST(0.05 * total_count AS INT) THEN glucose_level END) AS p5,\n MAX(CASE WHEN row_num = CAST(0.25 * total_count AS INT) THEN glucose_level END) AS p25,\n MAX(CASE WHEN row_num = CAST(0.50 * total_count AS INT) THEN glucose_level END) AS p50,\n MAX(CASE WHEN row_num = CAST(0.75 * total_count AS INT) THEN glucose_level END) AS p75,\n MAX(CASE WHEN row_num = CAST(0.95 * total_count AS INT) THEN glucose_level END) AS p95\n FROM\n ranked_data\n GROUP BY\n hour\n),\nfinal_percentiles AS (\n SELECT\n SUBSTR(hour, 1, 10) AS date,\n SUBSTR(hour, 12) AS hour,\n AVG(p5) AS p5,\n AVG(p25) AS p25,\n AVG(p50) AS p50,\n AVG(p75) AS p75,\n AVG(p95) AS p95\n FROM\n percentiles\n GROUP BY\n date, hour\n)\nSELECT\n date,\n hour,\n COALESCE(p5, 0) AS p5,\n COALESCE(p25, 0) AS p25,\n COALESCE(p50, 0) AS p50,\n COALESCE(p75, 0) AS p75,\n COALESCE(p95, 0) AS p95\nFROM\n final_percentiles\nORDER BY\n date, hour;\n"
    public static final String
    participant_cgm_dates_query
    "SELECT \nstrftime(\'%Y-%m-%d\', participant_cgm_start_date) AS participant_cgm_start_date, \nstrftime(\'%Y-%m-%d\', participant_cgm_end_date) AS participant_cgm_end_date \nFROM {dbName}.participant_cgm_date_range_cached \nWHERE participant_id = ? "
    public static final String
    participant_info_query
    "SELECT tenant_id, participant_id, gender, age, race_ethnicity, bmi, baseline_hba1c, diabetes_type, study_arm FROM {dbName}.drh_participant WHERE participant_id = ?"
    public static final String
    participant_metrics_query
    "WITH data AS ( SELECT DATE(Date_Time) AS Date, CGM_Value FROM {dbName}.combined_cgm_tracing_cached WHERE participant_id = ? AND DATE(Date_Time) BETWEEN ? AND ?), stats AS ( SELECT COALESCE(AVG(CGM_Value), 0) AS avg_cgm, COALESCE(AVG(CGM_Value * CGM_Value), 0) AS avg_cgm_sq, COUNT(DISTINCT Date) AS num_days, JULIANDAY(MAX(Date)) - JULIANDAY(MIN(Date)) + 1 AS days_range FROM data ), cv AS ( SELECT CASE WHEN avg_cgm = 0 THEN 0 ELSE (SQRT(avg_cgm_sq - avg_cgm * avg_cgm) / avg_cgm) * 100 END AS coefficient_of_variation FROM stats ), percentage_active AS ( SELECT CASE WHEN days_range = 0 THEN 0 ELSE ROUND((num_days / days_range) * 100, 2) END AS percentage_active FROM stats ), mean_glucose AS ( SELECT ROUND(COALESCE(avg_cgm, 0), 2) AS mean_glucose FROM stats ), gmi AS ( SELECT ROUND(COALESCE(avg_cgm, 0) * 0.155 + 95, 2) AS GMI FROM stats ) SELECT CASE WHEN cv.coefficient_of_variation = 0 OR stats.num_days = 0 OR percentage_active.percentage_active = 0 OR mean_glucose.mean_glucose = 0 OR gmi.GMI = 0 THEN 0 ELSE cv.coefficient_of_variation END AS glucose_variability, CASE WHEN cv.coefficient_of_variation = 0 OR stats.num_days = 0 OR percentage_active.percentage_active = 0 OR mean_glucose.mean_glucose = 0 OR gmi.GMI = 0 THEN 0 ELSE stats.num_days END AS number_of_days_cgm_worn, CASE WHEN cv.coefficient_of_variation = 0 OR stats.num_days = 0 OR percentage_active.percentage_active = 0 OR mean_glucose.mean_glucose = 0 OR gmi.GMI = 0 THEN 0 ELSE percentage_active.percentage_active END AS percentage_of_time_cgm_active, CASE WHEN cv.coefficient_of_variation = 0 OR stats.num_days = 0 OR percentage_active.percentage_active = 0 OR mean_glucose.mean_glucose = 0 OR gmi.GMI = 0 THEN 0 ELSE mean_glucose.mean_glucose END AS mean_glucose, CASE WHEN cv.coefficient_of_variation = 0 OR stats.num_days = 0 OR percentage_active.percentage_active = 0 OR mean_glucose.mean_glucose = 0 OR gmi.GMI = 0 THEN 0 ELSE gmi.GMI END AS glucose_management_indicator FROM cv, stats, percentage_active, mean_glucose, gmi"
    public static final String
    time_range_stacked_data_query
    "WITH GlucoseMetrics AS ( SELECT participant_id, COUNT(*) AS total_readings, SUM(CASE WHEN CGM_Value BETWEEN 54 AND 69 THEN 1 ELSE 0 END) AS time_below_range_low, SUM(CASE WHEN CGM_Value < 54 THEN 1 ELSE 0 END) AS time_below_range_very_low, SUM(CASE WHEN CGM_Value BETWEEN 70 AND 180 THEN 1 ELSE 0 END) AS time_in_range, SUM(CASE WHEN CGM_Value > 250 THEN 1 ELSE 0 END) AS time_above_vh, SUM(CASE WHEN CGM_Value BETWEEN 181 AND 250 THEN 1 ELSE 0 END) AS time_above_range_high FROM {dbName}.combined_cgm_tracing_cached WHERE participant_id = ? AND DATE(Date_Time) BETWEEN ? AND ? GROUP BY participant_id ), Defaults AS ( SELECT 0 AS total_readings, 0 AS time_below_range_low, 0 AS time_below_range_very_low, 0 AS time_in_range, 0 AS time_above_vh, 0 AS time_above_range_high ) SELECT COALESCE(CASE WHEN gm.total_readings = 0 THEN 0 ELSE (gm.time_below_range_low * 100.0 / gm.total_readings) END, 0) AS time_below_range_low_percentage, COALESCE(gm.time_below_range_low, 0) AS time_below_range_low, COALESCE(CASE WHEN gm.total_readings = 0 THEN \'00 hours, 00 minutes\' ELSE printf(\'%02d hours, %02d minutes\', (gm.time_below_range_low * 5) / 60, (gm.time_below_range_low * 5) % 60) END, \'00 hours, 00 minutes\') AS time_below_range_low_string, COALESCE(CASE WHEN gm.total_readings = 0 THEN 0 ELSE (gm.time_below_range_very_low * 100.0 / gm.total_readings) END, 0) AS time_below_range_very_low_percentage, COALESCE(gm.time_below_range_very_low, 0) AS time_below_range_very_low, COALESCE(CASE WHEN gm.total_readings = 0 THEN \'00 hours, 00 minutes\' ELSE printf(\'%02d hours, %02d minutes\', (gm.time_below_range_very_low * 5) / 60, (gm.time_below_range_very_low * 5) % 60) END, \'00 hours, 00 minutes\') AS time_below_range_very_low_string, COALESCE(CASE WHEN gm.total_readings = 0 THEN 0 ELSE (gm.time_in_range * 100.0 / gm.total_readings) END, 0) AS time_in_range_percentage, COALESCE(gm.time_in_range, 0) AS time_in_range, COALESCE(CASE WHEN gm.total_readings = 0 THEN \'00 hours, 00 minutes\' ELSE printf(\'%02d hours, %02d minutes\', (gm.time_in_range * 5) / 60, (gm.time_in_range * 5) % 60) END, \'00 hours, 00 minutes\') AS time_in_range_string, COALESCE(CASE WHEN gm.total_readings = 0 THEN 0 ELSE (gm.time_above_vh * 100.0 / gm.total_readings) END, 0) AS time_above_vh_percentage, COALESCE(gm.time_above_vh, 0) AS time_above_vh, COALESCE(CASE WHEN gm.total_readings = 0 THEN \'00 hours, 00 minutes\' ELSE printf(\'%02d hours, %02d minutes\', (gm.time_above_vh * 5) / 60, (gm.time_above_vh * 5) % 60) END, \'00 hours, 00 minutes\') AS time_above_vh_string, COALESCE(CASE WHEN gm.total_readings = 0 THEN 0 ELSE (gm.time_above_range_high * 100.0 / gm.total_readings) END, 0) AS time_above_range_high_percentage, COALESCE(gm.time_above_range_high, 0) AS time_above_range_high, COALESCE(CASE WHEN gm.total_readings = 0 THEN \'00 hours, 00 minutes\' ELSE printf(\'%02d hours, %02d minutes\', (gm.time_above_range_high * 5) / 60, (gm.time_above_range_high * 5) % 60) END, \'00 hours, 00 minutes\') AS time_above_range_high_string FROM Defaults d LEFT JOIN GlucoseMetrics gm ON 1=1;"