06_COHORT_MASTER.sql

--
--
------------------------------------------
--
-- Script:			06_COHORT_MASTER.sql
-- SAIL project:	1483 - Cardiovascular disease risk prediction and optimisation of risk factor management
--
-- About:			Creating master table for characterising cohort
-- Author:			Daniel King
------------------------------------------
--
-- Characteristics of all DM patients at entry to the study
------------------------------------------   
--
--Drop Table
CALL FNC.DROP_IF_EXISTS('SAILW1483V.P2_DIABETES_COHORT_MASTER');
COMMIT;
------------------------------------------
--
--Create Table
CREATE TABLE SAILW1483V.P2_DIABETES_COHORT_MASTER
(
ALF_PE						BIGINT,
GENDER						INTEGER,
ONS_AGE_BAND_FIRST_DIAG		VARCHAR(12),
ETHNICITY					VARCHAR(15),
WIMD_2019_QUINTILE			INTEGER,
WEIGHT_CAT					VARCHAR(5),
BMI							DECIMAL(31,8),
INCLUSION_TOTAL				INTEGER,
INCLUSION_STUDY_PERIOD		INTEGER,
FIRST_DATE					DATE,
FIRST_YEAR					INTEGER,
DIAG_U_18_AGE				INTEGER,
DIAG_U_18_DATE				DATE,
TYPE						VARCHAR(2),
AGE_DIAG					INTEGER,
AGE_DIAG_M					DECIMAL(31,8),
AGE_ENTRY					INTEGER,
AGE_ENTRY_M					DECIMAL(31,8),
CENSOR_DATE					DATE,
DEATH_DT					DATE,
DEATH_YEAR					INTEGER,
AGE_AT_DEATH				INTEGER,
AGE_AT_DEATH_M				DECIMAL(31,8),
CENSOR_DEAD					INTEGER,
CENSOR_MIGR					INTEGER,
CENSOR_REG					INTEGER,
CENSOR_ASCVD				INTEGER,
AGE_AT_ASCVD				INTEGER,
ASCVD_DT					DATE,
ASCVD_TERR					VARCHAR(20),
INC_WITH_ASCVD				INTEGER,
STUDY_30D_FOLLOW_UP			INTEGER,
STUDY_60D_FOLLOW_UP			INTEGER,
STUDY_90D_FOLLOW_UP			INTEGER,
STUDY_120D_FOLLOW_UP		INTEGER,
STUDY_365D_FOLLOW_UP		INTEGER,
INCIDENT					INTEGER,
PREVALENT					INTEGER,
SMOKING_STATUS				CHARACTER(1),
DEMENTIA_BEFORE				INTEGER,
MALIGNANT_BEFORE			INTEGER,
ASTHMA_BEFORE				INTEGER,
COPD_BEFORE					INTEGER,
RESP_BEFORE					INTEGER,
LIVER_BEFORE				INTEGER,
RENAL_BEFORE				INTEGER,
HF_BEFORE					INTEGER,
HYPERTENSION_BEFORE			INTEGER,
INC_CAT						VARCHAR(30)
)
;
COMMIT;
------------------------------------------
--
--Insert into Table
INSERT INTO SAILW1483V.P2_DIABETES_COHORT_MASTER
--
WITH A AS
(
SELECT
D.*,
W.GENDER,
W.WOB
FROM SAILW1483V.COHORT_DIABETES_ALFS D

LEFT JOIN
(
SELECT 
D.ALF_PE,
MIN(CAST(WDS.GNDR_CD AS INTEGER)) AS GENDER,
MIN(WDS.WOB) AS WOB
FROM SAILW1483V.COHORT_DIABETES_ALFS D

LEFT JOIN SAILW1483V.WDSD_DATA WDS 
ON D.ALF_PE = WDS.ALF_PE

GROUP BY D.ALF_PE	
) W
ON D.ALF_PE = W.ALF_PE
),

A_2 AS
(
SELECT
A.*,
CAST(ADDE.DEATH_DT AS DATE) AS DEATH_DT,
CASE 
	WHEN M.ALF_PE IS NOT NULL 
	THEN 1 ELSE 0 END AS CENSOR_MIGR,
M.START_DATE AS MIGRATION_DATE,
CASE 
	WHEN G.ALF_PE IS NOT NULL 
	THEN 1 ELSE 0 END AS CENSOR_REG,
G.START_DATE AS GP_LOSS_DATE
FROM A

LEFT JOIN SAILW1483V.EXTRACT_P2_ADDE_DEATHS ADDE
ON A.ALF_PE = ADDE.ALF_PE

LEFT JOIN SAILW1483V.PREP_DIABETES_CENSOR_MIGR M
ON A.ALF_PE = M.ALF_PE

LEFT JOIN SAILW1483V.PREP_DIABETES_CENSOR_GP_LOSS G
ON A.ALF_PE = G.ALF_PE
)

--SELECT * FROM A_2
,

B AS
(
SELECT
A.*,
CASE WHEN DEATH_DT IS NOT NULL 
	THEN 1 ELSE 0 END AS CENSOR_DEAD,
YEAR(FIRST_DATE - WOB) AS AGE_DIAG,
ROUND((DAYS(FIRST_DATE) - DAYS(WOB))/365.25,1) AS AGE_DIAG_M,
CASE
	WHEN INCIDENT = 1 THEN YEAR(FIRST_DATE - WOB)
	WHEN PREVALENT = 1 THEN (2010 - YEAR(FIRST_DATE)) + YEAR(FIRST_DATE - WOB) 	
END AS AGE_ENTRY,
CASE
	WHEN INCIDENT = 1 THEN ROUND((DAYS(FIRST_DATE) - DAYS(WOB))/365.25,1)
	WHEN PREVALENT = 1 THEN (2010 - YEAR(FIRST_DATE)) + ROUND((DAYS(FIRST_DATE) - DAYS(WOB))/365.25,1)	
END AS AGE_ENTRY_M,
YEAR(DEATH_DT - WOB) AS AGE_AT_DEATH,
ROUND((DAYS(DEATH_DT) - DAYS(WOB))/365.25,1) AS AGE_AT_DEATH_M
FROM A_2 A
)

--SELECT * FROM B
--SELECT COUNT (DISTINCT ALF_PE) FROM B
,

