TT 1.4 target_trial_urine_AMR_WRRS_2plus_7day_caltime.sql

---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
--***MUST RUN SCRIPT TT 1.1 target_trial_hosp_AMR_7day_caltime PRIOR TO THIS SCRIPT***
--initial table sailw1169v.VB_7day_TARGET_TRIAL_PRE2

--This script builds upon the cohorts developed for Recurrent urinary tract infections
--and prophylactic antibiotic use in women: a cross-sectional study in primary care,
--for the years 2015 to 2020.
--further information and code is available in github respository 
--https://github.com/SwanseaUniversityDataScience/ImPART/
--initial table SAILW1169V.COHORT_WRRS_RESULTS_AGREED
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

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

--Urine AMR identified in WRRS for 2+ different Abx types

--identified AS ANY urine test of interest in identifying UTIs with a specimen collected
--date after the patient's recurrent UTI diagnosis.
--AMR established as resistant record for Nitrofurantoin, Trimethoprim or Cefalexin 
--regardless of UTI status of the test.

---------------------------------------------------------------------------------------
--Abx resistant WRRS urine test
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

CALL FNC.DROP_IF_EXISTS('SAILW1169V.VB_TARGET_TRIAL_WRRS_AMR_2PLUS');

CREATE TABLE SAILW1169V.VB_TARGET_TRIAL_WRRS_AMR_2PLUS AS
(SELECT coh.ALF_PE,
		coh.DIAG_DATE,
		wrrs.SPCM_COLLECTED_DT,
		wrrs.REPORT_SEQ,
		wrrs.REQUEST_SEQ,
		wrrs.REQUEST_NAME,
		wrrs.CULTURE,
		wrrs.CULTURE2,
		wrrs.CULTURE3,
		wrrs.ORGANISM,
		wrrs.ORGANISM2,
		wrrs.ORGANISM3,
		wrrs.RED_BLOOD_CELL_COUNT,
		wrrs.WHITE_BLOOD_CELL_COUNT,
		wrrs.TRIMETHOPRIM,
		wrrs.NITROFURANTOIN,
		wrrs.GENTAMICIN,
		wrrs.AMOXICILLIN,
		wrrs.AMOXICILLIN_CLAVULANATE,
		wrrs.CEPHALEXIN,
		wrrs.PIVMECILLINAM,
		wrrs.FOSFOMYCIN,
		wrrs.CIPROFLOXACIN,
		wrrs.COAMOXICLAV,
		wrrs.ESCO,
		wrrs.ORGANISM_COUNT,
		wrrs.UTI_OUTCOME,
		wrrs.DIAG_ORGANISM,
		wrrs.PROV_DEPT_SITEID
FROM 
sailw1169v.V15VB_COHORT1 AS coh,
SAILW1169V.COHORT_WRRS_RESULTS_AGREED AS wrrs)
WITH NO DATA;

ALTER TABLE SAILW1169V.VB_TARGET_TRIAL_WRRS_AMR_2PLUS
ADD COLUMN resistant_count integer;

INSERT INTO SAILW1169V.VB_TARGET_TRIAL_WRRS_AMR_2PLUS
SELECT coh.ALF_PE,
		coh.DIAG_DATE,
		wrrs.SPCM_COLLECTED_DT,
		wrrs.REPORT_SEQ,
		wrrs.REQUEST_SEQ,
		wrrs.REQUEST_NAME,
		wrrs.CULTURE,
		wrrs.CULTURE2,
		wrrs.CULTURE3,
		wrrs.ORGANISM,
		wrrs.ORGANISM2,
		wrrs.ORGANISM3,
		wrrs.RED_BLOOD_CELL_COUNT,
		wrrs.WHITE_BLOOD_CELL_COUNT,
		wrrs.TRIMETHOPRIM,
		wrrs.NITROFURANTOIN,
		wrrs.GENTAMICIN,
		wrrs.AMOXICILLIN,
		wrrs.AMOXICILLIN_CLAVULANATE,
		wrrs.CEPHALEXIN,
		wrrs.PIVMECILLINAM,
		wrrs.FOSFOMYCIN,
		wrrs.CIPROFLOXACIN,
		wrrs.COAMOXICLAV,
		wrrs.ESCO,
		wrrs.ORGANISM_COUNT,
		wrrs.UTI_OUTCOME,
		wrrs.DIAG_ORGANISM,
		wrrs.PROV_DEPT_SITEID,
		mqo.resistant_count
