MAP_PatientType:
MAPPING LOAD  * INLINE [
	InValue,	OutValue
    ER,			ED
    INP,		Inpatient
    OBSER,		Observation
];

Encounters:
Load *,
// First check for COPD
// If the primary matches then it's possibly COPD
// And if the none of the other 14 are one of the values listed then it definitely is
// Have to use a Wildmatch becasue the All Diagnosis string contains all of them
IF ( Match([ICD9 Diagnoses 1] , '491.1', '491.20', '491.21', '491.22', '491.8', '491.9', '492.0', '492.8', '493.20', '493.21', '493.22', '494.0', '494.1', '496') > 0
And WildMatch([All Diagnosis], '*33.51*', '*33.52*', '*37.51*', '*37.52*', '*37.53*', '*37.54*', '*37.62*', '*37.63', '*33.50*', '*33.6*', '*50.51*', '*50.59*', '*52.80*', '*52.82*', '*55.69','196.0*', '*196.1*', '*196.2*', '*196.3*', '*196.5*', '*196.6*', '*196.8*', '*196.9*', '*197.0*', '*197.1*', '*197.2*', '*197.3*', '*197.4*', '*197.5*', '*197.6*', '*197.7*', '*197.8*', '*198.0*', '*198.1*', '*198.2*', '*198.3*', '*198.4*', '*198.5*', '*198.6*', '*198.7*', '*198.81*', '*198.82*', '*198.89*', '*203.02*', '*203.12*', '*203.82*', '*204.02*', '*204.12*', '*204.22*', '*204.82*', '*204.92*', '*205.02*', '*205.12*', '*205.22*', '*205.82*', '*205.92*', '*206.02*', '*206.12*', '*206.22*', '*206.82*', '*206.92*', '*207.02*', '*207.12*', '*207.22*', '*207.82*', '*208.02*', '*208.12*', '*208.22*', '*208.82*', '*208.92*', '*480.3*', '*480.8*', '*996.80*', '*996.81*', '*996.82*', '*996.83*', '*996.84*', '*996.85*', '*996.86*', '*996.87*', '*996.89*', '*V42.0*', '*V42.1*', '*V42.4*', '*V42.6*', '*V42.7*', '*V42.81*', '*V42.82*', '*V42.83*', '*V42.84*', '*V42.89*', '*V42.9*', '*V43.21*', '*V46.11*') = 0, 'COPD', 
// If we found COPD great, otherwise we need to check for Sepsis
IF (Match ([ICD9 Diagnoses 1] , '003.1', '027.0', '036.2', '036.3', '038.0', '038.10', '038.11', '038.12', '038.19', '038.2', '038.3', '038.40', '038.41', '038.42', '038.43', '038.44', '038.49', '038.8', '038.9', '785.52' ) > 0
And WildMatch([All Diagnosis], '*33.50*', '*33.51*', '*33.52*', '*33.6*', '*37.51*', '*37.52*', '*37.53*', '*37.54*', '*37.62*', '*37.63*', '*50.51*', '*50.59*', '*50.69*', '*52.80*', '*52.82*', '*52.83*', '*55.69*', '*196.0*', '*196.1*', '*196.2*', '*196.3*', '*196.5*', '*196.6*', '*196.8*', '*196.9*', '*197.0*', '*197.1*', '*197.2*', '*197.3*', '*197.4*', '*197.5*', '*197.6*', '*197.7*', '*197.8*', '*198.0*', '*198.1*', '*198.2*', '*198.3*', '*198.4*', '*198.5*', '*198.6*', '*198.7*', '*198.81*', '*198.82*', '*198.89*', '*203.00*', '*203.02*', '*203.12*', '*203.82*', '*204.02*', '*204.10*', '*204.12*', '*204.22*', '*204.82*', '*204.92*', '*205.02*', '*205.12*', '*205.22*', '*205.82*', '*205.92*', '*206.02*', '*206.12*', '*206.22*', '*206.82*', '*206.92*', '*207.02*', '*207.12*', '*207.22*', '*207.82*', '*208.02*', '*208.12*', '*208.22*', '*208.82*', '*208.92*', '*996.80*', '*996.81*', '*996.82*', '*996.83*', '*996.84*', '*996.85*', '*996.86*', '*996.87*', '*996.89*', '*V42.0*', '*V42.1*', '*V42.4*', '*V42.6*', '*V42.7*', '*V42.81*', '*V42.82*', '*V42.83*', '*V42.84*', '*V42.89*', '*V42.9*', '*V43.21*', '*205.32*') = 0, 'Sepsis',
'Nothing')) as [Core Diagnosis]
;