C AS
(
SELECT
B.*,
CASE WHEN PREVALENT = 1 THEN G_2.WIMD_2019_QUINTILE
ELSE G.WIMD_2019_QUINTILE
END AS WIMD_2019_QUINTILE
FROM B

LEFT JOIN SAILW1483V.EXTRACT_P2_WDSD_SINGLE_CLEAN_GEO_CHAR_LSOA2011 G
ON B.ALF_PE = G.ALF_PE AND (B.FIRST_DATE BETWEEN G.START_DATE AND G.END_DATE)

LEFT JOIN SAILW1483V.EXTRACT_P2_WDSD_SINGLE_CLEAN_GEO_CHAR_LSOA2011 G_2
ON B.ALF_PE = G_2.ALF_PE AND (SAILW1483V.STUDY_START_DATE BETWEEN G_2.START_DATE AND G_2.END_DATE)
),

D AS
(
SELECT
C.*,
CASE 
	WHEN CENSOR_DEAD = 0 AND CENSOR_MIGR = 0 AND CENSOR_REG = 0 THEN NULL
	
	WHEN CENSOR_DEAD = 0 AND CENSOR_MIGR = 0 AND CENSOR_REG = 1 THEN GP_LOSS_DATE
	WHEN CENSOR_DEAD = 0 AND CENSOR_MIGR = 1 AND CENSOR_REG = 0 THEN MIGRATION_DATE	
	WHEN CENSOR_DEAD = 1 AND CENSOR_MIGR = 0 AND CENSOR_REG = 0 THEN DEATH_DT
	
	WHEN CENSOR_DEAD = 1 AND CENSOR_MIGR = 1 AND CENSOR_REG = 0 AND DEATH_DT <= MIGRATION_DATE THEN DEATH_DT
	WHEN CENSOR_DEAD = 1 AND CENSOR_MIGR = 1 AND CENSOR_REG = 0 AND DEATH_DT > MIGRATION_DATE THEN MIGRATION_DATE
	WHEN CENSOR_DEAD = 1 AND CENSOR_MIGR = 0 AND CENSOR_REG = 1 AND DEATH_DT <= GP_LOSS_DATE THEN DEATH_DT
	WHEN CENSOR_DEAD = 1 AND CENSOR_MIGR = 0 AND CENSOR_REG = 1 AND DEATH_DT > GP_LOSS_DATE THEN GP_LOSS_DATE	
	WHEN CENSOR_DEAD = 0 AND CENSOR_MIGR = 1 AND CENSOR_REG = 1 AND MIGRATION_DATE <= GP_LOSS_DATE THEN MIGRATION_DATE
	WHEN CENSOR_DEAD = 0 AND CENSOR_MIGR = 1 AND CENSOR_REG = 1 AND MIGRATION_DATE > GP_LOSS_DATE THEN GP_LOSS_DATE
	
	WHEN CENSOR_DEAD = 1 AND CENSOR_MIGR = 1 AND CENSOR_REG = 1 AND DEATH_DT <= MIGRATION_DATE AND DEATH_DT <= GP_LOSS_DATE THEN DEATH_DT	
	WHEN CENSOR_DEAD = 1 AND CENSOR_MIGR = 1 AND CENSOR_REG = 1 AND MIGRATION_DATE < DEATH_DT AND MIGRATION_DATE <= GP_LOSS_DATE THEN MIGRATION_DATE	
	WHEN CENSOR_DEAD = 1 AND CENSOR_MIGR = 1 AND CENSOR_REG = 1 AND GP_LOSS_DATE < DEATH_DT AND GP_LOSS_DATE < MIGRATION_DATE THEN GP_LOSS_DATE
	ELSE '1999-12-31'
END AS CENSOR_DATE,
CASE 
	WHEN AGE_ENTRY <= 4 THEN '4 and under' 
	WHEN AGE_ENTRY <= 9 THEN '5 to 9' 
	WHEN AGE_ENTRY <= 14 THEN '10 to 14'  
	WHEN AGE_ENTRY <= 19 THEN '15 to 19'  
	WHEN AGE_ENTRY <= 24 THEN '20 to 24'  
	WHEN AGE_ENTRY <= 29 THEN '25 to 29'  
	WHEN AGE_ENTRY <= 34 THEN '30 to 34'  
	WHEN AGE_ENTRY <= 39 THEN '35 to 39'  
	WHEN AGE_ENTRY <= 44 THEN '40 to 44'  
	WHEN AGE_ENTRY <= 49 THEN '45 to 49'  
	WHEN AGE_ENTRY <= 54 THEN '50 to 54'  
	WHEN AGE_ENTRY <= 59 THEN '55 to 59'  
	WHEN AGE_ENTRY <= 64 THEN '60 to 64'  
	WHEN AGE_ENTRY <= 69 THEN '65 to 69'  
	WHEN AGE_ENTRY <= 74 THEN '70 to 74' 
	WHEN AGE_ENTRY <= 79 THEN '75 to 79'  
	WHEN AGE_ENTRY <= 84 THEN '80 to 84'  
	WHEN AGE_ENTRY <= 89 THEN '85 to 89'  
	WHEN AGE_ENTRY <= 94 THEN '90 to 94'  
	WHEN AGE_ENTRY <= 99 THEN '95 to 99'  
	WHEN AGE_ENTRY >= 100 THEN '100 and over'
	ELSE NULL END AS ONS_AGE_BAND_FIRST_DIAG,
ETH.ETHN_EC_ONS_DATE_LATEST_DESC AS ETHNICITY
FROM C

LEFT JOIN SAILW1483V.ETHN_1483 ETH
ON C.ALF_PE = ETH.ALF_PE
),