FROM sailw1169v.V15VB_COHORT1 AS coh
LEFT JOIN SAILW1169V.COHORT_WRRS_RESULTS_AGREED AS wrrs
ON coh.ALF_PE = wrrs.ALF_PE
INNER JOIN (SELECT alf_pe,
		SPCM_COLLECTED_DT,
		REPORT_SEQ,
		REQUEST_SEQ,
		count(CASE WHEN TRIMETHOPRIM = 'Resistant' THEN 1 END) + 
		count(CASE WHEN NITROFURANTOIN = 'Resistant' THEN 1 END) +
		count(CASE WHEN AMOXICILLIN = 'Resistant' THEN 1 END) +
		count(CASE WHEN AMOXICILLIN_CLAVULANATE = 'Resistant' THEN 1
					WHEN COAMOXICLAV  = 'Resistant' THEN 1 END) +
		count(CASE WHEN CEPHALEXIN = 'Resistant' THEN 1 END) +
		count(CASE WHEN PIVMECILLINAM = 'Resistant' THEN 1 END) +
		count(CASE WHEN FOSFOMYCIN = 'Resistant' THEN 1 END) +
		count(CASE WHEN CIPROFLOXACIN = 'Resistant' THEN 1 END)
		AS resistant_count
FROM SAILW1169V.COHORT_WRRS_RESULTS_AGREED
GROUP BY alf_pe, SPCM_COLLECTED_DT, REPORT_SEQ, REQUEST_SEQ) AS mqo
ON wrrs.alf_pe = mqo.alf_pe
AND wrrs.SPCM_COLLECTED_DT = mqo.spcm_collected_dt
AND wrrs.REPORT_SEQ = mqo.report_seq
AND wrrs.REQUEST_SEQ = mqo.request_seq
WHERE wrrs.SPCM_COLLECTED_DT > coh.DIAG_DATE
AND mqo.resistant_count > 1;

---------------------------------------------------------------------------------------				
--delete all but first specimen collected date for AMR urine test

DELETE FROM 
	(SELECT ROWNUMBER()	OVER(PARTITION BY ALF_PE ORDER BY SPCM_COLLECTED_DT) AS rn
			FROM SAILW1169V.VB_TARGET_TRIAL_WRRS_AMR_2PLUS) AS mqo
			WHERE rn > 1;
			
COMMIT;

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--create table for target trial UTI outcome with 2+ resistance

CALL fnc.drop_if_exists('sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS');

CREATE TABLE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
AS (SELECT * FROM sailw1169v.VB_7day_TARGET_TRIAL_PRE2)
WITH NO DATA;

INSERT INTO sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SELECT * FROM sailw1169v.VB_7day_TARGET_TRIAL_PRE2;

--update target trial table with amr date

ALTER TABLE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
ADD COLUMN URINE_AMR_dt date
ADD COLUMN URINE_AMR integer;

MERGE INTO  sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS tt
	USING (SELECT DISTINCT alf_pe, spcm_collected_dt FROM SAILW1169V.VB_TARGET_TRIAL_WRRS_AMR_2PLUS) AS amr
		ON tt.alf_pe = amr.alf_pe
			WHEN MATCHED THEN
				UPDATE
				SET tt.URINE_AMR_dt = amr.spcm_collected_dt
			;
		
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET URINE_AMR = CASE WHEN URINE_AMR_dt IS null
						THEN 0
				WHEN (URINE_AMR_dt > dod
					OR URINE_AMR_dt > GP_END_DT
					OR URINE_AMR_dt > study_end
					OR URINE_AMR_dt > ruti_diag_dt + 12 months)
						THEN 0
				WHEN URINE_AMR_dt IS NOT null
					AND URINE_AMR_dt between cal_dt AND cal_dt + 6 DAYS
					THEN 1
				ELSE 0
			END;
		
--update table to ensure only the first outcome is flagged if occuring in the same cal_time		
		
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET death = CASE WHEN URINE_AMR = 1
			THEN 0
			ELSE DEATH
		END;
	
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET ltf = CASE WHEN URINE_AMR = 1
			THEN 0
			ELSE LTF
		END;
	
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET study_ended = CASE WHEN URINE_AMR = 1
			THEN 0
			ELSE STUDY_ENDED 
		END;
	
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET DIAG_12MONTHS = CASE WHEN URINE_AMR = 1
			THEN 0
			ELSE DIAG_12MONTHS
		END;
	