LOAD
    fMRN as MRN,
    1 as EcnounterCtr,
    IF (Patient_Type = 'INP', 1, 0) as EncounterInpatientCtr,
    IF (Patient_Type = 'ER', 1, 0) as EncounterEDCtr,
    fEncounter as EncounterID,
    fAdmitDate as [Admit Dt/Tm],
    date(date#(fAdmitDate,'M/D/YYYY'),'MM-DD-YYYY') as [Admit Date],        
    fDischrgDate as [Discharge Dt/Tm],
    date(date#(fDischrgDate,'M/D/YYYY'),'MM-DD-YYYY') as [Discharge Date],
    Sex,
    Discharge_Responsible_Physicia as [Discharge Responsible Physician],   
    Discharge_Disposition as [Discharge Disposition],    
	Attending_Physicians as [Attending Physicians],
    ER_Phys as [ER Physician],
    Primary_Care_Physician as [Primary Care Physician],
    Family_Physician as [Family Physician],
    zip3 as Zip,
    agecat as [Age Category],
    APPLYMAP('MAP_PatientType',Patient_Type,'No Patient Type') as [Patient Type],
    ICD9_Diagnoses_1 as [ICD9 Diagnoses 1],
    ICD9_Diagnoses_2 as [ICD9 Diagnoses 2],
	ICD9_Diagnoses_3 as [ICD9 Diagnoses 3], 
    ICD9_Diagnoses_4 as [ICD9 Diagnoses 4],
    ICD9_Diagnoses_5 as [ICD9 Diagnoses 5],
    ICD9_Diagnoses_6 as [ICD9 Diagnoses 6],
    ICD9_Diagnoses_7 as [ICD9 Diagnoses 7],
    ICD9_Diagnoses_8 as [ICD9 Diagnoses 8],
    ICD9_Diagnoses_9 as [ICD9 Diagnoses 9],
    ICD9_Diagnoses_10 as [ICD9 Diagnoses 10],
    ICD9_Diagnoses_11 as [ICD9 Diagnoses 11],
    ICD9_Diagnoses_12 as [ICD9 Diagnoses 12],
    ICD9_Diagnoses_13 as [ICD9 Diagnoses 13],
    ICD9_Diagnoses_14 as [ICD9 Diagnoses 14],
    ICD9_Diagnoses_15 as [ICD9 Diagnoses 15],
    
    ICD9_Diagnoses_1 & ', ' &
    ICD9_Diagnoses_2 & ', ' &
    ICD9_Diagnoses_3 & ', ' &
    ICD9_Diagnoses_4 & ', ' &
    ICD9_Diagnoses_5 & ', ' &
    ICD9_Diagnoses_6 & ', ' &
    ICD9_Diagnoses_7 & ', ' &
    ICD9_Diagnoses_8 & ', ' &
    ICD9_Diagnoses_9 & ', ' &
    ICD9_Diagnoses_10 & ', ' &
    ICD9_Diagnoses_11 &', ' &
    ICD9_Diagnoses_12 & ', ' &
    ICD9_Diagnoses_13 & ', ' &
    ICD9_Diagnoses_14 & ', ' &
    ICD9_Diagnoses_15 as [All Diagnosis]
    
FROM [Discharge File.csv]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

// We need to find out if this is a repeat visit 
left join (Encounters)
LOAD
	EncounterID,
	IF(MRN = Previous(MRN),Dual('Yes', 1),Dual('No',0)) as [Inpatient IsReadmission Flag],
    IF(MRN = Previous(MRN),Previous([Discharge Dt/Tm])) as [Inpatient Previous Discharge Date],
    IF(MRN = Previous(MRN),Previous(EncounterID)) as [Inpatient Previous EncounterID],
    IF(MRN = Previous(MRN),NUM(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])),'#,##0.00')) as [Inpatient Readmission Difference],
    IF(MRN = Previous(MRN),IF(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])) <= 30.0, Dual('Yes', 1), Dual('No',0)), Dual('No',0)) as [Inpatient IsReadmission within 30]