E AS
(
SELECT
D.*,
CASE
	WHEN CENSOR_DATE IS NULL THEN DAYS(SAILW1483V.END_DATE) - DAYS(FIRST_DATE)
	ELSE DAYS(CENSOR_DATE) - DAYS(FIRST_DATE) 
END AS INCLUSION_TOTAL,
CASE 	
	WHEN CENSOR_DATE IS NULL AND FIRST_DATE >= CAST(SAILW1483V.STUDY_START_DATE AS DATE) THEN DAYS(SAILW1483V.END_DATE) - DAYS(FIRST_DATE)
	WHEN CENSOR_DATE IS NULL AND FIRST_DATE < CAST(SAILW1483V.STUDY_START_DATE AS DATE) THEN DAYS(SAILW1483V.END_DATE) - DAYS(CAST(SAILW1483V.STUDY_START_DATE AS DATE))
	WHEN CENSOR_DATE IS NOT NULL AND FIRST_DATE >= CAST(SAILW1483V.STUDY_START_DATE AS DATE) THEN DAYS(CENSOR_DATE) - DAYS(FIRST_DATE)
	WHEN CENSOR_DATE IS NOT NULL AND CENSOR_DATE >= CAST(SAILW1483V.STUDY_START_DATE AS DATE) THEN DAYS(CENSOR_DATE) - DAYS(CAST(SAILW1483V.STUDY_START_DATE AS DATE))
	ELSE 0
END AS INCLUSION_STUDY_PERIOD
FROM D
)

--SELECT * FROM E
--SELECT COUNT (DISTINCT ALF_PE) FROM E
,

F AS
(
SELECT
E.ALF_PE,
GENDER,
ONS_AGE_BAND_FIRST_DIAG,
ETHNICITY,
WIMD_2019_QUINTILE,
WEIGHT AS WEIGHT_CAT,
EVENT_VAL AS BMI,
INCLUSION_TOTAL,
INCLUSION_STUDY_PERIOD,	
E.FIRST_DATE,
YEAR(E.FIRST_DATE) AS FIRST_YEAR,
AGE_DIAG,
AGE_DIAG_M,
AGE_ENTRY,
AGE_ENTRY_M,
DEATH_DT,
YEAR(DEATH_DT) AS DEATH_YEAR,
AGE_AT_DEATH,
AGE_AT_DEATH_M,
CENSOR_DATE,
CASE WHEN CENSOR_DATE < DEATH_DT THEN 0 ELSE CENSOR_DEAD END AS CENSOR_DEAD,
CENSOR_MIGR,
CENSOR_REG,
CASE WHEN INCLUSION_STUDY_PERIOD >= 30 THEN 1 ELSE 0 END AS STUDY_30D_FOLLOW_UP,
CASE WHEN INCLUSION_STUDY_PERIOD >= 60 THEN 1 ELSE 0 END AS STUDY_60D_FOLLOW_UP,
CASE WHEN INCLUSION_STUDY_PERIOD >= 90 THEN 1 ELSE 0 END AS STUDY_90D_FOLLOW_UP,
CASE WHEN INCLUSION_STUDY_PERIOD >= 120 THEN 1 ELSE 0 END AS STUDY_120D_FOLLOW_UP,
CASE WHEN INCLUSION_STUDY_PERIOD >= 365 THEN 1 ELSE 0 END AS STUDY_365D_FOLLOW_UP,
INCIDENT,
PREVALENT
FROM E

LEFT JOIN SAILW1483V.TEMP_DIABETES_COHORT_BMI_BASELINE B
ON E.ALF_PE = B.ALF_PE
)

--SELECT COUNT (DISTINCT ALF_PE) FROM F
,

MORBS_1 AS
(
SELECT
ALF_PE,
CASE 
	WHEN PREVALENT = 1 THEN '2010-01-01'
	ELSE FIRST_DATE
END AS FIRST_DATE
FROM F
),

MORBS_2 AS
(
SELECT
A.ALF_PE,
A.FIRST_DATE,
GP.EVENT_DT,
M.READ_TYPE
FROM MORBS_1 A

JOIN SAILW1483V.EXTRACT_P2_WLGP_GP_EVENT_CLEANSED GP
ON A.ALF_PE = GP.ALF_PE

JOIN SAILW1483V.PHEN_MORB_READ M
ON GP.EVENT_CD = M.READ_CODE

WHERE GP.EVENT_DT <= A.FIRST_DATE

ORDER BY A.ALF_PE, GP.EVENT_DT
),

MORBS_3 AS
(
SELECT
ALF_PE,
READ_TYPE
FROM MORBS_2

GROUP BY ALF_PE,READ_TYPE
ORDER BY ALF_PE
),

MORBS_4 AS
(
SELECT 
ALF_PE,
READ_TYPE,
CASE WHEN READ_TYPE = 'DEMENTIA'			THEN 1 ELSE 0 END AS DEMENTIA_BEFORE,
CASE WHEN READ_TYPE = 'MALIGNANT' 			THEN 1 ELSE 0 END AS MALIGNANT_BEFORE,
CASE WHEN READ_TYPE = 'ASTHMA' 				THEN 1 ELSE 0 END AS ASTHMA_BEFORE,
CASE WHEN READ_TYPE = 'COPD' 				THEN 1 ELSE 0 END AS COPD_BEFORE,
CASE WHEN READ_TYPE IN ('COPD','ASTHMA')	THEN 1 ELSE 0 END AS RESP_BEFORE,
CASE WHEN READ_TYPE = 'LIVER_DISEASE' 		THEN 1 ELSE 0 END AS LIVER_BEFORE,
CASE WHEN READ_TYPE = 'CRF' 				THEN 1 ELSE 0 END AS RENAL_BEFORE,
CASE WHEN READ_TYPE = 'HEART_FAILURE' 		THEN 1 ELSE 0 END AS HF_BEFORE,
CASE WHEN READ_TYPE = 'HYPERTENSION' 		THEN 1 ELSE 0 END AS HYPERTENSION_BEFORE
FROM MORBS_3
),

MORBS_5 AS
(
SELECT
ALF_PE,
MAX (DEMENTIA_BEFORE) 		AS DEMENTIA_BEFORE,
MAX (MALIGNANT_BEFORE)		AS MALIGNANT_BEFORE,
MAX (ASTHMA_BEFORE) 		AS ASTHMA_BEFORE,
MAX (COPD_BEFORE) 			AS COPD_BEFORE,
MAX (RESP_BEFORE) 			AS RESP_BEFORE,
MAX (LIVER_BEFORE) 			AS LIVER_BEFORE,
MAX (RENAL_BEFORE) 			AS RENAL_BEFORE,
MAX (HF_BEFORE) 			AS HF_BEFORE,
MAX (HYPERTENSION_BEFORE) 	AS HYPERTENSION_BEFORE
FROM MORBS_4

GROUP BY ALF_PE
ORDER BY ALF_PE
),