--adjust the end reason flag to the earliest of outcome and new_pabx when they occur in the same cal_time
--if occuring on the same day then prioritise outcome
	
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET NEW_PABX = CASE WHEN URINE_AMR = 1
                AND NEW_PABX = 1
                AND URINE_AMR_dt < NEXT_PABX_STR
			THEN 0
			ELSE NEW_PABX 
		END;
	
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET URINE_AMR = CASE WHEN URINE_AMR = 1
                AND NEW_PABX = 1
			THEN 0
			ELSE URINE_AMR
		END;

---------------------------------------------------------------------
---------------------------------------------------------------------
--DELETE ANY ROWS WHERE CAL_TIME OVER THE AMR OUTCOME

DELETE FROM sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS amr
    WHERE URINE_AMR_dt NOT BETWEEN cal_dt AND CAL_dt + 6 days
	AND cal_dt >= URINE_AMR_dt;
 
       
DELETE FROM sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS amr
    WHERE next_pabx_str NOT BETWEEN cal_dt AND CAL_dt + 6 days
	AND cal_dt >= next_pabx_str;

----------------------------------------------------------------------------
--calculate inclusion days

CALL fnc.drop_if_exists('SESSION.VB_Inc_duration');
		
DECLARE GLOBAL TEMPORARY TABLE SESSION.VB_Inc_duration
(alf_pe varchar(15),
inc_dur integer)
ON COMMIT PRESERVE rows;

COMMIT;

INSERT INTO SESSION.VB_Inc_duration
SELECT DISTINCT tt.alf_pe, days_between(ex.exit_dt, tt.ruti_diag_dt) AS inc_dur
FROM sailw1169v.VB_7day_target_trial_pre2 AS tt
INNER join
(SELECT DISTINCT alf_pe,
		CASE WHEN sum(ltf) = 1
				THEN gp_end_dt
			WHEN sum(death) = 1
				THEN dod
			WHEN sum(URINE_AMR) = 1
				THEN URINE_AMR_dt
			WHEN sum(new_pabx) = 1
				THEN next_pabx_str
			WHEN sum(diag_12months) = 1
				THEN ruti_diag_dt + 12 months
			ELSE study_end
		END AS exit_dt
	FROM sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
GROUP BY alf_pe, gp_end_dt, gp_end_dt, URINE_AMR_dt, dod, study_end, next_pabx_str, ruti_diag_dt
ORDER BY alf_pe) AS ex
ON tt.alf_pe = ex.alf_pe;

COMMIT;

----------------------------------------------------------------------------------
--add WRRS organism growth in the 12 months up to and including rUTI date indicator

----------------------------------------------------------------------------------
--identify where multiple WRRS results on the same day closest to rUTI diag date
--assign hierarchy as follows:
	--confirmed UTI
	--possible UTI
	--heavy mixed growth
	--mixed growth
	--no micro evidence
	--exclude NULL culture

CALL fnc.drop_if_exists('SESSION.Multi_uti_outcomes');

DECLARE GLOBAL TEMPORARY TABLE SESSION.Multi_uti_outcomes
AS (SELECT alf_pe,
			spcm_collected_dt,
			uti_outcome,
			alf_pe AS UTI_CD
		FROM SAILW1169V.COHORT_WRRS_RESULTS_AGREED)
DEFINITION ONLY
ON COMMIT PRESERVE ROWS;

COMMIT;