resident Encounters
where [Patient Type] = 'Inpatient'
order by MRN, [Admit Dt/Tm];

// We need to find out if this is a repeat visit 
left join (Encounters)
LOAD
	EncounterID,
	IF(MRN = Previous(MRN),Dual('Yes', 1),Dual('No',0)) as [ED IsReadmission Flag],
    IF(MRN = Previous(MRN),Previous([Discharge Dt/Tm])) as [ED Previous Discharge Date],
    IF(MRN = Previous(MRN),Previous(EncounterID)) as [ED Previous EncounterID],
    IF(MRN = Previous(MRN),NUM(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])),'#,##0.00')) as [ED Readmission Difference],
    IF(MRN = Previous(MRN),IF(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])) <= 7.0,'Yes', 'No'), 'No') as [ED IsReadmission within 7],  
    IF(MRN = Previous(MRN),IF(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])) <= 30.0,'Yes', 'No'), 'No') as [ED IsReadmission within 30],
    IF(MRN = Previous(MRN),IF(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])) <= 60.0,'Yes', 'No'), 'No') as [ED IsReadmission within 60]
resident Encounters
where [Patient Type] = 'ED'
order by MRN, [Admit Dt/Tm];

// We need to find out if this is a repeat visit for COPD 
Left Join (Encounters)
LOAD
	EncounterID,
	IF(MRN = Previous(MRN),Dual('Yes', 1),Dual('No',0)) as [COPD IsReadmission Flag],
    IF(MRN = Previous(MRN),Previous([Discharge Dt/Tm])) as [COPD Previous Discharge Date],
    IF(MRN = Previous(MRN),Previous(EncounterID)) as [COPD Previous EncounterID],
    IF(MRN = Previous(MRN),NUM(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])),'#,##0.00')) as [COPD Readmission Difference],
    IF(MRN = Previous(MRN),IF(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])) <= 30.0, Dual('Yes', 1), Dual('No',0)), Dual('No',0)) as [COPD IsReadmission within 30],
    IF(MRN = Previous(MRN),IF(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])) <= 90.0,'Yes', 'No'), 'No') as [COPD IsReadmission within 90]
resident Encounters
where [Patient Type] = 'Inpatient' and  [Core Diagnosis] = 'COPD'
order by MRN, [Admit Dt/Tm];

// We need to find out if this is a repeat visit for Sepsis 
Left Join (Encounters)
LOAD
	EncounterID,
	IF(MRN = Previous(MRN),Dual('Yes', 1),Dual('No',0)) as [Sepsis IsReadmission Flag],
    IF(MRN = Previous(MRN),Previous([Discharge Dt/Tm])) as [Sepsis Previous Discharge Date],
    IF(MRN = Previous(MRN),Previous(EncounterID)) as [Sepsis Previous EncounterID],
    IF(MRN = Previous(MRN),NUM(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])),'#,##0.00')) as [Sepsis Readmission Difference],
    IF(MRN = Previous(MRN),IF(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])) <= 30.0,Dual('Yes', 1), Dual('No',0)), Dual('No',0)) as [Sepsis IsReadmission within 30],
    IF(MRN = Previous(MRN),IF(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])) <= 90.0,'Yes', 'No'), 'No') as [Sepsis IsReadmission within 90],
    IF(MRN = Previous(MRN),IF(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])) <= 120.0,'Yes', 'No'), 'No') as [Sepsis IsReadmission within 120],
    IF(MRN = Previous(MRN),IF(Interval([Admit Dt/Tm]-Previous([Discharge Dt/Tm])) > 120.0,'Yes', 'No'), 'No') as [Sepsis IsReadmission > 120]
Resident Encounters
Where [Patient Type] = 'Inpatient' and  [Core Diagnosis] = 'Sepsis'
Order By MRN, [Admit Dt/Tm];