G AS
(
SELECT
F.*,
CASE WHEN F.PREVALENT = 1 THEN P.SMOKING_STATUS ELSE S.SMOKING_STATUS END AS SMOKING_STATUS,
CASE WHEN E.DEMENTIA_BEFORE IS NULL THEN 0 ELSE E.DEMENTIA_BEFORE END AS DEMENTIA_BEFORE,
CASE WHEN E.MALIGNANT_BEFORE IS NULL THEN 0 ELSE E.MALIGNANT_BEFORE END AS MALIGNANT_BEFORE,
CASE WHEN E.ASTHMA_BEFORE IS NULL THEN 0 ELSE E.ASTHMA_BEFORE END AS ASTHMA_BEFORE,
CASE WHEN E.COPD_BEFORE IS NULL THEN 0 ELSE E.COPD_BEFORE END AS COPD_BEFORE,
CASE WHEN E.RESP_BEFORE IS NULL THEN 0 ELSE E.RESP_BEFORE END AS RESP_BEFORE,
CASE WHEN E.LIVER_BEFORE IS NULL THEN 0 ELSE E.LIVER_BEFORE END AS LIVER_BEFORE,
CASE WHEN E.RENAL_BEFORE IS NULL THEN 0 ELSE E.RENAL_BEFORE END AS RENAL_BEFORE,
CASE WHEN E.HF_BEFORE IS NULL THEN 0 ELSE E.HF_BEFORE END AS HF_BEFORE,
CASE WHEN E.HYPERTENSION_BEFORE IS NULL THEN 0 ELSE E.HYPERTENSION_BEFORE END AS HYPERTENSION_BEFORE
FROM F

LEFT JOIN MORBS_5 E
ON F.ALF_PE = E.ALF_PE

LEFT JOIN SAILW1483V.P2_DIABETES_ALL_SMOKER_OUTPUT S
ON F.ALF_PE = S.PATIENT_ID 

LEFT JOIN SAILW1483V.P2_DIABETES_ALL_SMOKER_OUTPUT_PREV P
ON F.ALF_PE = P.PATIENT_ID 

WHERE F.CENSOR_DATE >= SAILW1483V.STUDY_START_DATE OR F.CENSOR_DATE IS NULL
)

--SELECT COUNT (DISTINCT ALF_PE) FROM G
,

GP_EGFR AS
(
SELECT
ALF_PE,
MAX(RENAL_BEFORE) AS RENAL_BEFORE
FROM
(
SELECT
GP.ALF_PE,
CASE WHEN EVENT_VAL < 60 THEN 1 ELSE 0 END AS RENAL_BEFORE
FROM SAILW1483V.EXTRACT_P2_WLGP_GP_EVENT_CLEANSED GP

JOIN G
ON G.ALF_PE = GP.ALF_PE

JOIN SAILUKHDV.READ_CD_CV2_SCD R
ON GP.EVENT_CD = R.READ_CODE

WHERE (R.PREF_TERM_30 LIKE('%eGFR%') OR R.PREF_TERM_30 LIKE('%Glom%'))
		AND EVENT_DT < FIRST_DATE
		AND EVENT_VAL IS NOT NULL
		AND EVENT_VAL > 0	
)
GROUP BY ALF_PE
)

--SELECT * FROM GP_EGFR
,


WRRS_EGFR AS
(
SELECT
ALF_PE,
MAX(RENAL_BEFORE) AS RENAL_BEFORE
FROM
(
SELECT
ALF_PE, 
CASE WHEN CAST(VAL AS INTEGER) < 60 THEN 1 ELSE 0 END AS RENAL_BEFORE
FROM
(
SELECT
	W.ALF_PE,
	FIRST_DATE AS DIAG,
	SPCM_COLLECTED_DT AS TEST,
	VAL_TYPE,
	CODE,	
	NAME,	
	READ_CD,
	CASE 
		WHEN VAL = '>90' THEN 91
		WHEN VAL = '>60' THEN 61
		ELSE VAL END AS VAL,	
	UNITOFMEASUREMENT
FROM 
	SAILW1483V.EXTRACT_P2_WRRS_OBSERVATION_RESULT W

JOIN G
ON W.ALF_PE = G.ALF_PE
	
WHERE NAME LIKE('%GFR%')
	AND SPCM_COLLECTED_DT < FIRST_DATE
	AND VAL IS NOT NULL
	AND VAL_TYPE = 'SN'
	AND VAL NOT IN('AMENDED RESULT','*','{redacted}','NA (Haemolysed specimen)','Serum Haemolysed','Not available.','NA (No sample)','NA','Insufficient sample','<0.1','NA (TEST NOT PERFORMED.)','NA (Insufficient)','>165','<20','Not Tested','<18','<15','Not Available','>3270.8','<13')
)
)
GROUP BY ALF_PE
)

--SELECT * FROM WRRS_EGFR
,

EGFR AS
(
SELECT
ALF_PE,
MAX(RENAL_BEFORE) AS RENAL_BEFORE
FROM
(
SELECT
ALF_PE,
RENAL_BEFORE
FROM GP_EGFR

UNION

SELECT
ALF_PE,
RENAL_BEFORE
FROM WRRS_EGFR
)
GROUP BY ALF_PE
)

--SELECT * FROM EGFR