INSERT INTO SESSION.Multi_uti_outcomes
WITH cte as
(SELECT coh.alf_pe, coh.RUTI_DIAG_DT, max(SPCM_COLLECTED_DT) AS closest_spec_dt FROM SAILW1169V.COHORT_WRRS_RESULTS_AGREED AS wrrs
INNER JOIN sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS coh
ON wrrs.alf_pe = coh.ALF_PE
WHERE wrrs.SPCM_COLLECTED_DT BETWEEN coh.RUTI_DIAG_DT - 1 YEAR AND coh.RUTI_DIAG_DT
GROUP BY coh.alf_pe, coh.RUTI_DIAG_DT),
cte2 as
(SELECT wrrs1.* FROM SAILW1169V.COHORT_WRRS_RESULTS_AGREED AS wrrs1
INNER JOIN cte
ON wrrs1.alf_pe = cte.alf_pe
AND wrrs1.SPCM_COLLECTED_DT = cte.closest_spec_dt
ORDER BY wrrs1.alf_pe, wrrs1.spcm_collected_dt),
cte3 as
(SELECT alf_pe||spcm_collected_dt AS ALF_ID, count(alf_pe||spcm_collected_dt) AS spec_count
FROM cte2
GROUP BY alf_pe||spcm_collected_dt
HAVING count(alf_pe||spcm_collected_dt) > 1)
SELECT DISTINCT wr.alf_pe, 
				wr.SPCM_COLLECTED_DT,
				wr.UTI_OUTCOME,
				CASE WHEN uti_outcome = 'Confirmed UTI'
					THEN 1
						WHEN uti_outcome = 'Possible UTI'
					THEN 2
						WHEN uti_outcome = 'Heavy mixed growth'
					THEN 3
						WHEN uti_outcome = 'Mixed growth'
					THEN 4
						WHEN uti_outcome = 'No microbiological evidence of UTI'
					THEN 5
						WHEN uti_outcome = 'Exclude NULL culture'
					THEN 6
				END AS UTI_CD 
FROM SAILW1169V.COHORT_WRRS_RESULTS_AGREED AS wr
INNER JOIN cte3
ON cte3.alf_id = wr.alf_pe||spcm_collected_dt
ORDER BY wr.ALF_PE, wr.SPCM_COLLECTED_DT, uti_cd;

COMMIT;

DELETE FROM 
	(SELECT ROWNUMBER()	OVER(PARTITION BY ALF_PE, spcm_collected_dt ORDER BY UTI_CD) AS rn
			FROM SESSION.Multi_uti_outcomes) AS mqo
			WHERE rn > 1;

COMMIT;

--create closest WRRS result table, using above code where there were >1 result on same day

CALL fnc.drop_if_exists('sailw1169v.VB_7day_target_trial_closest_wrrs_2plus');

CREATE TABLE sailw1169v.VB_7day_target_trial_closest_wrrs_2plus
AS (SELECT alf_pe,
			spcm_collected_dt,
			uti_outcome,
			alf_pe AS UTI_IND
		FROM SAILW1169V.COHORT_WRRS_RESULTS_AGREED)
	WITH NO DATA;

INSERT INTO sailw1169v.VB_7day_target_trial_closest_wrrs_2plus
WITH cte as
(SELECT coh.alf_pe, coh.RUTI_DIAG_DT, max(SPCM_COLLECTED_DT) AS closest_spec_dt FROM SAILW1169V.COHORT_WRRS_RESULTS_AGREED AS wrrs
INNER JOIN sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS coh
ON wrrs.alf_pe = coh.ALF_PE
WHERE wrrs.SPCM_COLLECTED_DT BETWEEN coh.RUTI_DIAG_DT - 1 YEAR AND coh.RUTI_DIAG_DT
GROUP BY coh.alf_pe, coh.RUTI_DIAG_DT),
cte2 as
(SELECT wrrs1.* FROM SAILW1169V.COHORT_WRRS_RESULTS_AGREED AS wrrs1
INNER JOIN cte
ON wrrs1.alf_pe = cte.alf_pe
AND wrrs1.SPCM_COLLECTED_DT = cte.closest_spec_dt
ORDER BY wrrs1.alf_pe, wrrs1.spcm_collected_dt),
cte3 as
(SELECT alf_pe||spcm_collected_dt AS ALF_ID, count(alf_pe||spcm_collected_dt) AS spec_count
FROM cte2
GROUP BY alf_pe||spcm_collected_dt
HAVING count(alf_pe||spcm_collected_dt) = 1)
SELECT DISTINCT wr.alf_pe, 
				wr.SPCM_COLLECTED_DT,
				wr.UTI_OUTCOME,
				CASE WHEN wr.UTI_OUTCOME IN ('Confirmed UTI',
											'Possible UTI')
						THEN 1
					WHEN wr.UTI_OUTCOME IN ('Heavy mixed growth',
											'Mixed growth')
						THEN 2
					WHEN wr.UTI_OUTCOME IN ('No microbiological evidence of UTI',
											'Exclude NULL culture')
						THEN 0
				end
