Notes

/******* Find the duration between "entry to the clinic " to RRMS and SPMS index date *************/
--
drop table if exists ATS.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS;
create temporary table if not exists ATS.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS as

select PATIENT_ID, "Phenotype" as Phenotype, ENTRY_IN_THE_CLINIC, DATE_MSCOURSE_2, DATE_MSCOURSE_3,
DATE_OF_ONSET,
datediff('years', DATE_OF_ONSET, ENTRY_IN_THE_CLINIC) as DOS_to_Entry_in_Clinic,
datediff('years', ENTRY_IN_THE_CLINIC, DATE_MSCOURSE_2) as Entry_in_clinic_to_RRMS,
datediff('years', ENTRY_IN_THE_CLINIC, DATE_MSCOURSE_3) as Entry_in_clinic_to_SPMS,
case
when DOS_to_Entry_in_Clinic <-12 then '<-12'
when DOS_to_Entry_in_Clinic between -12 and -7 then '-12 to -7'
when DOS_to_Entry_in_Clinic between -6 and -5 then '-6 to -5'
when DOS_to_Entry_in_Clinic between -4 and -3 then '-4 to -3'
when DOS_to_Entry_in_Clinic between -2 and -1 then '-2 to -1'
when DOS_to_Entry_in_Clinic between 0 and 1 then '0 to 1'
when DOS_to_Entry_in_Clinic between 2 and 3 then '2 to 3'
when DOS_to_Entry_in_Clinic between 4 and 6 then '4 to 6'
when DOS_to_Entry_in_Clinic between 7 and 12 then '7 to 12'
when DOS_to_Entry_in_Clinic >12 then '>12'
end as DOS_to_Entry_in_Clinic_Period,
case
when Entry_in_clinic_to_RRMS <-12 then '<-12'
when Entry_in_clinic_to_RRMS between -12 and -7 then '-12 to -7'
when Entry_in_clinic_to_RRMS between -6 and -5 then '-6 to -5'
when Entry_in_clinic_to_RRMS between -4 and -3 then '-4 to -3'
when Entry_in_clinic_to_RRMS between -2 and -1 then '-2 to -1'
when Entry_in_clinic_to_RRMS between 0 and 1 then '0 to 1'
when Entry_in_clinic_to_RRMS between 2 and 3 then '2 to 3'
when Entry_in_clinic_to_RRMS between 4 and 6 then '4 to 6'
when Entry_in_clinic_to_RRMS between 7 and 12 then '7 to 12'
when Entry_in_clinic_to_RRMS >12 then '>12'
end as Entry_in_clinic_to_RRMS_Period,
case
when Entry_in_clinic_to_SPMS <-12 then '<-12'
when Entry_in_clinic_to_SPMS between -12 and -7 then '-12 to -7'
when Entry_in_clinic_to_SPMS between -6 and -5 then '-6 to -5'
when Entry_in_clinic_to_SPMS between -4 and -3 then '-4 to -3'
when Entry_in_clinic_to_SPMS between -2 and -1 then '-2 to -1'
when Entry_in_clinic_to_SPMS between 0 and 1 then '0 to 1'
when Entry_in_clinic_to_SPMS between 2 and 3 then '2 to 3'
when Entry_in_clinic_to_SPMS between 4 and 6 then '4 to 6'
when Entry_in_clinic_to_SPMS between 7 and 12 then '7 to 12'
when Entry_in_clinic_to_SPMS >12 then '>12'
end as Entry_in_clinic_to_SPMS_Period
from ats.NOVARTIS_KUWAIT_LOT_IDENTIFICATION id
join ats.NOVARTIS_KUWAIT_MS_RAW_PATIENTID_MS_PHENOTYPE as ms on id.PATIENT_ID=ms."Patient_ID"
where date_part('year', ENTRY_IN_THE_CLINIC)<=2021

--
select max(ENTRY_IN_THE_CLINIC) from ats.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS

/********* Entry in the clinic to Index date of RRMS/SPMS *******/
---- RRMS

select Phenotype, ENTRY_IN_CLINIC_TO_RRMS_PERIOD, count(ENTRY_IN_CLINIC_TO_RRMS_PERIOD) as Num_of_patients from(
select * from ats.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS
where Phenotype = 'RRMS')
group by Phenotype, ENTRY_IN_CLINIC_TO_RRMS_PERIOD
order by PHENOTYPE

select * from ats.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS
where Phenotype = 'RRMS'


---- SPMS

select Phenotype, Entry_in_clinic_to_SPMS_PERIOD, count(Entry_in_clinic_to_SPMS_PERIOD) as Num_of_patients from(
select * from ats.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS
where Phenotype = 'SPMS')
group by Phenotype, Entry_in_clinic_to_SPMS_PERIOD
order by PHENOTYPE

select * from ats.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS
where Phenotype = 'SPMS'

select max(DATE_MSCOURSE_3) as entr from ats.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS

/**************** Date of Onset (Onset of symptomns) to Entry in the clinic to **/
-- RRMS