SELECT
G.ALF_PE,
GENDER,
ONS_AGE_BAND_FIRST_DIAG,
ETHNICITY,
WIMD_2019_QUINTILE,
WEIGHT_CAT,
BMI,
INCLUSION_TOTAL,
INCLUSION_STUDY_PERIOD,
CASE WHEN AGE_DIAG < 18 THEN (18 - AGE_DIAG) YEARS + G.FIRST_DATE ELSE G.FIRST_DATE END AS FIRST_DATE,
CASE WHEN AGE_DIAG < 18 THEN (18 - AGE_DIAG) + FIRST_YEAR ELSE FIRST_YEAR END AS FIRST_YEAR,
CASE WHEN AGE_DIAG < 18 THEN AGE_DIAG ELSE NULL END AS DIAG_U_18_AGE,
CASE WHEN AGE_DIAG < 18 THEN G.FIRST_DATE ELSE NULL END AS DIAG_U_18_DATE,
CASE WHEN T.TYPE IS NULL THEN 'U' ELSE T.TYPE END AS TYPE,
AGE_DIAG,
AGE_DIAG_M,
CASE WHEN AGE_ENTRY < (18 - AGE_DIAG) + AGE_DIAG THEN (18 - AGE_DIAG) + AGE_DIAG ELSE AGE_ENTRY END AS AGE_ENTRY,
CASE WHEN AGE_ENTRY_M < (18 - AGE_DIAG_M) + AGE_DIAG_M THEN (18 - AGE_DIAG_M) + AGE_DIAG_M ELSE AGE_ENTRY_M END AS AGE_ENTRY_M,
CASE WHEN CENSOR_DATE IS NULL THEN '2025-01-01' ELSE CENSOR_DATE END AS CENSOR_DATE,
DEATH_DT,
DEATH_YEAR,
AGE_AT_DEATH,
AGE_AT_DEATH_M,
CENSOR_DEAD,
CENSOR_MIGR,
CENSOR_REG,
CASE
	WHEN P.FIRST_DATE IS NOT NULL AND CENSOR_DATE IS NULL THEN 1
	WHEN P.FIRST_DATE <= CENSOR_DATE THEN 1 
	ELSE 0 
END AS CENSOR_ASCVD,
CASE 
	WHEN P.FIRST_DATE IS NOT NULL AND P.FIRST_DATE <= G.FIRST_DATE THEN AGE_DIAG - YEAR(G.FIRST_DATE - P.FIRST_DATE)
	WHEN P.FIRST_DATE IS NOT NULL AND P.FIRST_DATE >  G.FIRST_DATE THEN AGE_DIAG + YEAR(P.FIRST_DATE - G.FIRST_DATE)
	ELSE NULL
END AS AGE_AT_ASCVD,
P.FIRST_DATE AS ASCVD_DT,
P.TYPE AS ASCVD_TERR,
CASE WHEN FIRST_YEAR >= YEAR(P.FIRST_DATE) THEN 1 ELSE 0 END AS INC_WITH_ASCVD,
STUDY_30D_FOLLOW_UP,
STUDY_60D_FOLLOW_UP,
STUDY_90D_FOLLOW_UP,
STUDY_120D_FOLLOW_UP,
STUDY_365D_FOLLOW_UP,
INCIDENT,
PREVALENT,
SMOKING_STATUS,
DEMENTIA_BEFORE,
MALIGNANT_BEFORE,
ASTHMA_BEFORE,
COPD_BEFORE,
RESP_BEFORE,
LIVER_BEFORE,
CASE WHEN E.RENAL_BEFORE = 1 OR G.RENAL_BEFORE = 1 THEN 1 ELSE G.RENAL_BEFORE END AS RENAL_BEFORE,
HF_BEFORE,
HYPERTENSION_BEFORE,
CASE WHEN STUDY_365D_FOLLOW_UP = 1 THEN '>= 1 year follow-up'
	 WHEN STUDY_365D_FOLLOW_UP = 0 AND CENSOR_DEAD = 1 THEN 'Lost to death'
	 ELSE 'Lost to follow-up'
END AS INC_CAT
FROM G

LEFT JOIN SAILW1483V.PREP_FIRST_EVENT P
ON G.ALF_PE = P.ALF_PE

LEFT JOIN SAILW1483V.COHORT_DIABETES_TYPES T
ON G.ALF_PE = T.ALF_PE

LEFT JOIN EGFR E
ON E.ALF_PE = G.ALF_PE

WHERE (18 - AGE_DIAG) YEARS + G.FIRST_DATE < SAILW1483V.END_DATE
;
COMMIT;
--
------------------------------------------
--
-- Select all results
SELECT
*
FROM 
	SAILW1483V.P2_DIABETES_COHORT_MASTER

ORDER BY ALF_PE
FETCH FIRST 100 ROWS ONLY;
--
-- Count all results
SELECT 
	COUNT(*)
FROM 
	SAILW1483V.P2_DIABETES_COHORT_MASTER;
--
-- Count distinct alfs
SELECT 
	COUNT(DISTINCT ALF_PE)
FROM 
	SAILW1483V.P2_DIABETES_COHORT_MASTER;
--
------------------------------------------   
--
-- Latest BMI record in each year of the study for prevalent patients
------------------------------------------
--
--Drop Table
CALL FNC.DROP_IF_EXISTS('SAILW1483V.TEMP_DIABETES_COHORT_BMI_PREV');
COMMIT;
------------------------------------------ 
--
-- Create Table
CREATE TABLE SAILW1483V.TEMP_DIABETES_COHORT_BMI_PREV
(
ALF_PE						BIGINT,
YR							INTEGER,
EVENT_VAL					DECIMAL(31,8),
WEIGHT						VARCHAR(5)
) 
;
COMMIT;
------------------------------------------
--
--Insert into Table
INSERT INTO SAILW1483V.TEMP_DIABETES_COHORT_BMI_PREV

WITH A AS
(
SELECT
ALF_PE,
FIRST_YR,
CENSOR_YR,
EVENT_DT,
EVENT_VAL,
WEIGHT
FROM
(
SELECT
B.ALF_PE,
FIRST_DATE,
YEAR(FIRST_DATE) AS FIRST_YR,
CENSOR_DATE,
YEAR(CENSOR_DATE) AS CENSOR_YR,
EVENT_DT,
EVENT_VAL,
WEIGHT,
ROW_NUMBER() OVER(PARTITION BY B.ALF_PE,YEAR(EVENT_DT) ORDER BY EVENT_DT DESC) AS RANK
FROM SAILW1483V.TEMP_DIABETES_COHORT_BMI B

JOIN SAILW1483V.P2_DIABETES_COHORT_MASTER F
ON B.ALF_PE = F.ALF_PE

WHERE EVENT_DT > FIRST_DATE
AND (EVENT_DT <= CENSOR_DATE OR CENSOR_DATE IS NULL)
)
WHERE RANK = 1
),


