2.1 Secondary_Analysis1.sql

--Primary analysis 1.4 Primary_Analysis_V2.sql script must be run before this script
--Uses tables sailw0972v.V2_VB_MI_UTI_COMBINED and sailw0972v.V2_VB_STROKE_UTI_COMBINED

--secondary analysis 1 includes individuals with a WLGP recorded UTI diagnosis read code and a WRRS microbiology result of mixed or heavy mixed growth
--only linked UTIs (occuring within 7 days of each other) which do not contain a confirmed or possible UTI within the wider linked series are included

CALL FNC.DROP_IF_EXISTS ('SESSION.V2_VB_DAYS_IN_COHORT');
CALL FNC.DROP_IF_EXISTS ('sailw0972v.V2_VB_MI_UTI_MIXED');
CALL FNC.DROP_IF_EXISTS ('SAILW0972V.V2_VB_MI_SECANAL1_SEC');
CALL FNC.DROP_IF_EXISTS ('SAILW0972V.V2_VB_MI_SECANAL1');
CALL FNC.DROP_IF_EXISTS ('SESSION.V2_VB_STROKE_WRRS_SECANAL1');
CALL FNC.DROP_IF_EXISTS ('sailw0972v.V2_VB_STROKE_UTI_MIXED');
CALL FNC.DROP_IF_EXISTS ('SAILW0972V.V2_VB_STROKE_SECANAL1');

--generate table of inclusion start and end dates for first period of inclusion only

DECLARE GLOBAL TEMPORARY TABLE SESSION.V2_VB_DAYS_IN_COHORT AS
	(SELECT ALF_PE,
			LATEST_START,
			EARLIEST_END
			FROM SAILW0972V.V2_VB_WDSD_DAYS_IN_COHORT) 
DEFINITION ONLY
ON COMMIT PRESERVE ROWS;

Commit;

INSERT INTO SESSION.V2_VB_DAYS_IN_COHORT
	(ALF_PE,
	LATEST_START,
	EARLIEST_END)
	SELECT dic.ALF_PE,
			min(dic.LATEST_START),
			min(dic.EARLIEST_END)
	FROM SAILW0972V.V2_VB_WDSD_DAYS_IN_COHORT AS dic
	GROUP BY dic. ALF_PE;

Commit;

DELETE FROM SESSION.V2_VB_DAYS_IN_COHORT
	WHERE EARLIEST_END < LATEST_START;

-----------------------------------------------------------------------
--create table for the UTI sequences where the most significant result was mixed or heavy mixed growth
--i.e. no confirmed or possible UTI results identified in the surrounding combined UTI sequence (less than 7 days between UTIs)

CREATE TABLE sailw0972v.V2_VB_MI_UTI_MIXED
AS (SELECT alf_pe,
			diag_dt,
			group_number
	FROM sailw0972v.V2_VB_MI_UTI_COMBINED)
WITH NO data;

INSERT INTO sailw0972v.V2_VB_MI_UTI_MIXED
WITH cte AS --find highest outcome in a linked UTI sequence
(SELECT alf_pe, group_number, min(outcome_int) AS highest_outcome
	FROM sailw0972v.V2_VB_MI_UTI_COMBINED
GROUP BY alf_pe, group_number),
cte2 AS --find only those linked sequences with highest outcome 3 or 4 (mixed or heavy mixed)
(SELECT * FROM cte WHERE highest_outcome IN (3,4)),
cte3 AS -- minimum diagnosis date 
(SELECT cte.alf_pe,
		cte.group_number,
		min(u.diag_dt) AS diag_dt
		FROM cte
		INNER JOIN sailw0972v.V2_VB_MI_UTI_COMBINED AS u
			ON cte.alf_pe = u.alf_pe
			AND cte.group_number = u.group_number
		WHERE u.outcome_int IN (3,4)
	GROUP BY cte.alf_pe,
			cte.group_number)
SELECT DISTINCT 
		cte2.alf_pe,
		cte3.diag_dt,
		cte2.group_number
	FROM sailw0972v.V2_VB_MI_UTI_COMBINED AS uti
	INNER JOIN cte2
		ON uti.alf_pe = cte2.alf_pe
		AND uti.group_number = cte2.group_number
	INNER JOIN cte3
		ON uti.alf_pe = cte3.alf_pe
		AND uti.diag_dt = cte3.diag_dt
ORDER BY cte2.alf_pe, cte3.diag_dt;

--------------------------------------------------------------------------------------------------------

---create MI mixed analysis table with start and end date of inclusion eligibility and week of birth----

CREATE TABLE SAILW0972V.V2_VB_MI_SECANAL1 AS (SELECT
		diag.ALF_PE,
		diag.DIAG_DT,
		dic.LATEST_START AS INC_START,
		dic.EARLIEST_END AS INC_END,
		yic.DOD,
		fe.WOB,
		fe.FIRST_EPI_STR_DT AS FIRST_EVENT_DT,
		fe.DIABETES
			FROM sailw0972v.V2_VB_MI_UTI_MIXED AS diag,
				SESSION.V2_VB_DAYS_IN_COHORT AS dic,
				SAILW0972V.V2_VB_WDSD_AGE_IN_COHORT AS yic,
				SAILW0972V.V2_VB_PEDW_EPS_MI_FIRST_EVENT AS fe) WITH NO DATA;
					
