text: -- Databricks notebook source
  -- MAGIC %md
  -- MAGIC ## CATEGORISE BMI JOURNALS
  
  -- COMMAND ----------
  
  -- create table with only BMI journals in
  CREATE OR REPLACE TEMPORARY VIEW BMI_JOURNALS_2 AS
  SELECT a.*
  , b.ConceptId_Description
  , b.BMI_CAT
  FROM gdppr_database.gdppr_table AS a
  LEFT JOIN ref_data.BMI_CODES AS b
  ON a.CODE = b.ConceptID
  WHERE a.CODE IN (SELECT DISTINCT ConceptID FROM ref_data.BMI_CODES)
  
  -- COMMAND ----------
  
  -- add categories on to BMI journals
  -- add age at time of journal date (allows removal of under 18's)
  CREATE OR REPLACE TEMPORARY VIEW BMI_JOURNALS_CAT_2 AS
  SELECT *
  , CASE WHEN BMI_CAT = 'ALL' AND VALUE1_CONDITION IS NULL THEN 'INDETERMINABLE'
              WHEN BMI_CAT = 'ALL' AND VALUE1_CONDITION >= 10 AND VALUE1_CONDITION < 18.5 THEN 'UNDERWEIGHT'
              WHEN BMI_CAT = 'ALL' AND VALUE1_CONDITION >= 18.5 AND VALUE1_CONDITION < 25 THEN 'IDEAL'
              WHEN BMI_CAT = 'ALL' AND VALUE1_CONDITION >= 25 AND VALUE1_CONDITION < 30 THEN 'OVERWEIGHT'
              WHEN BMI_CAT = 'ALL' AND VALUE1_CONDITION >= 30 AND VALUE1_CONDITION <= 55 THEN 'OBESE'
              WHEN BMI_CAT = 'ALL' AND VALUE1_CONDITION > 55 AND VALUE1_CONDITION <= 185  THEN 'INDETERMINABLE'  -- needs further investigation
              WHEN BMI_CAT = 'ALL' AND VALUE1_CONDITION < 10 THEN 'INDETERMINABLE' 
              WHEN BMI_CAT = 'ALL' AND VALUE1_CONDITION > 185 THEN 'INDETERMINABLE' 
              WHEN BMI_CAT = 'UNDERWEIGHT' AND VALUE1_CONDITION IS NULL THEN 'UNDERWEIGHT'
              WHEN BMI_CAT = 'UNDERWEIGHT' AND VALUE1_CONDITION = 0 THEN 'UNDERWEIGHT'
              WHEN BMI_CAT = 'IDEAL' AND VALUE1_CONDITION IS NULL THEN 'IDEAL'
              WHEN BMI_CAT = 'IDEAL' AND VALUE1_CONDITION = 0 THEN 'IDEAL'
              WHEN BMI_CAT = 'IDEAL' AND VALUE1_CONDITION >= 18.5 AND VALUE1_CONDITION < 25 THEN 'IDEAL'
              WHEN BMI_CAT = 'UNDER_IDEAL_BORDER' AND VALUE1_CONDITION IS NULL THEN 'UNDER 20 NULLS'
              WHEN BMI_CAT = 'UNDER_IDEAL_BORDER' AND VALUE1_CONDITION = 0 THEN 'UNDER 20 NULLS'
              WHEN BMI_CAT = 'UNDER_IDEAL_BORDER' AND VALUE1_CONDITION >= 10 AND VALUE1_CONDITION < 18.5 THEN 'UNDERWEIGHT'
              WHEN BMI_CAT = 'UNDER_IDEAL_BORDER' AND VALUE1_CONDITION >= 18.5 AND VALUE1_CONDITION <= 20 THEN 'IDEAL'
              WHEN BMI_CAT = 'OVERWEIGHT' AND VALUE1_CONDITION IS NULL THEN 'OVERWEIGHT'
              WHEN BMI_CAT = 'OVERWEIGHT' AND VALUE1_CONDITION = 0 THEN 'OVERWEIGHT'
              WHEN BMI_CAT = 'OVERWEIGHT' AND VALUE1_CONDITION >= 25 AND VALUE1_CONDITION < 30 THEN 'OVERWEIGHT'
              WHEN BMI_CAT = 'OBESE' AND VALUE1_CONDITION IS NULL THEN 'OBESE'
              WHEN BMI_CAT = 'OBESE' AND VALUE1_CONDITION = 0 THEN 'OBESE'
              WHEN BMI_CAT = 'OBESE' AND VALUE1_CONDITION >= 30 AND VALUE1_CONDITION < 55 THEN 'OBESE'
              WHEN BMI_CAT = 'UNSUITABLE/DECLINED' THEN 'UNSUITABLE/DECLINED'
              WHEN BMI_CAT = 'UNKNOWN' THEN 'INDETERMINABLE'
              WHEN BMI_CAT = 'CHILD' THEN 'CHILD'
              ELSE 'INDETERMINABLE'
              END AS BMI_CATEGORISED_INITIAL
  , (DATEDIFF(DATE, DATE_OF_BIRTH))/365.25 AS AGE_AT_TIME_OF_JOURNAL
  FROM BMI_JOURNALS_2
  
  -- COMMAND ----------
  
  -- create table with no unknown BMIs in and only patients over 18 at the time of their BMI recording
  CREATE OR REPLACE TEMPORARY VIEW BMI_CATEGORY_NO_UNKNOWNS_2 AS
  SELECT * 
  FROM BMI_JOURNALS_CAT_2
  WHERE BMI_CATEGORISED_INITIAL NOT IN ("INDETERMINABLE", "UNSUITABLE/DECLINED", "CHILD")
  AND AGE_AT_TIME_OF_JOURNAL >= 18
  
  -- COMMAND ----------
  
  -- MAGIC %md
  -- MAGIC ## PULL TOGETHER HEIGHT AND WEIGHT JOURNALS
  
  -- COMMAND ----------
  
  -- create table with only height journals in
  CREATE OR REPLACE TEMPORARY VIEW HEIGHT_JOURNALS_2 AS
  SELECT a.*
  , b.ConceptId_Description
  FROM gdppr_database.gdppr_table AS a
  LEFT JOIN ref_data.HEIGHT_CODES AS b
  ON a.CODE = b.ConceptID
  WHERE a.CODE IN (SELECT DISTINCT ConceptID FROM ref_data.HEIGHT_CODES)
  
  -- COMMAND ----------
  
  -- create table with only weight journals in
  CREATE OR REPLACE TEMPORARY VIEW WEIGHT_JOURNALS_2 AS
  SELECT a.*
  , b.ConceptId_Description
  FROM gdppr_database.gdppr_table AS a
  LEFT JOIN ref_data.WEIGHT_CODES AS b
  ON a.CODE = b.ConceptID
  WHERE a.CODE IN (SELECT DISTINCT ConceptID FROM ref_data.WEIGHT_CODES)
  
  -- COMMAND ----------
  
  -- join height and weight journals together for journals for the same patient which were recorded on the same date
  -- join on several other patient factors
  -- calculate age at time of journal to allow removal of under 18s
  CREATE OR REPLACE TEMPORARY VIEW HW_JOINT_2 AS
  SELECT a.*
  , (DATEDIFF(a.DATE, a.DATE_OF_BIRTH))/365.25 AS AGE_AT_TIME_OF_JOURNAL
  , CASE WHEN a.VALUE1_CONDITION >=0 AND a.VALUE1_CONDITION <=3 THEN a.VALUE1_CONDITION * 100
  ELSE a.VALUE1_CONDITION END AS H_VALUE
  , b.RECORD_DATE AS W_RECORD_DATE
  , b.CODE AS W_CODE
  , b.VALUE1_CONDITION AS W_VALUE
  , b.LINKS AS W_LINKS
  FROM HEIGHT_JOURNALS_2 AS a
  INNER JOIN WEIGHT_JOURNALS_2 AS b
  ON a.NHS_NUMBER = b.NHS_NUMBER
  AND a.DATE = b.DATE
  AND a.PRACTICE = b.PRACTICE
  AND a.REPORTING_PERIOD_END_DATE = b.REPORTING_PERIOD_END_DATE
  AND a.JOURNAL_REPORTING_PERIOD_END_DATE = b.JOURNAL_REPORTING_PERIOD_END_DATE
  WHERE a.VALUE1_CONDITION IS NOT NULL
  AND b.VALUE1_CONDITION IS NOT NULL
  
  -- COMMAND ----------
  
  -- calculate BMI 
  -- remove journals for under 18s at the time of their BMI recording
  CREATE OR REPLACE TEMPORARY VIEW HW_ADULT_2 AS
  SELECT * 
  , W_VALUE / POWER((H_VALUE/100),2) AS BMI
  FROM HW_JOINT_2
  WHERE AGE_AT_TIME_OF_JOURNAL >= 18
  
  -- COMMAND ----------
  
  -- categorise BMIs
  CREATE OR REPLACE TEMPORARY VIEW HEIGHT_WEIGHT_CATEGORISED_2 AS
  SELECT *
  , CASE WHEN BMI IS NULL THEN 'INDETERMINABLE'
              WHEN BMI >= 10 AND BMI < 18.5 THEN 'UNDERWEIGHT'
              WHEN BMI >= 18.5 AND BMI < 25 THEN 'IDEAL'
              WHEN BMI >= 25 AND BMI < 30 THEN 'OVERWEIGHT'
              WHEN BMI >= 30 AND BMI <= 55 THEN 'OBESE'
              WHEN BMI > 55 AND BMI <= 185  THEN 'INDETERMINABLE'  -- needs further investigation
              WHEN BMI < 10 THEN 'INDETERMINABLE' 
              ELSE 'INDETERMINABLE' END AS BMI_CATEGORISED_INITIAL
  FROM HW_ADULT_2
  
  -- COMMAND ----------
  
  -- create table with no unknown BMIs in
  CREATE OR REPLACE TEMPORARY VIEW HEIGHT_WEIGHT_NO_UNKNOWN_2 AS
  SELECT *
  FROM HEIGHT_WEIGHT_CATEGORISED_2
  WHERE BMI_CATEGORISED_INITIAL NOT IN ("INDETERMINABLE", "UNSUITABLE/DECLINED", "CHILD")
  
  -- COMMAND ----------
  
  -- MAGIC %md
  -- MAGIC # ADD IN CHILD JOURNALS
  
  -- COMMAND ----------
  
  -- create table of BMI journals where age at time of measurement was <18
  CREATE OR REPLACE TEMPORARY VIEW CHILD_BMI_JOURNALS AS
  SELECT *
  FROM BMI_JOURNALS_2
  WHERE DATEDIFF(DATE, DATE_OF_BIRTH)/365.25 < 18
  
  -- COMMAND ----------
  
  -- add categories on to BMI journals - anything already categorised leave as is (trust clinicians)
  -- add age at time of journal date to allow calculation of centile for those that need it
  CREATE OR REPLACE TEMPORARY VIEW CHILD_BMI_JOURNALS_CAT AS
  SELECT *
  , CASE WHEN BMI_CAT = 'ALL' AND VALUE1_CONDITION IS NULL THEN 'INDETERMINABLE'
              WHEN BMI_CAT = 'ALL' AND VALUE1_CONDITION IS NOT NULL THEN 'CENTILE'
              WHEN BMI_CAT = 'UNDERWEIGHT' AND VALUE1_CONDITION IS NULL THEN 'UNDERWEIGHT'
              WHEN BMI_CAT = 'UNDERWEIGHT' AND VALUE1_CONDITION = 0 THEN 'UNDERWEIGHT'
              WHEN BMI_CAT = 'UNDERWEIGHT' AND VALUE1_CONDITION IS NOT NULL AND VALUE1_CONDITION != 0 THEN 'UNDERWEIGHT'
              WHEN BMI_CAT = 'IDEAL' AND VALUE1_CONDITION IS NULL THEN 'IDEAL'
              WHEN BMI_CAT = 'IDEAL' AND VALUE1_CONDITION = 0 THEN 'IDEAL'
              WHEN BMI_CAT = 'IDEAL' AND VALUE1_CONDITION IS NOT NULL AND VALUE1_CONDITION != 0 THEN 'CENTILE'
              WHEN BMI_CAT = 'UNDER_IDEAL_BORDER' AND VALUE1_CONDITION IS NULL THEN 'UNDER 20 NULLS'
              WHEN BMI_CAT = 'UNDER_IDEAL_BORDER' AND VALUE1_CONDITION = 0 THEN 'UNDER 20 NULLS'
              WHEN BMI_CAT = 'UNDER_IDEAL_BORDER' AND VALUE1_CONDITION IS NOT NULL AND VALUE1_CONDITION != 0 THEN 'CENTILE'
              WHEN BMI_CAT = 'OVERWEIGHT' AND VALUE1_CONDITION IS NULL THEN 'OVERWEIGHT'
              WHEN BMI_CAT = 'OVERWEIGHT' AND VALUE1_CONDITION = 0 THEN 'OVERWEIGHT'
              WHEN BMI_CAT = 'OVERWEIGHT' AND VALUE1_CONDITION IS NOT NULL AND VALUE1_CONDITION != 0 THEN 'CENTILE'
              WHEN BMI_CAT = 'OBESE' AND VALUE1_CONDITION IS NULL THEN 'OBESE'
              WHEN BMI_CAT = 'OBESE' AND VALUE1_CONDITION = 0 THEN 'OBESE'
              WHEN BMI_CAT = 'OBESE' AND VALUE1_CONDITION IS NOT NULL AND VALUE1_CONDITION != 0 THEN 'CENTILE'
              WHEN BMI_CAT = 'UNSUITABLE/DECLINED' THEN 'UNSUITABLE/DECLINED'
              WHEN BMI_CAT = 'UNKNOWN' THEN 'INDETERMINABLE'
              WHEN BMI_CAT = 'CHILD' THEN 'CHILD'
              ELSE 'INDETERMINABLE'
              END AS BMI_CATEGORISED_INITIAL
  FROM CHILD_BMI_JOURNALS
  
  -- COMMAND ----------
  
  -- categorise child centile codes according to centile boundaries
  CREATE OR REPLACE TEMPORARY VIEW CHILD_CENTILE_CODES AS
  SELECT *
  , CASE WHEN ConceptID IN ('920141000000102', '920161000000101', '920181000000105') THEN 'UNDERWEIGHT'
         WHEN ConceptID IN ('920971000000105', '920991000000109') THEN 'OVERWEIGHT'
         WHEN ConceptID IN ('921011000000105', '921031000000102', '921051000000109') THEN 'OBESE'
         ELSE 'IDEAL'
         END AS BMI_CATEGORISED_INITIAL
  FROM ref_data.BMI_CODES
  WHERE BMI_CAT = 'CHILD'
  
  -- COMMAND ----------
  
  -- attach child BMI category onto child BMI centile journals
  CREATE OR REPLACE TEMPORARY VIEW CHILD_CENTILES AS
  SELECT a.*
  , b. BMI_CATEGORISED_INITIAL
  , DATEDIFF(a.DATE, a.DATE_OF_BIRTH)/365.25 AS AGE_AT_TIME_OF_JOURNAL
  FROM CHILD_BMI_JOURNALS AS a
  LEFT JOIN CHILD_CENTILE_CODES AS b
  ON a.CODE = b.ConceptID
  WHERE a.BMI_CAT = 'CHILD'
  
  -- COMMAND ----------
  
  -- get BMI journals which need centiles calucalting
  CREATE OR REPLACE TEMPORARY VIEW BMI_CENTILE_JOURNALS AS
  SELECT * 
  , (DATEDIFF(DATE, DATE_OF_BIRTH)/365.25) * 12 AS AGE_MONTHS
  FROM CHILD_BMI_JOURNALS_CAT
  WHERE BMI_CATEGORISED_INITIAL = 'CENTILE'
  
  -- COMMAND ----------
  
  -- calc upper and lower age (months)
  CREATE OR REPLACE TEMPORARY VIEW BMI_CENTILE_CALC AS 
  SELECT *
  , FLOOR(AGE_MONTHS) AS AGE_LOWER
  , CEIL(AGE_MONTHS) AS AGE_UPPER
  , AGE_MONTHS - FLOOR(AGE_MONTHS) AS AGE_DIFF
  FROM BMI_CENTILE_JOURNALS
  
  -- COMMAND ----------
  
  -- pull in LMS values based on sex and age
  CREATE OR REPLACE TEMPORARY VIEW BMI_CENTILE_CALC_2 AS 
  SELECT a.*
  , CASE WHEN a.SEX = 1 THEN b.BOYS_L
         WHEN a.SEX = 2 THEN b.GIRLS_L
         ELSE null END AS L_LOW
  , CASE WHEN a.SEX = 1 THEN b.BOYS_M
         WHEN a.SEX = 2 THEN b.GIRLS_M
         ELSE null END AS M_LOW
  , CASE WHEN a.SEX = 1 THEN b.BOYS_S
         WHEN a.SEX = 2 THEN b.GIRLS_S
         ELSE null END AS S_LOW
  , CASE WHEN a.SEX = 1 THEN c.BOYS_L
         WHEN a.SEX = 2 THEN c.GIRLS_L
         ELSE null END AS L_HIGH
  , CASE WHEN a.SEX = 1 THEN c.BOYS_M
         WHEN a.SEX = 2 THEN c.GIRLS_M
         ELSE null END AS M_HIGH
  , CASE WHEN a.SEX = 1 THEN c.BOYS_S
         WHEN a.SEX = 2 THEN c.GIRLS_S
         ELSE null END AS S_HIGH
  FROM BMI_CENTILE_CALC AS a
  LEFT JOIN ref_data.child_bmi_centiles_ordered AS b
  ON a.AGE_LOWER = b.Months_Calculated
  LEFT JOIN ref_data.child_bmi_centiles_ordered AS c
  ON a.AGE_UPPER = c.Months_Calculated
  
  -- COMMAND ----------
  
  -- calculate LMS values to use
  CREATE OR REPLACE TEMPORARY VIEW BMI_CENTILE_CALC_3 AS
  SELECT * 
  , L_LOW + (L_HIGH - L_LOW) * AGE_DIFF AS L_TO_USE
  , M_LOW + (M_HIGH - M_LOW) * AGE_DIFF AS M_TO_USE
  , S_LOW + (S_HIGH - S_LOW) * AGE_DIFF AS S_TO_USE
  FROM BMI_CENTILE_CALC_2
  
  -- COMMAND ----------
  
  -- calculate z score
  CREATE OR REPLACE TEMPORARY VIEW BMI_CENTILE_CALC_4 AS
  SELECT *
  , VALUE1_CONDITION AS BMI
  , (POWER((VALUE1_CONDITION/M_TO_USE), L_TO_USE) - 1) / (L_TO_USE * S_TO_USE) AS BMI_Z_SCORE
  FROM BMI_CENTILE_CALC_3
  
  -- COMMAND ----------
  
  -- categorise bmi z score
  CREATE OR REPLACE TEMPORARY VIEW BMI_CENTILE_CALC_5 AS
  SELECT *
  , DATEDIFF(DATE, DATE_OF_BIRTH)/365.25 AS AGE_AT_TIME_OF_JOURNAL
  , CASE WHEN BMI_Z_SCORE <=-2 THEN "UNDERWEIGHT"
         WHEN BMI_Z_SCORE >-2 AND BMI_Z_SCORE <(4/3) THEN "IDEAL"
         WHEN BMI_Z_SCORE >= (4/3) AND BMI_Z_SCORE <2 THEN "OVERWEIGHT"
         WHEN BMI_Z_SCORE >=2 THEN "OBESE"
         ELSE "ERROR"
         END AS CENTILE_CLINICAL_BMI_CAT
  FROM BMI_CENTILE_CALC_4
  
  -- COMMAND ----------
  
  -- MAGIC %md
  -- MAGIC ## CHILD HEIGHT AND WEIGHT
  
  -- COMMAND ----------
  
  -- join height and weight journals together for journals which were recorded on the same date
  -- join on several other patient factors
  -- calculate age at time of journal to allow removal of over 18s
  CREATE OR REPLACE TEMPORARY VIEW HW_JOINT_2_CHILD AS
  SELECT a.*
  , DATEDIFF(a.DATE, a.DATE_OF_BIRTH)/365.25 AS AGE_AT_TIME_OF_JOURNAL
  , CASE WHEN a.VALUE1_CONDITION >=0 AND a.VALUE1_CONDITION <=3 THEN a.VALUE1_CONDITION * 100
  ELSE a.VALUE1_CONDITION END AS H_VALUE
  , b.RECORD_DATE AS W_RECORD_DATE
  , b.CODE AS W_CODE
  , b.VALUE1_CONDITION AS W_VALUE
  FROM HEIGHT_JOURNALS_2 AS a
  INNER JOIN WEIGHT_JOURNALS_2 AS b
  ON a.NHS_NUMBER = b.NHS_NUMBER
  AND a.DATE = b.DATE
  AND a.PRACTICE = b.PRACTICE
  AND a.REPORTING_PERIOD_END_DATE = b.REPORTING_PERIOD_END_DATE
  AND a.JOURNAL_REPORTING_PERIOD_END_DATE = b.JOURNAL_REPORTING_PERIOD_END_DATE
  WHERE a.VALUE1_CONDITION IS NOT NULL
  AND a.VALUE1_CONDITION >0
  AND a.VALUE1_CONDITION <= 200
  AND b.VALUE1_CONDITION IS NOT NULL
  AND (DATEDIFF(a.DATE, a.DATE_OF_BIRTH))/365.25 <18
  
  -- COMMAND ----------
  
  -- calculate BMI
  -- remove journals for over 18s at the time of their BMI recording
  CREATE OR REPLACE TEMPORARY VIEW HW_CHILD AS
  SELECT * 
  , W_VALUE / POWER((H_VALUE/100),2) AS BMI
  FROM HW_JOINT_2_CHILD
  
  -- COMMAND ----------
  
  -- MAGIC %md
  -- MAGIC ### CENTILES
  
  -- COMMAND ----------
  
  -- select certain fields, add in age (months)
  CREATE OR REPLACE TEMPORARY VIEW CHILD_CENTILE_CALC AS 
  SELECT DATE_OF_BIRTH
  , SEX
  , NHS_NUMBER
  , ETHNIC
  , PRACTICE
  , GP_SYSTEM_SUPPLIER
  , REPORTING_PERIOD_END_DATE
  , JOURNAL_REPORTING_PERIOD_END_DATE
  , DATE
  , RECORD_DATE
  , AGE_AT_TIME_OF_JOURNAL
  , H_VALUE
  , W_VALUE
  , BMI
  , (DATEDIFF(DATE, DATE_OF_BIRTH)/365.25) * 12 AS AGE_MONTHS
  FROM HW_CHILD
  
  -- COMMAND ----------
  
  -- calculate age lower and upper (months)
  CREATE OR REPLACE TEMPORARY VIEW CHILD_CENTILE_CALC_2 AS 
  SELECT *
  , FLOOR(AGE_MONTHS) AS AGE_LOWER
  , CEIL(AGE_MONTHS) AS AGE_UPPER
  , AGE_MONTHS - FLOOR(AGE_MONTHS) AS AGE_DIFF
  FROM CHILD_CENTILE_CALC
  
  -- COMMAND ----------
  
  -- pull in LMS based on age and sex
  CREATE OR REPLACE TEMPORARY VIEW CHILD_CENTILE_CALC_3 AS 
  SELECT a.*
  , CASE WHEN a.SEX = 1 THEN b.BOYS_L
         WHEN a.SEX = 2 THEN b.GIRLS_L
         ELSE null END AS L_LOW
  , CASE WHEN a.SEX = 1 THEN b.BOYS_M
         WHEN a.SEX = 2 THEN b.GIRLS_M
         ELSE null END AS M_LOW
  , CASE WHEN a.SEX = 1 THEN b.BOYS_S
         WHEN a.SEX = 2 THEN b.GIRLS_S
         ELSE null END AS S_LOW
  , CASE WHEN a.SEX = 1 THEN c.BOYS_L
         WHEN a.SEX = 2 THEN c.GIRLS_L
         ELSE null END AS L_HIGH
  , CASE WHEN a.SEX = 1 THEN c.BOYS_M
         WHEN a.SEX = 2 THEN c.GIRLS_M
         ELSE null END AS M_HIGH
  , CASE WHEN a.SEX = 1 THEN c.BOYS_S
         WHEN a.SEX = 2 THEN c.GIRLS_S
         ELSE null END AS S_HIGH
  FROM CHILD_CENTILE_CALC_2 AS a
  LEFT JOIN ref_data.child_bmi_centiles_ordered AS b
  ON a.AGE_LOWER = b.Months_Calculated
  LEFT JOIN ref_data.child_bmi_centiles_ordered AS c
  ON a.AGE_UPPER = c.Months_Calculated
  
  -- COMMAND ----------
  
  -- calculate LMS to use
  CREATE OR REPLACE TEMPORARY VIEW CHILD_CENTILE_CALC_4 AS
  SELECT * 
  , L_LOW + (L_HIGH - L_LOW) * AGE_DIFF AS L_TO_USE
  , M_LOW + (M_HIGH - M_LOW) * AGE_DIFF AS M_TO_USE
  , S_LOW + (S_HIGH - S_LOW) * AGE_DIFF AS S_TO_USE
  FROM CHILD_CENTILE_CALC_3
  
  -- COMMAND ----------
  
  -- calcualte bmi z score
  CREATE OR REPLACE TEMPORARY VIEW CHILD_CENTILE_CALC_5 AS
  SELECT *
  , (POWER((BMI/M_TO_USE), L_TO_USE) - 1) / (L_TO_USE * S_TO_USE) AS BMI_Z_SCORE
  FROM CHILD_CENTILE_CALC_4
  
  -- COMMAND ----------
  
  -- categorise bmi z score
  CREATE OR REPLACE TEMPORARY VIEW CHILD_CENTILE_CALC_6 AS
  SELECT *
  , CASE WHEN BMI_Z_SCORE <=-2 THEN "UNDERWEIGHT"
         WHEN BMI_Z_SCORE >-2 AND BMI_Z_SCORE <(4/3) THEN "IDEAL"
         WHEN BMI_Z_SCORE >= (4/3) AND BMI_Z_SCORE <2 THEN "OVERWEIGHT"
         WHEN BMI_Z_SCORE >=2 THEN "OBESE"
         ELSE "ERROR"
         END AS CENTILE_CLINICAL_BMI_CAT
  FROM CHILD_CENTILE_CALC_5
  
  -- COMMAND ----------
  
  -- MAGIC %md
  -- MAGIC ## APPEND BMI (pre categorised) JOURNALS AND CALCULATED BMI JOURNALS (from height and weight) TOGETHER
  
  -- COMMAND ----------
  
  -- append adult BMI journals to Height/Weight BMI calculated journals
  CREATE OR REPLACE TEMPORARY VIEW BMI_HW_COMBINED_2 AS
  SELECT DATE_OF_BIRTH
  ,SEX	
  ,LSOA
  ,DATE_OF_DEATH
  ,NHS_NUMBER	
  ,ETHNIC
  ,PRACTICE	
  ,GP_SYSTEM_SUPPLIER
  ,PROCESSED_TIMESTAMP	
  ,REPORTING_PERIOD_END_DATE	
  ,JOURNAL_REPORTING_PERIOD_END_DATE
  ,DATE	
  ,RECORD_DATE	
  ,CODE	
  ,SENSITIVE_CODE	
  ,EPISODE_CONDITION
  ,EPISODE_PRESCRIPTION	
  ,VALUE1_CONDITION	
  ,VALUE2_CONDITION
  ,VALUE1_PRESCRIPTION	
  ,VALUE2_PRESCRIPTION	
  ,LINKS
  ,ConceptId_Description
  ,AGE_AT_TIME_OF_JOURNAL
  ,null as H_VALUE	
  ,null as W_RECORD_DATE
  ,null as W_CODE	
  ,null as W_VALUE	
  ,null as W_LINKS
  ,null as BMI
  ,BMI_CAT	
  ,BMI_CATEGORISED_INITIAL	
  ,"BMI (ADULT)" AS TABLE_ORIGIN
  FROM BMI_CATEGORY_NO_UNKNOWNS_2
  
  UNION 
  
  SELECT DATE_OF_BIRTH	
  ,SEX	
  ,LSOA	
  ,DATE_OF_DEATH
  ,NHS_NUMBER	
  ,ETHNIC	
  ,PRACTICE
  ,GP_SYSTEM_SUPPLIER	
  ,PROCESSED_TIMESTAMP	
  ,REPORTING_PERIOD_END_DATE
  ,JOURNAL_REPORTING_PERIOD_END_DATE
  ,DATE	
  ,RECORD_DATE
  ,CODE
  ,SENSITIVE_CODE	
  ,EPISODE_CONDITION
  ,EPISODE_PRESCRIPTION	
  ,VALUE1_CONDITION
  ,VALUE2_CONDITION	
  ,VALUE1_PRESCRIPTION	
  ,VALUE2_PRESCRIPTION	
  ,LINKS	
  ,ConceptId_Description
  ,AGE_AT_TIME_OF_JOURNAL	
  ,H_VALUE	
  ,W_RECORD_DATE
  ,W_CODE	
  ,W_VALUE	
  ,W_LINKS
  ,BMI	
  ,null as BMI_CAT	
  ,BMI_CATEGORISED_INITIAL 
  ,"HW (ADULT)" AS TABLE_ORIGIN
  FROM HEIGHT_WEIGHT_NO_UNKNOWN_2
  
  
  -- COMMAND ----------
  
  -- append child journals (bmi and height/weight calculated)
  CREATE OR REPLACE TEMPORARY VIEW BMI_HW_COMBINED_3 AS
  SELECT DATE_OF_BIRTH
  ,SEX	
  ,NHS_NUMBER	
  ,ETHNIC
  ,PRACTICE	
  ,GP_SYSTEM_SUPPLIER
  ,REPORTING_PERIOD_END_DATE	
  ,JOURNAL_REPORTING_PERIOD_END_DATE
  ,DATE	
  ,RECORD_DATE	
  ,AGE_AT_TIME_OF_JOURNAL
  ,COALESCE(BMI, VALUE1_CONDITION) as BMI	
  ,BMI_CATEGORISED_INITIAL AS BMI_CATEGORY	
  ,TABLE_ORIGIN
  FROM BMI_HW_COMBINED_2
  
  UNION
  
  SELECT DATE_OF_BIRTH
  ,SEX	
  ,NHS_NUMBER	
  ,ETHNIC
  ,PRACTICE	
  ,GP_SYSTEM_SUPPLIER
  ,REPORTING_PERIOD_END_DATE	
  ,JOURNAL_REPORTING_PERIOD_END_DATE
  ,DATE	
  ,RECORD_DATE	
  ,AGE_AT_TIME_OF_JOURNAL
  ,BMI	
  ,CENTILE_CLINICAL_BMI_CAT AS BMI_CATEGORY	
  ,"HW (CHILD) CENTILE" AS TABLE_ORIGIN
  FROM CHILD_CENTILE_CALC_6
  WHERE CENTILE_CLINICAL_BMI_CAT IN ('UNDERWEIGHT', 'IDEAL', 'UNDER 20 NULLS', 'OVERWEIGHT', 'OBESE')
  
  UNION
  
  SELECT DATE_OF_BIRTH
  ,SEX	
  ,NHS_NUMBER	
  ,ETHNIC
  ,PRACTICE	
  ,GP_SYSTEM_SUPPLIER
  ,REPORTING_PERIOD_END_DATE	
  ,JOURNAL_REPORTING_PERIOD_END_DATE
  ,DATE	
  ,RECORD_DATE	
  ,AGE_AT_TIME_OF_JOURNAL
  ,BMI	
  ,CENTILE_CLINICAL_BMI_CAT AS BMI_CATEGORY	
  ,"CHILD BMI CENTILE" AS TABLE_ORIGIN
  FROM BMI_CENTILE_CALC_5
  WHERE CENTILE_CLINICAL_BMI_CAT IN ('UNDERWEIGHT', 'IDEAL', 'UNDER 20 NULLS', 'OVERWEIGHT', 'OBESE')
  
  UNION
  
  SELECT DATE_OF_BIRTH
  ,SEX	
  ,NHS_NUMBER	
  ,ETHNIC
  ,PRACTICE	
  ,GP_SYSTEM_SUPPLIER
  ,REPORTING_PERIOD_END_DATE	
  ,JOURNAL_REPORTING_PERIOD_END_DATE
  ,DATE	
  ,RECORD_DATE	
  ,AGE_AT_TIME_OF_JOURNAL
  ,VALUE1_CONDITION AS BMI	
  ,BMI_CATEGORISED_INITIAL AS BMI_CATEGORY	
  ,"BMI - CENTILE" AS TABLE_ORIGIN
  FROM CHILD_CENTILES
  WHERE BMI_CATEGORISED_INITIAL IN ('UNDERWEIGHT', 'IDEAL', 'UNDER 20 NULLS', 'OVERWEIGHT', 'OBESE')
  
  -- COMMAND ----------
  
  -- select the most recent known BMI journal date for each patient
  CREATE OR REPLACE TEMPORARY VIEW RECENT_BMI_DATES_2 AS
  SELECT NHS_NUMBER
  , MAX(DATE) AS DATE
  FROM BMI_HW_COMBINED_3
  GROUP BY NHS_NUMBER
  ORDER BY NHS_NUMBER
  
  -- COMMAND ----------
  
  -- create table with only the most recent BMI journal(s) in for each patient - some patients may have multiple journals e.g. both saying there are overweight or one saying overweight and another saying ideal..
  CREATE OR REPLACE TEMPORARY VIEW RECENT_BMI_JOURNALS_2 AS
  SELECT a.*
  FROM BMI_HW_COMBINED_3 AS a
  INNER JOIN RECENT_BMI_DATES_2 AS b
    ON a.NHS_NUMBER = b.NHS_NUMBER
    AND a.DATE = b.DATE
  
  -- COMMAND ----------
  
  -- create table to remove patients with more than one BMI category in their most recent journals  i.e. if patient has 2 journals saying ideal that is fine, but if patient has one journal saying overweight and another saying ideal this needs to be removed and looked at further
  -- we do this by counting the number of journals per patient, per BMI category
  CREATE OR REPLACE TEMPORARY VIEW BMI_CATS_PER_PATIENT_2 AS
  SELECT NHS_NUMBER
  , COUNT(DISTINCT BMI_CATEGORY) AS COUNT_CATEGORIES
  FROM RECENT_BMI_JOURNALS_2
  GROUP BY NHS_NUMBER
  
  -- COMMAND ----------
  
  -- create list of patients with only one BMI category per patient -> patient with more than one BMI category will have a count greater than one
  CREATE OR REPLACE TEMPORARY VIEW CATS_PER_PATIENT_2 AS
  SELECT DISTINCT NHS_NUMBER
  FROM BMI_CATS_PER_PATIENT_2
  WHERE COUNT_CATEGORIES = 1
  
  -- COMMAND ----------
  
  ------------------------------------------------------------------------
  -----------------------------------------------------------------------
  ----------- THIS IS THE FINAL TABLE ------------------------------------
  ------------------------------------------------------------------------
  -----------------------------------------------------------------------
  
  -- create table with patients with more than one BMI cat removed
  CREATE OR REPLACE TEMPORARY VIEW RECENT_BMI_CAT_DUPS_REMOVED_2 AS
  SELECT *
  FROM RECENT_BMI_JOURNALS_2
  WHERE NHS_NUMBER IN (SELECT * FROM CATS_PER_PATIENT_2)