From 90bac161ca0617423a3c31f62073a1ace0bfdad8 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 3 Oct 2025 16:36:59 +0000 Subject: [PATCH] Convert FOLLOW_STUDIES --- conversion/load_data.sql | 45 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 45 insertions(+) diff --git a/conversion/load_data.sql b/conversion/load_data.sql index 207cc9f..085a0bd 100644 --- a/conversion/load_data.sql +++ b/conversion/load_data.sql @@ -281,6 +281,51 @@ SELECT upper( -- problem #31) FROM clean.follow ORDER BY fol_b_animid, fol_date; +-- +-- follow -> FOLLOW_STUDIES +-- +-- Load all the new studies into STUDIES +INSERT INTO studies (study, description) + WITH new_studies AS ( + SELECT UPPER(follow.fol_study_code_1) AS study + FROM clean.follow + WHERE follow.fol_study_code_1 IS NOT NULL + GROUP BY UPPER(follow.fol_study_code_1) + UNION + SELECT UPPER(follow.fol_study_code_2) AS study + FROM clean.follow + WHERE follow.fol_study_code_2 IS NOT NULL + GROUP BY UPPER(follow.fol_study_code_2) + ) + SELECT new_studies.study, new_studies.study + FROM new_studies + GROUP BY new_studies.study + ON CONFLICT (study) + DO NOTHING; + +-- follow.fol_study_code_1 -> FOLLOW_STUDIES +INSERT INTO follow_studies (fid, code) + SELECT + (SELECT follows.fid + FROM follows + WHERE follows.focal = follow.fol_b_animid + AND follows.date = follow.fol_date) + , UPPER(follow.fol_study_code_1) + FROM clean.follow + WHERE fol_study_code_1 IS NOT NULL; + +-- follow.fol_study_code_2 -> FOLLOW_STUDIES +INSERT INTO follow_studies (fid, code) + SELECT + (SELECT follows.fid + FROM follows + WHERE follows.focal = follow.fol_b_animid + AND follows.date = follow.fol_date) + , UPPER(follow.fol_study_code_2) + FROM clean.follow + WHERE fol_study_code_2 IS NOT NULL; + + -- -- follow -> FOLLOW_OBSERVERS -- -- 2.34.1