--Use recurring CTE to duplicate rows based on the duration of follow-up
CTE(ALF_PE,FIRST_YR,CENSOR_YR,YR) AS
(
SELECT
	ALF_PE,
	FIRST_YEAR AS FIRST_YR,
	CASE WHEN YEAR(CENSOR_DATE) IS NULL THEN 2024 ELSE YEAR(CENSOR_DATE) END AS CENSOR_YR,
	FIRST_YEAR AS YR
FROM SAILW1483V.P2_DIABETES_COHORT_MASTER

UNION ALL

SELECT
	ALF_PE,
	FIRST_YR,
	CENSOR_YR,
	YR + 1
FROM CTE
WHERE YR <= CTE.CENSOR_YR-1
),

B AS
(
SELECT
CTE.ALF_PE,
CTE.YR,
EVENT_VAL,
WEIGHT
FROM CTE

LEFT JOIN A
ON CTE.ALF_PE = A.ALF_PE
AND CTE.YR = YEAR(EVENT_DT)

ORDER BY ALF_PE, YR
)

SELECT
ALF_PE,
YR,
FIRST_VALUE(EVENT_VAL) OVER(PARTITION BY VALUE_PARTITION ORDER BY ALF_PE,YR) AS EVENT_VAL,
FIRST_VALUE(WEIGHT) OVER(PARTITION BY VALUE_PARTITION_2 ORDER BY ALF_PE,YR) AS WEIGHT
FROM
(
SELECT
ALF_PE,
YR,
EVENT_VAL,
WEIGHT,
SUM(CASE WHEN EVENT_VAL IS NULL THEN 0 ELSE 1 END) OVER (ORDER BY ALF_PE,YR) AS VALUE_PARTITION,
SUM(CASE WHEN WEIGHT IS NULL THEN 0 ELSE 1 END) OVER (ORDER BY ALF_PE,YR) AS VALUE_PARTITION_2
FROM B
ORDER BY ALF_PE,YR
)

;
------------------------------------------

SELECT * FROM SAILW1483V.TEMP_DIABETES_COHORT_BMI_PREV;

SELECT COUNT(*) FROM SAILW1483V.TEMP_DIABETES_COHORT_BMI_PREV;

SELECT COUNT(DISTINCT ALF_PE) FROM SAILW1483V.TEMP_DIABETES_COHORT_BMI_PREV;

------------------------------------------
--
-- Reclassification of characteristics in each year of the study
------------------------------------------   
--
--Drop Table
CALL FNC.DROP_IF_EXISTS('SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC');
COMMIT;
------------------------------------------
--
--Create Table
CREATE TABLE SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC
(
ALF_PE						BIGINT,
YEAR						INTEGER,
AGE							INTEGER,
FIRST_DATE					DATE,
TYPE						VARCHAR(2),
AGE_DIAG					INTEGER,
WEIGHT_CAT					VARCHAR(5),
SMOKING_STATUS				CHARACTER(1),
DYSLIPID					INTEGER,
HYPERTENSION				INTEGER,
CKD							INTEGER,
ASCVD						INTEGER
)
;
COMMIT;
------------------------------------------
--
--Insert into Table
INSERT INTO SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC
--
WITH X AS
(
SELECT
ALF_PE,
FIRST_DATE,
YEAR(FIRST_DATE) AS ENTRY_YR,
CENSOR_DATE,
YEAR(CENSOR_DATE) AS EXIT_YR
FROM SAILW1483V.P2_DIABETES_COHORT_MASTER

WHERE 
STUDY_365D_FOLLOW_UP = 1
)

--SELECT * FROM X
,

--Use recurring CTE to duplicate rows based on the duration of follow-up
CTE(ALF_PE,FIRST_DATE,ENTRY_YR,CENSOR_DATE,EXIT_YR,YR) AS
(
SELECT
	ALF_PE,
	FIRST_DATE,
	ENTRY_YR,
	CENSOR_DATE,
	CASE WHEN EXIT_YR IS NULL THEN 2025 ELSE EXIT_YR END AS EXIT_YR,
	ENTRY_YR AS YR
FROM X

UNION ALL

SELECT
	ALF_PE,
	FIRST_DATE,
	ENTRY_YR,
	FIRST_DATE,
	EXIT_YR,
	YR + 1
FROM CTE
WHERE YR <= CTE.EXIT_YR-1
)

--SELECT * FROM CTE
,

A AS
(
SELECT
	CTE.ALF_PE,
	CAST(CTE.YR || '-01-01' AS DATE) AS YR,
	B.WEIGHT AS WEIGHT_CAT,
	F.SMOKING_STATUS,
	CTE.FIRST_DATE,
	CASE WHEN CTE.CENSOR_DATE IS NULL THEN CAST('2025-01-01' AS DATE) ELSE CTE.CENSOR_DATE END AS CENSOR_DATE,
	F.AGE_DIAG,
	F.AGE_ENTRY
FROM CTE

JOIN SAILW1483V.P2_DIABETES_COHORT_MASTER F
ON CTE.ALF_PE = F.ALF_PE

LEFT JOIN SAILW1483V.TEMP_DIABETES_COHORT_BMI_PREV B
ON CTE.ALF_PE = B.ALF_PE
AND CTE.YR = B.YR

WHERE CTE.YR >= 2010

ORDER BY CTE.ALF_PE,CTE.YR
)

--SELECT * FROM A
--SELECT COUNT(DISTINCT ALF_PE) FROM A
,

B AS
(
SELECT
A.ALF_PE,
A.FIRST_DATE,
A.CENSOR_DATE,
A.YR,
GP.EVENT_DT,
M.READ_TYPE
FROM A

JOIN SAILW1483V.EXTRACT_P2_WLGP_GP_EVENT_CLEANSED GP
ON A.ALF_PE = GP.ALF_PE

JOIN SAILW1483V.PHEN_MORB_READ M
ON GP.EVENT_CD = M.READ_CODE

WHERE GP.EVENT_DT BETWEEN SAILW1483V.START_DATE AND A.YR
AND M.READ_TYPE IN('HYPERTENSION','DYSLIPID','CRF')

ORDER BY A.ALF_PE, GP.EVENT_DT
)

--SELECT * FROM B
,

C AS
(
SELECT
ALF_PE,
FIRST_DATE,
CENSOR_DATE,
YR,
EVENT_DT,
READ_TYPE
FROM 
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY ALF_PE, READ_TYPE ORDER BY EVENT_DT) AS RANK
FROM B
)
WHERE RANK = 1
)