FROM SAILW1169V.COHORT_WRRS_RESULTS_AGREED AS wr
INNER JOIN cte3
ON cte3.alf_id = wr.alf_pe||spcm_collected_dt
UNION
SELECT alf_pe,
		spcm_collected_dt,
		uti_outcome,
		CASE WHEN UTI_OUTCOME IN ('Confirmed UTI',
									'Possible UTI')
				THEN 1
			WHEN UTI_OUTCOME IN ('Heavy mixed growth',
									'Mixed growth')
				THEN 2
			WHEN UTI_OUTCOME IN ('No microbiological evidence of UTI',
									'Exclude NULL culture')
				THEN 0
		end
FROM SESSION.Multi_uti_outcomes;

--Update AMR table with confirmed or possible UTI = 1
					--	heavy mixed growth or mixed growth = 2
					--	no micro evidence or exclude NULL culture = 3
					--	no WRRS in prior 12 months = NULL
					
ALTER TABLE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
ADD COLUMN MOST_RECENT_URINE integer;
				
MERGE INTO sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS coh
	USING sailw1169v.VB_7day_target_trial_closest_wrrs_2plus AS wr
		ON coh.ALF_PE = wr.ALF_PE
			WHEN MATCHED THEN
				UPDATE
				SET coh.MOST_RECENT_URINE = wr.UTI_IND
			;
		
----------------------------------------------------------------------------------
--Add pAbx type field

ALTER TABLE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
ADD COLUMN PABX_TYPE varchar(20);

MERGE INTO sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS coh
USING
(SELECT DISTINCT mindt.alf_pe, mindt.PABX_AFTER_RUTI1, pabx.ALT_ABX_TYPE
from
(SELECT mqo.ALF_PE, min(mqo.PABX_AFTER_RUTI1) AS PABX_AFTER_RUTI1 FROM
(SELECT cohp.*, pabxp.PABX_STR AS PABX_AFTER_RUTI1 FROM sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS cohp
LEFT JOIN SAILW1169V.VB_ALTERNATING_PABX_COH3 AS pabxp
ON cohp.ALF_PE = pabxp.ALF_PE
AND MONTHS_BETWEEN(pabxp.PABX_STR,cohp.ruti_diag_dt) >= 0
AND MONTHS_BETWEEN(pabxp.PABX_STR,cohp.ruti_diag_dt) < 12
ORDER BY ALF_PE) AS mqo
WHERE mqo.PABX_AFTER_RUTI1 IS NOT NULL
GROUP BY mqo.ALF_PE) AS mindt
INNER JOIN SAILW1169V.VB_ALTERNATING_PABX_COH3 AS pabx
ON mindt.alf_pe = pabx.alf_pe
AND mindt.PABX_AFTER_RUTI1 = pabx.PABX_STR)
AS pabx
		ON coh.ALF_PE = pabx.ALF_PE
			WHEN MATCHED THEN
				UPDATE
				SET coh.PABX_TYPE = pabx.ALT_ABX_TYPE;
			
----------------------------------------------------------------------------------
--update pabx_str and pabx_end to null/inclusion end date where this occurs outside 
--the study period or criteria.

UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET pabx_str = CASE WHEN pabx_str > MIN_END_DT
					THEN NULL
					ELSE pabx_str
				end;
			
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET pabx_end = CASE WHEN pabx_str IS NULL
					THEN NULL
				ELSE CASE WHEN pabx_end > MIN_END_DT
					THEN MIN_END_DT
					ELSE pabx_end
				END
			end;
		
----------------------------------------------------------------------------------
--add column pabx_duration

ALTER TABLE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
ADD COLUMN pabx_duration integer;

UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET pabx_duration = CASE WHEN pabx_str IS NULL 
							then NULL
						ELSE days_between(PABX_END, first_abx_dt)
					END;
				
----------------------------------------------------------------------------------
--update pabx_str_all and pabx_end_all to null/inclusion end date where this occurs
--outside the study period or criteria.

UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET pabx_str_all = CASE WHEN pabx_str_all > MIN_END_DT
					THEN NULL
					ELSE pabx_str_all
				end;
			
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET pabx_end_all = CASE WHEN pabx_str_all IS NULL
					THEN NULL
				ELSE CASE WHEN pabx_end_all > MIN_END_DT
					THEN MIN_END_DT
					ELSE pabx_end_all
				END
			end;
		