INSERT INTO SAILW0972V.V2_VB_MI_SECANAL1 (
		ALF_PE,
		DIAG_DT,
		INC_START,
		INC_END,
		DOD,
		WOB,
		FIRST_EVENT_DT,
		DIABETES)
		SELECT 		diag.ALF_PE,
					diag.DIAG_DT,
					dic.LATEST_START AS INC_START,
					dic.EARLIEST_END AS INC_END,
					yic.DOD,
					fe.WOB,
					fe.FIRST_EPI_STR_DT,
					fe.DIABETES
			FROM sailw0972v.V2_VB_MI_UTI_MIXED AS diag
				LEFT JOIN SESSION.V2_VB_DAYS_IN_COHORT AS dic
					ON diag.ALF_PE = dic.ALF_PE
				LEFT JOIN SAILW0972V.V2_VB_WDSD_AGE_IN_COHORT AS yic
					ON diag.ALF_PE = yic.ALF_PE
				LEFT JOIN SAILW0972V.V2_VB_PEDW_EPS_MI_FIRST_EVENT AS fe
					ON diag.ALF_PE = fe.ALF_PE;
				
--MI add flag to indicate if individual's cohort eligibility ended due to death
				
ALTER TABLE SAILW0972V.V2_VB_MI_SECANAL1
	ADD COLUMN INC_END_DEATH_FG INTEGER;

UPDATE SAILW0972V.V2_VB_MI_SECANAL1
	SET INC_END_DEATH_FG = CASE WHEN DOD = INC_END THEN '1'
							ELSE '0'
						END;
					
--delete cases where UTI does not occur within first period of inclusion from MI table

DELETE FROM SAILW0972V.V2_VB_MI_SECANAL1
	WHERE DIAG_DT NOT BETWEEN INC_START AND INC_END;	

--delete duplicate rows

DELETE FROM 
	(SELECT ROWNUMBER()	OVER(PARTITION BY ALF_PE,
										DIAG_DT,
										INC_START,
										INC_END,
										DOD,
										WOB,
										FIRST_EVENT_DT,
										INC_END_DEATH_FG
								ORDER BY ALF_PE) AS rn
			FROM SAILW0972V.V2_VB_MI_SECANAL1) AS mqo
			WHERE rn > 1;
		
ALTER TABLE SAILW0972V.V2_VB_MI_SECANAL1
	ADD COLUMN PREV_EVENT_FG VARCHAR(5);

MERGE INTO SAILW0972V.V2_VB_MI_SECANAL1 AS prim
	USING (SELECT ALF_PE, PREVIOUS_EVENT FROM SAILW0972V.V2_VB_PEDW_EPS_MI_FIRST_EVENT) AS coh
		ON prim.ALF_PE = coh.ALF_PE
			WHEN MATCHED THEN
				UPDATE
				SET prim.PREV_EVENT_FG = coh.PREVIOUS_EVENT
			;
		
--Amend diabetes and previous event flags to binary

UPDATE SAILW0972V.V2_VB_MI_SECANAL1
	SET DIABETES = CASE WHEN DIABETES = FALSE THEN 0
						ELSE 1
					END;
				
UPDATE SAILW0972V.V2_VB_MI_SECANAL1
	SET PREV_EVENT_FG = CASE WHEN PREV_EVENT_FG = FALSE THEN 0
						ELSE 1
					END;		
					
------------------------------------------------------------------------------------------------------
--Stroke Cohort

-----------------------------------------------------------------------
--create table for the UTI sequences where the most significant result was mixed or heavy mixed growth
--i.e. no confirmed or possible UTI results identified in the surrounding combined UTI sequence (less than 7 days between UTIs)

CREATE TABLE sailw0972v.V2_VB_STROKE_UTI_MIXED
AS (SELECT alf_pe,
			diag_dt,
			group_number
	FROM sailw0972v.V2_VB_STROKE_UTI_COMBINED)
WITH NO data;

INSERT INTO sailw0972v.V2_VB_STROKE_UTI_MIXED
WITH cte AS --find highest outcome in a linked UTI sequence
(SELECT alf_pe, group_number, min(outcome_int) AS highest_outcome
	FROM sailw0972v.V2_VB_STROKE_UTI_COMBINED
GROUP BY alf_pe, group_number),
cte2 AS --find only those linked sequences with highest outcome 3 or 4 (mixed or heavy mixed)
(SELECT * FROM cte WHERE highest_outcome IN (3,4)),
cte3 AS -- minimum diagnosis date 
(SELECT cte.alf_pe,
		cte.group_number,
		min(u.diag_dt) AS diag_dt
		FROM cte
		INNER JOIN sailw0972v.V2_VB_STROKE_UTI_COMBINED AS u
			ON cte.alf_pe = u.alf_pe
			AND cte.group_number = u.group_number
		WHERE u.outcome_int IN (3,4)
	GROUP BY cte.alf_pe,
			cte.group_number)