--SELECT * FROM C
--SELECT COUNT(DISTINCT ALF_PE) FROM C
,

D AS
(
SELECT
ALF_PE,
YR,
MAX (WEIGHT_CAT) 				AS WEIGHT_CAT,
MAX (SMOKING_STATUS) 			AS SMOKING_STATUS,
MAX (FIRST_DATE) 				AS FIRST_DATE,
MAX (CENSOR_DATE) 				AS CENSOR_DATE,
MAX (AGE_DIAG) 					AS AGE_DIAG,
MAX (AGE_ENTRY) 				AS AGE_ENTRY,
MAX (DYSLIPID) 					AS DYSLIPID,
MAX (DYSLIPID_DT) 				AS DYSLIPID_DT,
MAX (HYPERTENSION) 				AS HYPERTENSION,
MAX (HYPERTENSION_DT) 			AS HYPERTENSION_DT,
MAX (CKD) 						AS CKD,
MAX (CKD_DT) 					AS CKD_DT
FROM 
(
SELECT 
A.*,
C.READ_TYPE,
CASE WHEN READ_TYPE = 'DYSLIPID' 			THEN 1 ELSE 0 END AS DYSLIPID,
CASE WHEN READ_TYPE = 'DYSLIPID'			THEN EVENT_DT ELSE NULL END AS DYSLIPID_DT,
CASE WHEN READ_TYPE = 'HYPERTENSION' 		THEN 1 ELSE 0 END AS HYPERTENSION,
CASE WHEN READ_TYPE = 'HYPERTENSION'		THEN EVENT_DT ELSE NULL END AS HYPERTENSION_DT,
CASE WHEN READ_TYPE = 'CRF' 				THEN 1 ELSE 0 END AS CKD,
CASE WHEN READ_TYPE = 'CRF'					THEN EVENT_DT ELSE NULL END AS CKD_DT
FROM A

LEFT JOIN C
ON A.ALF_PE = C.ALF_PE
AND A.YR BETWEEN C.EVENT_DT AND C.CENSOR_DATE
)
GROUP BY ALF_PE,YR
)

--SELECT COUNT(DISTINCT ALF_PE) FROM D

SELECT 
D.ALF_PE,
YEAR(YR) AS YEAR,
CASE WHEN F.DIAG_U_18_DATE IS NOT NULL THEN (YEAR(YR) - YEAR(F.DIAG_U_18_DATE)) + D.AGE_DIAG ELSE (YEAR(YR) - YEAR(D.FIRST_DATE)) + D.AGE_DIAG END AS AGE,
D.FIRST_DATE,
F.TYPE,
D.AGE_DIAG,
D.WEIGHT_CAT,
D.SMOKING_STATUS,
D.DYSLIPID,
D.HYPERTENSION,
D.CKD,
CASE WHEN ASCVD_DT IS NOT NULL AND YR >= ASCVD_DT THEN 1 ELSE 0 END AS ASCVD
FROM D

LEFT JOIN SAILW1483V.P2_DIABETES_COHORT_MASTER F
ON D.ALF_PE = F.ALF_PE

ORDER BY D.ALF_PE, D.YR
;
COMMIT;
--
------------------------------------------
--
-- Select all results
SELECT 
	* 
FROM 
	SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC
ORDER BY ALF_PE
FETCH FIRST 100 ROWS ONLY;
--
-- Count all results
SELECT 
	COUNT(*)
FROM 
	SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC;
--
-- Count distinct alfs
SELECT 
	COUNT(DISTINCT ALF_PE)
FROM 
	SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC;
--
------------------------------------------   
--
-- Reclassification of CKD diagnosis in each year of the study
------------------------------------------   
--
--Drop Table
CALL FNC.DROP_IF_EXISTS('SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC_CKD');
COMMIT;
------------------------------------------
--
--Create Table
CREATE TABLE SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC_CKD
(
	ALF_PE				BIGINT,
	YEAR				INTEGER,
	AGE					INTEGER,
	FIRST_DATE			DATE,
	CKD					INTEGER,
	ASCVD				INTEGER
)
;
COMMIT;
------------------------------------------
--
--Insert into Table
INSERT INTO SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC_CKD
--
WITH X AS
(
SELECT
ALF_PE,
FIRST_DATE,
YEAR(FIRST_DATE) AS ENTRY_YR,
CENSOR_DATE,
YEAR(CENSOR_DATE) AS EXIT_YR
FROM SAILW1483V.P2_DIABETES_COHORT_MASTER

WHERE 
STUDY_365D_FOLLOW_UP = 1
)

--SELECT * FROM X
,

--Use recurring CTE to duplicate rows based on the duration of follow-up
CTE(ALF_PE,FIRST_DATE,ENTRY_YR,CENSOR_DATE,EXIT_YR,YR) AS
(
SELECT
	ALF_PE,
	FIRST_DATE,
	ENTRY_YR,
	CENSOR_DATE,
	CASE WHEN EXIT_YR IS NULL THEN 2025 ELSE EXIT_YR END AS EXIT_YR,
	ENTRY_YR AS YR
FROM X

UNION ALL

SELECT
	ALF_PE,
	FIRST_DATE,
	ENTRY_YR,
	FIRST_DATE,
	EXIT_YR,
	YR + 1
FROM CTE
WHERE YR <= CTE.EXIT_YR-1
)

--SELECT * FROM CTE
,

A AS
(
SELECT
	CTE.ALF_PE,
	CAST(CTE.YR || '-01-01' AS DATE) AS YR,
	CTE.FIRST_DATE,
	CASE WHEN CTE.CENSOR_DATE IS NULL THEN CAST('2025-01-01' AS DATE) ELSE CTE.CENSOR_DATE END AS CENSOR_DATE,
	F.AGE_DIAG,
	F.AGE_ENTRY
FROM CTE

JOIN SAILW1483V.P2_DIABETES_COHORT_MASTER F
ON CTE.ALF_PE = F.ALF_PE

WHERE CTE.YR >= 2010

ORDER BY CTE.ALF_PE,CTE.YR
)

--SELECT * FROM A
--SELECT COUNT(DISTINCT ALF_PE) FROM A
,