----------------------------------------------------------------------------------
--add column pabx_duration_all

ALTER TABLE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
ADD COLUMN pabx_duration_all integer;

UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
SET pabx_duration_all = CASE WHEN pabx_str_all IS NULL 
							then NULL
						ELSE days_between(PABX_END_all, PABX_ALL_FIRST_SCRIPT)
					END;
		
----------------------------------------------------------------------------------
--add column to identify whether death occurs between amr_outcome and study end date or gp_end
--do not flag if lost to follow up due to gp_end prior to study end in case they have
--moved out of the country and we would not have follow up data.

ALTER table sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
ADD COLUMN death_after_outcome integer;

UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS tt
SET death_after_outcome = 1
	WHERE tt.alf_pe IN 
	(SELECT DISTINCT coh.alf_pe FROM sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS coh
INNER join
(SELECT * FROM (SELECT alf_pe, min(COALESCE(gp_end_dt, STUDY_END)) AS min_end
FROM sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
GROUP BY alf_pe) AS mqo) AS jj
ON coh.alf_pe = jj.alf_pe
WHERE coh.dod BETWEEN coh.URINE_AMR_DT AND jj.min_end);

UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS tt
SET death_after_outcome = CASE WHEN death_after_outcome = 1
								THEN death_after_outcome
							ELSE 0 
						END;
					
---------------------------------------------------------------------------------
--add column for next UTI

ALTER TABLE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS
ADD COLUMN next_uti_dt date
ADD COLUMN next_uti integer;

ALTER TABLE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS activate NOT logged INITIALLY;			
					
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS t1
SET t1.next_uti_dt = 
(SELECT min(uti.EVENT_STR_DT)
	FROM SAILW1169V.VB_ALL_UTI_COMB AS uti
	INNER JOIN sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS amr
		ON uti.ALF_PE = amr.ALF_PE
		AND uti.EVENT_STR_DT > amr.RUTI_DIAG_DT
WHERE t1.alf_pe = uti.alf_pe
GROUP BY uti.alf_pe);

COMMIT;
					
UPDATE sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS amr
SET next_uti = case when amr.next_uti_dt
					BETWEEN cal_dt AND cal_dt + 6 DAYS THEN 1
						ELSE 0
				end;
			
------------------------------------------------------------------------------------
--additional exclusion criteria, delete all patients from the cohort where first 
--prescription was prior to baseline.

DELETE from sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS amr
WHERE amr.alf_pe IN (SELECT DISTINCT coh.alf_pe FROM sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS  AS coh --working tab
INNER JOIN SAILW1169V.VB_ALTERNATING_PABX_COH3 AS pabx
ON coh.ALF_pe = pabx.ALF_PE
AND coh.PABX_STR = pabx.PABX_STR
WHERE pabx.ALT_STR_DT < ruti_diag_dt
AND pabx.PABX_STR >= ruti_diag_dt
ORDER BY coh.alf_pe);
				
---------------------------------------------------------------------------------
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
--create outcome 1 hospital amr event table
--sets subsequent pAbx date information to NULL if occurring after study end date

CALL fnc.drop_if_exists('sailw1169v.VB_7day_target_trial_urine_amr_2plus');

CREATE TABLE sailw1169v.VB_7day_target_trial_urine_amr_2plus
(ALF varchar(15),
RUTI_DIAGDATE date,
cal_time integer,
fup_obs integer,
birth_week date,
ruti_age integer,
ethnic integer,
WIMD integer,
BMI integer,
smoking integer,
alcohol integer,
frailty integer,
diab integer,
cancer integer,
ckd integer,
htn integer,
ihd integer,
cvd integer,
ccf integer,
ms integer,
mnd integer,
dementia integer,
pd integer,
severe_ment integer,
asthma integer,
copd integer,
immun_supp integer,
renal_stone integer,
urin_tract_abnorm integer,
amr_baseline integer,
MOST_RECENT_URINE integer,
utis_gp_baseline integer,
utis_hosp_baseline integer,
gp_prescrib_rate double,
pabx_start integer,
pabx_type varchar(20),
dose varchar(15),
dose_consistency varchar(20),
time_to_pabx integer,
pabx_duration integer,
all_pabx integer,
all_pabx_duration integer,
gp_int_days integer,
urine_amr integer,
urine_amr_dt date,
death integer,
death_dt date,
fu_loss integer,
fu_loss_dt date,
study_end integer,
diag_12months integer,
diag_12months_dt date,
new_pabx_str integer,
new_pabx_str_dt date,
death_after_outcome integer,
next_uti integer)
NOT logged initially;