SELECT DISTINCT 
		cte2.alf_pe,
		cte3.diag_dt,
		cte2.group_number
	FROM sailw0972v.V2_VB_STROKE_UTI_COMBINED AS uti
	INNER JOIN cte2
		ON uti.alf_pe = cte2.alf_pe
		AND uti.group_number = cte2.group_number
	INNER JOIN cte3
		ON uti.alf_pe = cte3.alf_pe
		AND uti.diag_dt = cte3.diag_dt
ORDER BY cte2.alf_pe, cte3.diag_dt;

-------------------------------------------------------------------

---create STROKE mixed secondary analysis table with start and end date of inclusion eligibility and week of birth----

CREATE TABLE SAILW0972V.V2_VB_STROKE_SECANAL1 AS (SELECT
		diag.ALF_PE,
		diag.DIAG_DT,
		dic.LATEST_START AS INC_START,
		dic.EARLIEST_END AS INC_END,
		yic.DOD,
		fe.WOB,
		fe.FIRST_EPI_STR_DT AS FIRST_EVENT_DT,
		fe.DIABETES
			FROM sailw0972v.V2_VB_STROKE_UTI_MIXED AS diag,
				SESSION.V2_VB_DAYS_IN_COHORT AS dic,
				SAILW0972V.V2_VB_WDSD_AGE_IN_COHORT AS yic,
				SAILW0972V.V2_VB_PEDW_EPS_STROKE_FIRST_EVENT AS fe) WITH NO DATA;
					
INSERT INTO SAILW0972V.V2_VB_STROKE_SECANAL1 (
		ALF_PE,
		DIAG_DT,
		INC_START,
		INC_END,
		DOD,
		WOB,
		FIRST_EVENT_DT,
		DIABETES)
		SELECT 		diag.ALF_PE,
					diag.DIAG_DT,
					dic.LATEST_START AS INC_START,
					dic.EARLIEST_END AS INC_END,
					yic.DOD,
					fe.WOB,
					fe.FIRST_EPI_STR_DT,
					fe.DIABETES
			FROM sailw0972v.V2_VB_STROKE_UTI_MIXED AS diag
				LEFT JOIN SESSION.V2_VB_DAYS_IN_COHORT AS dic
					ON diag.ALF_PE = dic.ALF_PE
				LEFT JOIN SAILW0972V.V2_VB_WDSD_AGE_IN_COHORT AS yic
					ON diag.ALF_PE = yic.ALF_PE
				LEFT JOIN SAILW0972V.V2_VB_PEDW_EPS_STROKE_FIRST_EVENT AS fe
					ON diag.ALF_PE = fe.ALF_PE;
				
--add flag to indicate if individual's cohort eligibility ended due to death
				
ALTER TABLE SAILW0972V.V2_VB_STROKE_SECANAL1
	ADD COLUMN INC_END_DEATH_FG INTEGER;

UPDATE SAILW0972V.V2_VB_STROKE_SECANAL1
	SET INC_END_DEATH_FG = CASE WHEN DOD = INC_END THEN '1'
							ELSE '0'
						END;
					
-------------------------------------------------------------------------------------					
--delete cases where UTI does not occur within first period of inclusion from stroke table

DELETE FROM SAILW0972V.V2_VB_STROKE_SECANAL1
	WHERE DIAG_DT NOT BETWEEN INC_START AND INC_END;

--delete duplicate rows

DELETE FROM 
	(SELECT ROWNUMBER()	OVER(PARTITION BY ALF_PE,
										DIAG_DT,
										INC_START,
										INC_END,
										DOD,
										WOB,
										FIRST_EVENT_DT,
										INC_END_DEATH_FG
								ORDER BY ALF_PE) AS rn
			FROM SAILW0972V.V2_VB_STROKE_SECANAL1) AS mqo
			WHERE rn > 1;
		
ALTER TABLE SAILW0972V.V2_VB_STROKE_SECANAL1
	ADD COLUMN PREV_EVENT_FG VARCHAR(5);

MERGE INTO SAILW0972V.V2_VB_STROKE_SECANAL1 AS prim
	USING (SELECT ALF_PE, PREVIOUS_EVENT FROM SAILW0972V.V2_VB_PEDW_EPS_STROKE_FIRST_EVENT) AS coh
		ON prim.ALF_PE = coh.ALF_PE
			WHEN MATCHED THEN
				UPDATE
				SET prim.PREV_EVENT_FG = coh.PREVIOUS_EVENT
			;	
		
--Amend diabetes and previous event flags to binary

UPDATE SAILW0972V.V2_VB_STROKE_SECANAL1
	SET DIABETES = CASE WHEN DIABETES = FALSE THEN 0
						ELSE 1
					END;
				
UPDATE SAILW0972V.V2_VB_STROKE_SECANAL1
	SET PREV_EVENT_FG = CASE WHEN PREV_EVENT_FG = FALSE THEN 0
						ELSE 1
					END;