From e1fafe113d8f1941b5e2294b70f2a9f004e73efa Mon Sep 17 00:00:00 2001 From: gamesguru Date: Tue, 29 Sep 2020 16:54:09 -0400 Subject: [PATCH] functions --- sql/functions.sql | 150 ++++++++++++++++++++-------------------------- 1 file changed, 64 insertions(+), 86 deletions(-) diff --git a/sql/functions.sql b/sql/functions.sql index a0d4a4e..c77d7e3 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -63,7 +63,6 @@ WHERE OR wrist.value OR ankle.value; - -------------------------------- -- Measurements (cm) -------------------------------- @@ -71,105 +70,84 @@ WHERE SELECT date, users.name, - chest.value AS chest - -- thigh.value AS thigh, - -- calf.value AS calf, - -- shoulders.value AS shoulders, - -- waist.value AS waist, - -- hips.value AS hips, - -- neck.value AS neck, - -- forearm.value AS forearm + chest.value / 2.54 AS chest, + arm.value / 2.54 AS arm, + thigh.value / 2.54 AS thigh, + calf.value / 2.54 AS calf, + shoulders.value / 2.54 AS shoulders, + waist.value / 2.54 AS waist, + hips.value / 2.54 AS hips, + neck.value / 2.54 AS neck, + forearm.value / 2.54 AS forearm FROM biometric_log INNER JOIN users ON user_id = users.id LEFT JOIN bio_log_entry chest ON chest.biometric_id = 5 AND chest.log_id = biometric_log.id - WHERE - chest.value; - + LEFT JOIN bio_log_entry arm ON arm.biometric_id = 6 + AND arm.log_id = biometric_log.id + LEFT JOIN bio_log_entry thigh ON thigh.biometric_id = 7 + AND thigh.log_id = biometric_log.id + LEFT JOIN bio_log_entry calf ON calf.biometric_id = 8 + AND calf.log_id = biometric_log.id + LEFT JOIN bio_log_entry shoulders ON shoulders.biometric_id = 9 + AND shoulders.log_id = biometric_log.id + LEFT JOIN bio_log_entry waist ON waist.biometric_id = 10 + AND waist.log_id = biometric_log.id + LEFT JOIN bio_log_entry hips ON hips.biometric_id = 11 + AND hips.log_id = biometric_log.id + LEFT JOIN bio_log_entry neck ON neck.biometric_id = 12 + AND neck.log_id = biometric_log.id + LEFT JOIN bio_log_entry forearm ON forearm.biometric_id = 13 + AND forearm.log_id = biometric_log.id +WHERE + chest.value + OR arm.value + OR thigh.value + OR calf.value + OR shoulders.value + OR waist.value + OR hips.value + OR neck.value + OR forearm.value; -------------------------------- -- Skinfolds (mm) -------------------------------- --------------------------------- --- OLD: pulse/bp --------------------------------- - -SELECT - date, - users.name, - ( - SELECT - value - FROM - bio_log_entry - WHERE - biometric_id = 23 - AND log_id = biometric_log.id) AS sys, - ( - SELECT - value - FROM - bio_log_entry - WHERE - biometric_id = 24 - AND log_id = biometric_log.id) AS dia, - ( - SELECT - value - FROM - bio_log_entry - WHERE - biometric_id = 22 - AND log_id = biometric_log.id) AS pulse, - notes -FROM - biometric_log - INNER JOIN users ON user_id = users.id; - --- LEFT JOIN bio_log_entry ON biometric_id IN (22,23,24); SELECT date, users.name, - notes, - ( - SELECT - ( - SELECT - value - FROM - bio_log_entry - WHERE - biometric_id = 23 - AND log_id = biometric_log.id) || '/' || ( - SELECT - value - FROM - bio_log_entry - WHERE - biometric_id = 24 - AND log_id = biometric_log.id) || ' (' || ( - SELECT - value - FROM - bio_log_entry - WHERE - biometric_id = 22 - AND log_id = biometric_log.id) || ' bpm)') AS summary - FROM - biometric_log - INNER JOIN users ON user_id = users.id; - -SELECT - users.name, - date, - notes, - biometrics.name, - bio_log_entry.value + CAST(pectoral.value AS int) AS pec, + CAST(abdominal.value AS int) AS ab, + CAST(quadricep.value AS int) AS quad, + CAST(midaxillar.value AS int) AS midax, + CAST(subscapular.value AS int) AS sub, + CAST(tricep.value AS int) AS tricep, + CAST(suprailiac.value AS int) AS supra FROM biometric_log INNER JOIN users ON user_id = users.id - INNER JOIN bio_log_entry ON log_id = biometric_log.id - INNER JOIN biometrics ON biometric_id = biometrics.id; + LEFT JOIN bio_log_entry pectoral ON pectoral.biometric_id = 14 + AND pectoral.log_id = biometric_log.id + LEFT JOIN bio_log_entry abdominal ON abdominal.biometric_id = 15 + AND abdominal.log_id = biometric_log.id + LEFT JOIN bio_log_entry quadricep ON quadricep.biometric_id = 16 + AND quadricep.log_id = biometric_log.id + LEFT JOIN bio_log_entry midaxillar ON midaxillar.biometric_id = 17 + AND midaxillar.log_id = biometric_log.id + LEFT JOIN bio_log_entry subscapular ON subscapular.biometric_id = 18 + AND subscapular.log_id = biometric_log.id + LEFT JOIN bio_log_entry tricep ON tricep.biometric_id = 19 + AND tricep.log_id = biometric_log.id + LEFT JOIN bio_log_entry suprailiac ON suprailiac.biometric_id = 20 + AND suprailiac.log_id = biometric_log.id +WHERE + pectoral.value + OR abdominal.value + OR quadricep.value + OR midaxillar.value + OR subscapular.value + OR tricep.value + OR suprailiac.value; -- 2.52.0