select Phenotype, DOS_to_Entry_in_Clinic_Period, count(DOS_to_Entry_in_Clinic_Period) as Num_of_patients from(
select * from ats.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS
where Phenotype = 'RRMS')
group by Phenotype, DOS_to_Entry_in_Clinic_Period
order by PHENOTYPE

select PATIENT_ID, PHENOTYPE, ENTRY_IN_THE_CLINIC, DATE_OF_ONSET, DOS_TO_ENTRY_IN_CLINIC, DOS_TO_ENTRY_IN_CLINIC_PERIOD from ats.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS
where Phenotype = 'RRMS'

-- SPMS

select Phenotype, DOS_to_Entry_in_Clinic_Period, count(DOS_to_Entry_in_Clinic_Period) as Num_of_patients from(
select * from ats.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS
where Phenotype = 'SPMS')
group by Phenotype, DOS_to_Entry_in_Clinic_Period
order by PHENOTYPE

select PATIENT_ID, PHENOTYPE, ENTRY_IN_THE_CLINIC, DATE_OF_ONSET, DOS_TO_ENTRY_IN_CLINIC, DOS_TO_ENTRY_IN_CLINIC_PERIOD from ats.NOVARTIS_KUWAIT_ENTRY_IN_CLINIC_TO_RRMS_SPMS
where Phenotype = 'SPMS'


/*********************** Pull Event Table **********/

select EVENT_VALUE, count(EVENT_VALUE) from(
select lot.PATIENT_ID, ms."Phenotype" as Phenotype, EVENT_DATE, EVENT_TYPE, EVENT_VALUE, START_REASON,LOT_NUM
from ats.NOVARTIS_KUWAIT_LOT_EVENT_START_REASON_LOT lot
join ats.NOVARTIS_KUWAIT_MS_RAW_PATIENTID_MS_PHENOTYPE as ms on lot.PATIENT_ID=ms."Patient_ID"
where Event_Value in ('RR', 'SP')
)
group by EVENT_VALUE

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

select * from(
select lot.PATIENT_ID, ms."Phenotype" as Phenotype, EVENT_DATE, EVENT_TYPE, EVENT_VALUE, START_REASON, LOT_NUM
from ats.NOVARTIS_KUWAIT_LOT_EVENT_START_REASON_LOT lot
join ats.NOVARTIS_KUWAIT_MS_RAW_PATIENTID_MS_PHENOTYPE as ms on lot.PATIENT_ID=ms."Patient_ID"
where Phenotype in ('SPMS', 'RRMS') ) as p
pivot(sum(EVENT_VALUE) for EVENT_VALUE in ('CIS','RR','SP','Mild','Moderate','Severe','Adverse Event','Allergic reaction','Lack of efficacy',
'Lack of tolerance','Non adherence','Patient Choice/Convenience','Persistence of relapses','Persisting MRI activity','Pregnancy confirmed',
'Pregnancy planning','Progession of disease','Progression of EDSS','Scheduled Stop','Aubagio','Avonex','Betaferon','Copaxone','Gilenya',
'Lemtrada','Mavenclad','Plegridy','Rebif','Rituxan','Ocrevus','Tecfidera','Tysabri')) as t

select distinct (EVENT_VALUE) from t1


select lot.PATIENT_ID, ms."Phenotype" as Phenotype, EVENT_DATE, EVENT_TYPE, EVENT_VALUE, START_REASON, LOT_NUM
from ats.NOVARTIS_KUWAIT_LOT_EVENT_START_REASON_LOT lot
join ats.NOVARTIS_KUWAIT_MS_RAW_PATIENTID_MS_PHENOTYPE as ms on lot.PATIENT_ID=ms."Patient_ID"
where Phenotype in ('SPMS', 'RRMS')


select lot.PATIENT_ID, ms."Phenotype" as Phenotype, EVENT_DATE, EVENT_TYPE, EVENT_VALUE, START_REASON, LOT_NUM
from ats.NOVARTIS_KUWAIT_LOT_EVENT_START_REASON_LOT lot
join ats.NOVARTIS_KUWAIT_MS_RAW_PATIENTID_MS_PHENOTYPE as ms on lot.PATIENT_ID=ms."Patient_ID"
where PATIENT_ID=2092

/***********************/
select * from ats.NOVARTIS_KUWAIT_LOT_RELAPSE_EVENT

select max(count_of_relpases) from(
select PATIENT_ID, EVENT_VALUE, count(EVENT_VALUE) count_of_relpases from ats.NOVARTIS_KUWAIT_LOT_RELAPSE_EVENT
group by PATIENT_ID, EVENT_VALUE)
-- 5

select * from(
select PATIENT_ID, EVENT_VALUE, count(EVENT_VALUE) count_of_relpases from ats.NOVARTIS_KUWAIT_LOT_RELAPSE_EVENT
group by PATIENT_ID, EVENT_VALUE)t
pivot (sum(count_of_relpases) for EVENT_VALUE in ('Mild', 'Moderate', 'Severe' )) as p