INSERT INTO sailw1169v.VB_7day_target_trial_urine_amr_2plus
SELECT tt.alf_pe,
		tt.RUTI_DIAG_Dt,
		tt.cal_time,
		dur.inc_dur,
		pre.wob,
		pre.agediag,
		pre.ethnic,
		pre.WIMD_2019_quintile,
		pre.BMI_VAL,
		pre.smok,
		pre.alcohol_flg,
		pre.efi,
		pre.diabetes,
		pre.cancer,
		pre.renal_disease,
		pre.hypertension,
		pre.cvd,
		pre.cerebrov,
		pre.heartfail,
		pre.ms,
		pre.mnd,
		pre.dementia,
		pre.parkinsons,
		pre.smh,
		pre.asthma,
		pre.copd,
		pre.immun_supp,
		pre.renal_stone,
		pre.abn_renal,
		pre.amr_baseline,
		tt.MOST_RECENT_URINE,
		pre.gp_uti_pre,
		pre.pedw_uti_pre,
		pre.gp_presc_rt,
		CASE WHEN (tt.pabx_str > tt.MIN_END_DT
					OR tt.pabx_str IS NULL
					OR tt.pabx_str > tt.URINE_AMR_DT)
			THEN 0 
				ELSE tt.PABX
			end,
		CASE WHEN (tt.pabx_str > tt.MIN_END_DT
					OR tt.pabx_str IS NULL
					OR tt.pabx_str > tt.URINE_AMR_DT)
			THEN NULL
				ELSE tt.pabx_type
			end,
		CASE WHEN (tt.pabx_str > tt.MIN_END_DT
					OR tt.pabx_str IS NULL
					OR tt.pabx_str > tt.URINE_AMR_DT)
			THEN NULL
				ELSE pre.dose
			end,
		CASE WHEN (tt.pabx_str > tt.MIN_END_DT
					OR tt.pabx_str IS NULL
					OR tt.pabx_str > tt.URINE_AMR_DT)
			THEN NULL
				ELSE pre.dose_consistency
			end,
		CASE WHEN (tt.pabx_str > tt.MIN_END_DT
					OR tt.pabx_str IS NULL
					OR tt.pabx_str > tt.URINE_AMR_DT)
			THEN NULL 
				ELSE pre.days_to_pabx
			end,
		CASE WHEN (tt.pabx_str > tt.MIN_END_DT
					OR tt.pabx_str IS NULL
					OR tt.pabx_str > tt.URINE_AMR_DT)
			THEN NULL 
				ELSE tt.pabx_duration
			end,
		CASE WHEN (tt.pabx_str_all > tt.MIN_END_DT
					OR tt.pabx_str_all IS NULL
					OR tt.pabx_str_all > tt.URINE_AMR_DT)
			THEN 0
				ELSE tt.all_pabx
			end,
		CASE WHEN (tt.pabx_str_all > tt.MIN_END_DT
					OR tt.pabx_str_all IS NULL
					OR tt.pabx_str_all > tt.URINE_AMR_DT)
			THEN NULL 
				ELSE tt.pabx_duration_all
			end,
		pre.gp_int_days,
		tt.URINE_AMR,
		tt.urine_amr_dt,
		tt.death,
		tt.dod,
		tt.ltf,
		tt.gp_end_dt,
		tt.study_ended,
		tt.diag_12months,
		tt.ruti_diag_dt + 12 MONTHS,
		tt.new_pabx,
		tt.next_pabx_str,
		tt.death_after_outcome,
		tt.NEXT_UTI
FROM sailw1169v.VB_7day_TARGET_TRIAL_AMR_WRRS_2PLUS AS tt
	INNER JOIN sailw1169v.VB_7day_target_trial_pre AS pre
	ON tt.alf_pe = pre.alf_pe
	INNER JOIN SESSION.VB_Inc_duration AS dur
	ON tt.alf_pe = dur.alf_pe
ORDER BY alf_pe, cal_dt;

GRANT ALL ON sailw1169v.VB_7day_target_trial_urine_amr_2plus TO USER sanyaoll;