From f33cc7ea042981a4d1d84028a2b100b9897bda10 Mon Sep 17 00:00:00 2001
From: alex <alex@pdp7.net>
Date: Thu, 15 May 2025 22:13:49 +0200
Subject: Add reporting.aggregate_standard_bp_measurements view

---
 weight/schema.sql | 20 ++++++++++++++++++++
 1 file changed, 20 insertions(+)

(limited to 'weight/schema.sql')

diff --git a/weight/schema.sql b/weight/schema.sql
index 80a8adcb..b2f3b71a 100644
--- a/weight/schema.sql
+++ b/weight/schema.sql
@@ -59,6 +59,26 @@ insert into zqxjk._tables(name, default_sort) values ('pressure_medication', '{"
 
 create schema reporting;
 
+create view reporting.aggregate_standard_bp_measurements as (
+  with daily_standard_bp_measurements as (
+    select date_trunc('day', bp.measured_at)::date as day_measured,
+           systolic,
+           diastolic
+      from weight.bp
+     where bp.kind = 'standard'
+  )
+  select daily_standard_bp_measurements.*,
+         'all' as kind
+    from daily_standard_bp_measurements
+   union
+  select day_measured,
+         min(systolic) as systolic,
+         min(diastolic) as diastolic,
+         'daily_minimum' as kind
+    from daily_standard_bp_measurements
+   group by day_measured
+);
+
 create view reporting.weekly_blood_pressure as (
   with limits as (
     select date_trunc('week', min(taken_at)) as min_week,
-- 
cgit v1.2.3