B AS
(
SELECT
A.ALF_PE,
A.FIRST_DATE,
A.CENSOR_DATE,
A.YR,
GP.EVENT_DT,
M.READ_TYPE
FROM A

JOIN SAILW1483V.EXTRACT_P2_WLGP_GP_EVENT_CLEANSED GP
ON A.ALF_PE = GP.ALF_PE

JOIN SAILW1483V.PHEN_MORB_READ M
ON GP.EVENT_CD = M.READ_CODE

WHERE GP.EVENT_DT BETWEEN SAILW1483V.START_DATE AND A.YR
AND M.READ_TYPE IN('CRF')

ORDER BY A.ALF_PE, GP.EVENT_DT
)

--SELECT * FROM B
,

C AS
(
SELECT
ALF_PE,
FIRST_DATE,
CENSOR_DATE,
YR,
EVENT_DT,
READ_TYPE
FROM 
(
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY ALF_PE, READ_TYPE ORDER BY EVENT_DT) AS RANK
FROM B
)
WHERE RANK = 1
)

--SELECT * FROM C
--SELECT COUNT(DISTINCT ALF_PE) FROM C
,

D AS
(
SELECT
ALF_PE,
YR,
MAX (FIRST_DATE) 				AS FIRST_DATE,
MAX (CENSOR_DATE) 				AS CENSOR_DATE,
MAX (AGE_DIAG) 					AS AGE_DIAG,
MAX (AGE_ENTRY) 				AS AGE_ENTRY,
MAX (CKD) 						AS CKD,
MAX (CKD_DT) 					AS CKD_DT
FROM 
(
SELECT 
A.*,
C.READ_TYPE,
CASE WHEN READ_TYPE = 'CRF' 				THEN 1 ELSE 0 END AS CKD,
CASE WHEN READ_TYPE = 'CRF'					THEN EVENT_DT ELSE NULL END AS CKD_DT
FROM A

LEFT JOIN C
ON A.ALF_PE = C.ALF_PE
AND A.YR BETWEEN C.EVENT_DT AND C.CENSOR_DATE
)
GROUP BY ALF_PE,YR
)

--SELECT * FROM D ORDER BY ALF_PE, YR
,

GP_EGFR AS
(
SELECT
ALF_PE,
YR,
MAX(CKD) AS CKD
FROM
(
SELECT
GP.ALF_PE,
D.YR,
CASE WHEN EVENT_VAL < 60 THEN 1 ELSE 0 END AS CKD
FROM SAILW1483V.EXTRACT_P2_WLGP_GP_EVENT_CLEANSED GP

JOIN D
ON D.ALF_PE = GP.ALF_PE
AND GP.EVENT_DT < D.YR

JOIN SAILUKHDV.READ_CD_CV2_SCD R
ON GP.EVENT_CD = R.READ_CODE

WHERE (R.PREF_TERM_30 LIKE('%eGFR%') OR R.PREF_TERM_30 LIKE('%Glom%'))
		AND EVENT_VAL IS NOT NULL
		AND EVENT_VAL > 0	
)
GROUP BY ALF_PE,YR
)

--SELECT * FROM GP_EGFR
,


WRRS_EGFR AS
(
SELECT
ALF_PE,
YR,
MAX(CKD) AS CKD
FROM
(
SELECT
ALF_PE, 
YR,
CASE WHEN CAST(VAL AS INTEGER) < 60 THEN 1 ELSE 0 END AS CKD
FROM
(
SELECT
	W.ALF_PE,
	D.YR,
	CASE 
		WHEN VAL = '>90' THEN 91
		WHEN VAL = '>60' THEN 61
		ELSE VAL END AS VAL,	
	UNITOFMEASUREMENT
FROM 
	SAILW1483V.EXTRACT_P2_WRRS_OBSERVATION_RESULT W

JOIN D
ON W.ALF_PE = D.ALF_PE
AND W.SPCM_COLLECTED_DT < D.YR
	
WHERE NAME LIKE('%GFR%')
	AND VAL IS NOT NULL
	AND VAL_TYPE = 'SN'
	AND VAL NOT IN('AMENDED RESULT','*','{redacted}','NA (Haemolysed specimen)','Serum Haemolysed','Not available.','NA (No sample)','NA','Insufficient sample','<0.1','NA (TEST NOT PERFORMED.)','NA (Insufficient)','>165','<20','Not Tested','<18','<15','Not Available','>3270.8','<13')
)
)
GROUP BY ALF_PE,YR
)

--SELECT * FROM WRRS_EGFR
,

EGFR AS
(
SELECT
ALF_PE,
YR,
MAX(CKD) AS CKD
FROM
(
SELECT
ALF_PE,
YR,
CKD
FROM GP_EGFR

UNION

SELECT
ALF_PE,
YR,
CKD
FROM WRRS_EGFR
)
GROUP BY ALF_PE,YR
)

SELECT 
D.ALF_PE,
YEAR(D.YR) AS YEAR,
CASE WHEN F.DIAG_U_18_DATE IS NOT NULL THEN (YEAR(D.YR) - YEAR(F.DIAG_U_18_DATE)) + D.AGE_DIAG ELSE (YEAR(D.YR) - YEAR(D.FIRST_DATE)) + D.AGE_DIAG END AS AGE,
D.FIRST_DATE,
CASE WHEN D.CKD = 1 OR E.CKD = 1 THEN 1 ELSE D.CKD END AS CKD,
CASE WHEN ASCVD_DT IS NOT NULL AND D.YR >= ASCVD_DT THEN 1 ELSE 0 END AS ASCVD
FROM D

LEFT JOIN SAILW1483V.P2_DIABETES_COHORT_MASTER F
ON D.ALF_PE = F.ALF_PE

LEFT JOIN EGFR E
ON E.ALF_PE = D.ALF_PE
AND E.YR = D.YR

ORDER BY D.ALF_PE, D.YR
;
COMMIT;
--
------------------------------------------
--
-- Select all results
SELECT 
	* 
FROM 
	SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC_CKD
ORDER BY ALF_PE
FETCH FIRST 100 ROWS ONLY;
--
-- Count all results
SELECT 
	COUNT(*)
FROM 
	SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC_CKD;
--
-- Count distinct alfs
SELECT 
	COUNT(DISTINCT ALF_PE)
FROM 
	SAILW1483V.P2_DIABETES_COHORT_MASTER_DYNAMIC_CKD;
--
------------------------------------------