Cdn.mdedge.com



Appendix. ICD-9, Procedure, and Lab Codes

README: The SQL code in this Appendix represents the queries used in MS SQL Server to access the UCSF Clarity Database and calculate the Modified Automated Padua Prediction Score (MAPPS). Each represents the code to calculate one criterion of MAPPS and returns a unique patient ID and the score value, with exception of the files "PAT_ENC_to_PAT_ID", "SCD_On_Off_Demographics", and "Total_Demographics". These latter three were queries written to acquire additional demographic data and are included for convenience in recreating the studied cohort.

In the interest of readability, the code is in text. Readers who are interested in receiving the SQL Code Files and additional discussion should contact the Corresponding Author.

SQL CODE FOR “ACTIVE CANCER”

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

SELECT PAT_ENC.PAT_ENC_CSN_ID, max(CancerScore) as [CancerScore] from

PAT_ENC left outer join

(

SELECT PAT_ENC.PAT_ENC_CSN_ID, PAT_ENC.PAT_ID,

case when cancer.PAT_ID is not null then 3 else 0 end [CancerScore]

FROM PAT_ENC

LEFT OUTER JOIN (SELECT DISTINCT(PAT_ID)

FROM HSP_TRANSACTIONS

inner join PAT_ENC on HSP_TRANSACTIONS.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

WHERE

DATEDIFF(month,HSP_TRANSACTIONS.SERVICE_DATE, PAT_ENC.CONTACT_DATE) between 0 and 6 --Need to make between, ensure no negative numbers

AND

HSP_TRANSACTIONS.PAT_ENC_CSN_ID IN

(SELECT PAT_ENC_CSN_ID

FROM HSP_TRANSACTIONS

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE CPT_CODE IN ('96401', '96402' , '96405', '96406', '96409', '96411'

, '96413', '96415', '96416', '96417', '96420', '96422', '96423', '96425'

, '96440', '93521', '96522', '96523', '96542', '96549', '0182T'

, '0190T', '0197T', '19296', '19297', '19298', '49411', '57155', '57156'

, '58346', '76950', '76965', '77371', '77372', '77373', '77401', '77402'

, '77403', '77404', '77405', '77406', '77407', '77408', '77409', '77410'

, '77411', '77412', '77413', '77414', '77415', '77416', '77418', '77422'

, '77423', '77427', '77431', '77432', '77435', '77470', '77499', '77520'

, '77522', '77523', '77525', '77600', '77605', '77610', '77615', '77620'

, '77750', '77761', '77762', '77763', '77776', '77777', '77778', '77785'

, '77786', '77787', '77789', '77799')

)GROUP BY PAT_ID

) cancer

on cancer.PAT_ID=PAT_ENC.PAT_ID

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

UNION

SELECT PAT_ENC.PAT_ENC_CSN_ID, PAT_ENC.PAT_ID,

case when cancer.PAT_ID is not null then 3 else 0 end [CancerScore]

FROM PAT_ENC

LEFT OUTER JOIN

(SELECT DISTINCT(V_DIAGNOSIS_INFO.PAT_ID)

FROM

PAT_ENC

inner join V_DIAGNOSIS_INFO

on V_DIAGNOSIS_INFO.PAT_ID=PAT_ENC.PAT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = V_DIAGNOSIS_INFO.DX_ID

WHERE

datediff(day, FIRST_DATE, HOSP_ADMSN_TIME) >= 1 --ensures that diagnosis occurred prior to admission in question

AND ICD9_CODE LIKE '196%'

AND (ICD9_CODE LIKE '197%'

or ICD9_CODE LIKE '198%'

or ICD9_CODE = '199.0')

GROUP BY V_DIAGNOSIS_INFO.PAT_ID

) cancer

on cancer.PAT_ID=PAT_ENC.PAT_ID

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

) as [TMP_TABLE] on PAT_ENC.PAT_ENC_CSN_ID = TMP_TABLE.PAT_ENC_CSN_ID

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G)

GROUP BY PAT_ENC.PAT_ENC_CSN_ID

ORDER BY PAT_ENC.PAT_ENC_CSN_ID

SQL CODE FOR “INFECTION AND RHEUMATOLOGIC DISORDER”

Using Pitchstone Health ICD 9 look up for infection and rheumatologic disorder, linking to that encounter's claim dx

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

Select distinct(PAT_ENC_CSN_ID),

CASE when PAT_ENC_CSN_ID in

(SELECT DISTINCT(PAT_ENC.PAT_ENC_CSN_ID)

FROM PAT_ENC_DX

left outer join HSP_ADMIT_DIAG on PAT_ENC_DX.PAT_ENC_CSN_ID = HSP_ADMIT_DIAG.PAT_ENC_CSN_ID

left outer join PAT_ENC on PAT_ENC_DX.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

left outer join PATIENT on PAT_ENC_DX.PAT_ID = PATIENT.PAT_ID

left outer join CLARITY_EDG on PAT_ENC_DX.DX_ID = CLARITY_EDG.DX_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND

(

--Acute Infections

CLARITY_EDG.ICD9_CODE between '001' and '139.9'

OR CLARITY_EDG.ICD9_CODE IN ('288.04','323.41','519.01','528.3','536.41','539.81','569.61','596.81','598.00','686.9','958.3')

OR CLARITY_EDG.ICD9_CODE like '326%'

OR CLARITY_EDG.ICD9_CODE like '379.6%'

OR CLARITY_EDG.ICD9_CODE like '380.1%'

OR CLARITY_EDG.ICD9_CODE like '465%'

OR CLARITY_EDG.ICD9_CODE like '567%'

OR CLARITY_EDG.ICD9_CODE like '590%'

OR CLARITY_EDG.ICD9_CODE like '596.81%'

OR CLARITY_EDG.ICD9_CODE like '599%'

OR CLARITY_EDG.ICD9_CODE like '639%'

OR CLARITY_EDG.ICD9_CODE like '634.0%'

OR CLARITY_EDG.ICD9_CODE like '635.0%'

OR CLARITY_EDG.ICD9_CODE like '639.0%'

OR CLARITY_EDG.ICD9_CODE like '646.6%'

OR CLARITY_EDG.ICD9_CODE like '647.9%'

OR CLARITY_EDG.ICD9_CODE like '670.0%'

OR CLARITY_EDG.ICD9_CODE like '659.3%'

OR CLARITY_EDG.ICD9_CODE like '711%'

OR CLARITY_EDG.ICD9_CODE like '730%'

OR CLARITY_EDG.ICD9_CODE like '999.3%'

OR CLARITY_EDG.ICD9_CODE like '998.5%'

OR CLARITY_EDG.ICD9_CODE like '996.6%'

OR ADMIT_DIAG_TEXT like '%infect%'

OR ADMIT_DIAG_TEXT like '%sepsis%'

OR ADMIT_DIAG_TEXT like '%cellulitis%'

--Acute Rheum

OR CLARITY_EDG.ICD9_CODE like '357%'

OR CLARITY_EDG.ICD9_CODE like '359.6%'

OR CLARITY_EDG.ICD9_CODE between '710' and '719.99'

OR ADMIT_DIAG_TEXT like '%rheumat%'

OR ADMIT_DIAG_TEXT like '%lupus%'

)

)

then 1 else 0 end [AcuteInfxnOrRheum]

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G)

Order by PAT_ENC_CSN_ID

SQL CODE FOR “STROKE” “ACUTE MI”

Using Pitchstone Health ICD 9 look up for ICD 9 codes, linking to that encounter's claim dx

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

Select distinct(PAT_ENC_CSN_ID),

CASE when PAT_ENC_CSN_ID in

(SELECT DISTINCT(PAT_ENC.PAT_ENC_CSN_ID)

FROM PAT_ENC_DX

left outer join HSP_ADMIT_DIAG on PAT_ENC_DX.PAT_ENC_CSN_ID = HSP_ADMIT_DIAG.PAT_ENC_CSN_ID

left outer join PAT_ENC on PAT_ENC_DX.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

left outer join PATIENT on PAT_ENC_DX.PAT_ID = PATIENT.PAT_ID

left outer join CLARITY_EDG on PAT_ENC_DX.DX_ID = CLARITY_EDG.DX_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND

(CLARITY_EDG.ICD9_CODE between '430' and '436.9' --Ischemic stroke (NOTE that this is more than just ischemic stroke, to make just IS would just take 433-434.9)

OR ADMIT_DIAG_TEXT like '%stroke%'

OR ADMIT_DIAG_TEXT like '%cerebral infarction%'

OR CLARITY_EDG.ICD9_CODE between '410' and '410.9' --Myocardial Infarction

OR ADMIT_DIAG_TEXT like '%myocardial infarction%'

OR ADMIT_DIAG_TEXT like '%heart attack%')

)

then 1

when PAT_ENC_CSN_ID in

(SELECT ORDER_RESULTS.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join ORDER_RESULTS ORDER_RESULTS on ORDER_RESULTS.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join ORDER_PROC OP on OP.ORDER_PROC_ID = ORDER_RESULTS.ORDER_PROC_ID

inner join ORDER_PROC_2 op2 on op2.ORDER_PROC_ID =

ORDER_RESULTS.ORDER_PROC_ID

inner join CLARITY_COMPONENT comp on PONENT_ID =

ORDER_PONENT_ID

WHERE PONENT_ID in ('2477','1511129', '1511130', '1534327', '1552153', '1808154', '1910141')

AND ORD_VALUE > '0.049'

AND DATEDIFF(hour, HOSP_ADMSN_TIME, ORDER_RESULTS.RESULT_TIME) between 0 and 4

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01')

then 1 --Abnormal troponin within 4 hrs of admission

else 0 end [Acute_MI_Stroke]

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G)

Order by PAT_ENC_CSN_ID

SQL FILE FOR “BED REST”

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

SELECT distinct(PAT_ENC.PAT_ENC_CSN_ID),

case when Immobile.PAT_ENC_CSN_ID is not null then 3 else 0 end [ReducedMobility]

FROM PAT_ENC

LEFT OUTER JOIN (SELECT distinct(PAT_ENC_CSN_ID)

FROM PAT_ENC

join IP_DATA_STORE

on IP_DATA_STORE.EPT_CSN = PAT_ENC.PAT_ENC_CSN_ID

join IP_FLWSHT_REC

on IP_FLWSHT_REC.INPATIENT_DATA_ID = IP_DATA_STORE.INPATIENT_DATA_ID

join IP_FLWSHT_MEAS

on IP_FLWSHT_MEAS.FSD_ID = IP_FLWSHT_REC.FSD_ID

WHERE FLO_MEAS_ID = '305560' and

(MEAS_VALUE LIKE '%bedrest%' and MEAS_VALUE NOT LIKE '%up ad lib%' and MEAS_VALUE NOT LIKE '%ambulate in hall%'

and MEAS_VALUE NOT LIKE '%ambulate in room%')

GROUP BY PAT_ENC_CSN_ID)

Immobile on

Immobile.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G))

Order by PAT_ENC_CSN_ID

SQL CODE FOR “COAGULOPATHY”

--Important Note: RESULT_IN_RANGE_YN has null value for both No and N/A)

--Protein C Labs = PROC_ID 7672 and 7676 (Can Use RESULT_IN_RANGE_YN)

--Protein S Labs = PROC_ID 7680 and 7684] (Can Use RESULT_IN_RANGE_YN)

--Antithrombin = 1732 and 92462 (Can Use RESULT_IN_RANGE_YN)

--Factor V Leiden = 1802 (Need to look at text associated with lab..., look at order 462869 ORDER_PROC_ID)

--Antiphospholipid AB Panel = 98315 with 12 components, we care about 7 and 8, possible 9-12

--Negative for COMPONENT_ID 807: ORD_VALUE = 'NEG', 'No Lupus Anticoagulant present.', 'Normal', 'The results are not diagnostic of lupus anticoagulant.'

--Positive for COMPONENT_ID 807: ORD_VALUE = 'POS', 'POSITIVE', 'Positive, Lupus Anticoagulant present.'

--Negative for COMPONENT_ID 2445: ORD_VALUE = 'NEG'

--Positive for COMPONENT_ID 2445: ORD_VALUE = 'POS', 'POSITIVE', 'POSITIVE, SEE COMMENT'

--Homocysteine = 834 (Can Use RESULT_IN_RANGE_YN)

--Prothrombin Mutation = 64734 (Need to look at text associated with lab..., look at order 462851 ORDER_PROC_ID)

--Unresolved include RVVT (OPI 462863), Methyl Reductase Mutations (OP 462873), Anticardiolipin IgG (462857), Anticardiolipin IgM (462861)

--Hypercoagulability in ICD9 Codes = 289.81 and 289.92

--Still missing lab results F V Leiden, Prothrombin, 9-12 of Antiphospholipid panel, RVVT, Methyl Reductase Mutation, Anticardiolipin

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

SELECT PAT_ENC.PAT_ENC_CSN_ID, max(CoagScore) as [CoagScore] from

PAT_ENC left outer join

(

SELECT PAT_ENC.PAT_ENC_CSN_ID, PAT_ENC.PAT_ID,

case when coag.PAT_ID is not null then 3 else 0 end [CoagScore]

FROM PAT_ENC

LEFT OUTER JOIN (SELECT DISTINCT(p.PAT_ID)

FROM PATIENT p

inner join ORDER_RESULTS orslt on orslt.PAT_ID = p.PAT_ID

inner join ORDER_PROC OP on OP.ORDER_PROC_ID = orslt.ORDER_PROC_ID

inner join ORDER_PROC_2 op2 on op2.ORDER_PROC_ID =

orslt.ORDER_PROC_ID

inner join CLARITY_COMPONENT comp on PONENT_ID =

PONENT_ID

WHERE

(PROC_ID in ('7672', '7676') and RESULT_IN_RANGE_YN IS NULL)

OR

(PROC_ID in ('7680', '7684') and RESULT_IN_RANGE_YN IS NULL)

OR

(PROC_ID in ('1732', '92462') and RESULT_IN_RANGE_YN IS NULL)

OR

(PROC_ID = '98315' and PONENT_ID = '807' and ORD_VALUE in ('POS', 'POSITIVE', 'Positive, Lupus Anticoagulant present.'))

OR

(PROC_ID = '98315' and PONENT_ID = '2445' and ORD_VALUE in ('POS', 'POSITIVE', 'POSITIVE, SEE COMMENT'))

OR

(PROC_ID = '834' and RESULT_IN_RANGE_YN IS NULL)

GROUP BY p.PAT_ID

) coag

on coag.PAT_ID=PAT_ENC.PAT_ID

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

UNION

SELECT PAT_ENC.PAT_ENC_CSN_ID, PAT_ENC.PAT_ID,

case when coag.PAT_ID is not null then 3 else 0 end [CoagScore]

FROM PAT_ENC

LEFT OUTER JOIN

(SELECT DISTINCT(V_DIAGNOSIS_INFO.PAT_ID)

FROM

PAT_ENC

inner join V_DIAGNOSIS_INFO

on V_DIAGNOSIS_INFO.PAT_ID=PAT_ENC.PAT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = V_DIAGNOSIS_INFO.DX_ID

WHERE

datediff(day, FIRST_DATE, HOSP_ADMSN_TIME) >= 0 --ensures that diagnosis occurred prior to admission in question

AND (ICD9_CODE = '289.81'

or ICD9_CODE = '289.82')

GROUP BY V_DIAGNOSIS_INFO.PAT_ID

) coag

on coag.PAT_ID=PAT_ENC.PAT_ID

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

) as COAG_TABLE on PAT_ENC.PAT_ENC_CSN_ID = COAG_TABLE.PAT_ENC_CSN_ID

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

WHERE

PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G)

GROUP BY PAT_ENC.PAT_ENC_CSN_ID

ORDER BY PAT_ENC_CSN_ID

SQL CODE FOR “ELDERLY AGE”

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

SELECT distinct(PAT_ENC_CSN_ID), CASE when DATEDIFF(year, BIRTH_DATE, CONTACT_DATE) >= 70 then 1 else 0 end [ElderlyAge]

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G))

Order by PAT_ENC_CSN_ID

SQL CODE FOR “ACUTE MI” “ISCHEMIC STROKE”

Indicates if Acute MI or Ischemic Stroke was on ICD9 List for THAT claim, meaning only looking at that one encounter

To change it to looking to entire patient, change Case When to PAT_ID rather than PAT_ENC_CSN_ID

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

Select distinct(PAT_ENC_CSN_ID),

CASE when

PAT_ENC.PAT_ID in (SELECT PAT_ENC.PAT_ID FROM

PAT_ENC

inner join V_DIAGNOSIS_INFO

on V_DIAGNOSIS_INFO.PAT_ID=PAT_ENC.PAT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = V_DIAGNOSIS_INFO.DX_ID

WHERE

datediff(day, FIRST_DATE, HOSP_ADMSN_TIME) >= 0 --ensures that diagnosis occurred prior to admission in question

AND

(ICD9_CODE like '428%' --Heart Failure

OR

ICD9_CODE between '518.81' and '518.85'

OR

ICD9_CODE = '518.51'

OR

ICD9_CODE = '518.53'

OR

ICD9_CODE = '799.1')

) --Respiratory Failure

then 1

when PAT_ENC_CSN_ID in

(SELECT ORDER_PROC.PAT_ENC_CSN_ID

FROM ORDER_PROC

inner join PAT_ENC on ORDER_PROC.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID --Vent order within 4 hrs of admission

WHERE PROC_CODE in ('RT108', 'RT109', 'RT111', 'RT112', 'RT114', 'RT25', 'RT5')

AND DATEDIFF(hour, HOSP_ADMSN_TIME, ORDER_PROC.ORDER_INST) between 0 and 4

AND ORDER_STATUS_C not like '4'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01')

then 1 else 0 end [HeartOrRespiratoryFailure]

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

PAT_ENC_CSN_ID in (SELECT * FROM #G)

Order by PAT_ENC_CSN_ID

SQL CODE FOR “HORMONAL TREATMENT”

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

Select distinct(PAT_ENC.PAT_ENC_CSN_ID),

CASE when PAT_MED_RESUME.PAT_ENC_CSN_ID in

(SELECT PAT_MED_RESUME.PAT_ENC_CSN_ID FROM PAT_MED_RESUME

inner join ORDER_MED on PAT_MED_RESUME.MED_RESUME_ORD_ID = ORDER_MED.ORDER_MED_ID

WHERE MEDICATION_ID IN

('14949','14950','25172','36324','36325','37561','125965','125966', --CLIMARA

'23882','24394','126294', --COMBIPATCH

'127195','2200', --DELESTROGEN

'127284',--DEPO-MEDROL

'2251','127287','2250',--DEPO-TESTOSTER

'2928','128938',--ESTRADERM

--ESTROGEN

'25012'

,'26950'

,'126477'

,'141334'

,'9966'

,'9974'

,'9975'

,'14597'

,'27273'

,'33425'

,'16032'

,'36198'

,'128969'

,'14196'

,'14197'

,'35957'

,'70774'

,'141333'

,'2200'

,'9960'

,'9972'

,'25011'

,'38442'

,'70776'

,'126475'

,'9959'

,'70777'

,'102251'

,'9964'

,'70775'

,'127195'

,'2938'

,'9963'

,'9973'

,'9976'

,'37459'

,'126476'

,'128935'

,'203346'

--ESTROPIPATE

,'12400'

,'12401'

,'12402'

,'128975'

--EVISTA

,'22190'

,'129080'

--EXEMESTANE

,'26551'

,'129138'

--LEVONORGESTREL

,'37533'

,'82084'

,'10401'

,'36857'

,'132876'

,'20638'

,'99445'

,'132874'

,'26755'

,'132875'

,'206636'

,'29280'

--LOESTRIN

,'133188'

,'133192'

,'70610'

,'133189'

,'146866'

,'145808'

,'31394'

,'133190'

,'14875'

,'133191'

--ORTHO-CYCLEN

,'25030'

,'136463'

--ORTHO EVRA

,'32485'

,'136458'

--ORTHO-CEPT

,'25061'

,'136462'

--ORTHO-NOVUM

,'136469'

,'136467'

,'25033'

,'136470'

--PREMARIN

,'6506'

,'6509'

,'138168'

,'6508'

,'36189'

,'6507'

--PREMPRO

,'14467'

,'35965'

,'37453'

,'138202'

--PROGESTERONE

,'4854'

,'4856'

,'40473'

,'104403'

,'106212'

,'40474'

,'77061'

,'106273'

,'131329'

,'138518'

,'6599'

,'26550'

,'37459'

,'138515'

,'138517'

,'138520'

,'4855'

,'23122'

,'42987'

,'82391'

,'16032'

,'19736'

,'77924'

,'102064'

,'138516'

,'6597'

,'14196'

,'14197'

,'35957'

,'41141'

,'42939'

,'77007'

,'102572'

,'133798')

)

then 1 else 0 end [HormonalTherapy]

FROM PAT_ENC

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join PAT_MED_RESUME on PAT_MED_RESUME.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

left outer join ORDER_MED on PAT_MED_RESUME.MED_RESUME_ORD_ID = ORDER_MED.ORDER_MED_ID

left outer join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID

left outer join ZC_MED_RESUME_STAT on ZC_MED_RESUME_STAT.RESUME_STATUS_C = PAT_MED_RESUME.MED_RESUME_STAT_C

WHERE

PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G)

Order by PAT_ENC_CSN_ID

SQL CODE FOR “MEDICAL PROPHYLAXIS RECEIVED”

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

SELECT Distinct(PAT_ENC.PAT_ENC_CSN_ID),

CASE when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join ORDER_MED on ORDER_MED.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID WHERE SIMPLE_GENERIC_C = '4155') then 'Dalteparin'

when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join ORDER_MED on ORDER_MED.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID WHERE SIMPLE_GENERIC_C in ('6023', '8885', '9045')) then 'Argatroban'

when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join ORDER_MED on ORDER_MED.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID WHERE SIMPLE_GENERIC_C = '6316') then 'Fondaparinux'

when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join ORDER_MED on ORDER_MED.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID WHERE SIMPLE_GENERIC_C = '8505') then 'Dabigatran'

when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join ORDER_MED on ORDER_MED.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID WHERE SIMPLE_GENERIC_C = '8953') then 'Rivaroxaban'

when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join ORDER_MED on ORDER_MED.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID WHERE SIMPLE_GENERIC_C = '9735') then 'Apixaban'

when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join ORDER_MED on ORDER_MED.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID WHERE SIMPLE_GENERIC_C = '2270') then 'Warfarin'

when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join ORDER_PROC on PAT_ENC.PAT_ENC_CSN_ID = ORDER_PROC.PAT_ENC_CSN_ID

WHERE PROC_CODE in ('NUR536', 'NUR563', 'EQ188', 'EQ54')) then 'SCD' --NUR536 Place SCD, NUR563 Maintain SCD, EQ188 SCD (DME), EQ54 SCD (DME)

when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join ORDER_MED on ORDER_MED.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID

WHERE CLARITY_MEDICATION.MEDICATION_ID = '110556') then 'Heparin' --Only HEPARIN, PORCINE (PF) 5,000 UNIT/0.5 ML INJECTION SYRINGE

when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join ORDER_MED on ORDER_MED.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID WHERE SIMPLE_GENERIC_C = '4215') then 'Enoxaparin'

when PAT_ENC.PAT_ENC_CSN_ID in (

SELECT PAT_ENC.PAT_ENC_CSN_ID

FROM PAT_ENC

inner join ORDER_PROC on PAT_ENC.PAT_ENC_CSN_ID = ORDER_PROC.PAT_ENC_CSN_ID

inner join HSP_ACCT_DX_LIST on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join ORDER_MED on ORDER_MED.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

inner join CLARITY_MEDICATION on ORDER_MED.MEDICATION_ID = CLARITY_MEDICATION.MEDICATION_ID

WHERE PROC_CODE in ('NUR536', 'NUR563', 'EQ188', 'EQ54')

AND (SIMPLE_GENERIC_C in ('2270', '8953', '4155', '4255', '6023', '8885', '9045', '6316', '8505', '8953', '9735', '2270')

OR CLARITY_MEDICATION.MEDICATION_ID = '110556')

) then 'SCD and Pharm'

else 'No Ppx' end [Med Ppx Received]

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G)

Order by PAT_ENC_CSN_ID

SQL CODE FOR “OBESITY”

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

Select PAT_ENC_CSN_ID,

CASE when

PAT_ENC.PAT_ID in (SELECT PAT_ENC.PAT_ID FROM

PAT_ENC

inner join V_DIAGNOSIS_INFO

on V_DIAGNOSIS_INFO.PAT_ID=PAT_ENC.PAT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = V_DIAGNOSIS_INFO.DX_ID

WHERE

ICD9_CODE like '278%' --Doing what Kucher et al did, look for BMI first then search for obesity ICD9 code

OR PAT_ENC.BMI >= 30)

then 1 else 0 end [Obesity]

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

PAT_ENC_CSN_ID in (SELECT * FROM #G)

GROUP BY PAT_ENC_CSN_ID, PAT_ENC.PAT_ID

ORDER BY PAT_ENC_CSN_ID

SQL CODE FOR “PAT ENC TO PAT ID”

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

SELECT PATIENT.PAT_ID, PAT_ENC_CSN_ID

FROM PATIENT inner join PAT_ENC on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE PAT_ENC_CSN_ID in (Select * from #G)

SQL CODE FOR “PRIOR VTE”

--INCLUSION/EXCLUSION:

--1. 18 yo, not pregnant, not hospice, LOS >2 days (all of which you have)

--2. VTE as principal diagnosis, OR VTE as secondary dx with POA=Y,

--OUTCOMES ASSESSMENT:

--in-hospital VTE (the thing we are trying to predict) is:

--VTE in positions diagnosis positions 2 and up, AND VTE POA=N or U

--VTE in any position in a subsequent hospitalization if no VTE during this admission

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_3. ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9')

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

Select distinct(PAT_ENC_CSN_ID),

CASE when

PAT_ENC.PAT_ID in (SELECT PAT_ENC.PAT_ID FROM

PAT_ENC

inner join V_DIAGNOSIS_INFO

on V_DIAGNOSIS_INFO.PAT_ID=PAT_ENC.PAT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = V_DIAGNOSIS_INFO.DX_ID

WHERE

datediff(day, FIRST_DATE, HOSP_ADMSN_TIME) >= 2 --ensures that diagnosis occurred prior to admission in question

AND

ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',

'671.31','671.33','671.44','673.2', '673.20', '673.21', 'V12.51', 'V12.55' )) --Codes for Prior VTE courtesy of Raman Khanna

then 3 else 0 end [Prior_VTE]

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G)

Order by PAT_ENC_CSN_ID

SQL CODE FOR “SCD ON OFF DEMOGRAPHICS”

--Use below comment to figure out all the different measured values for a specific encounter

--SELECT PAT_ENC_CSN_ID, MEAS_VALUE, COUNT(*)

--FROM IP_FLWSHT_MEAS

--join IP_FLWSHT_REC

--on IP_FLWSHT_MEAS.FSD_ID = IP_FLWSHT_REC.FSD_ID

--join IP_DATA_STORE

--on IP_FLWSHT_REC.INPATIENT_DATA_ID = IP_DATA_STORE.INPATIENT_DATA_ID

--join PAT_ENC

--on IP_DATA_STORE.EPT_CSN = PAT_ENC.PAT_ENC_CSN_ID

--WHERE FLO_MEAS_ID = '7060420' AND PAT_ENC_CSN_ID = 'Insert a PAT_ENC_CSN_ID here'

--GROUP BY MEAS_VALUE, PAT_ENC_CSN_ID

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

--Get only the daily cares/safety flowsheet for afilliated PAT_ENC_CSN_ID in #G

SELECT PAT_ENC_CSN_ID into #SCD_TEMP

FROM IP_FLWSHT_MEAS

left outer join IP_FLWSHT_REC on IP_FLWSHT_MEAS.FSD_ID = IP_FLWSHT_REC.FSD_ID

left outer join IP_DATA_STORE on IP_FLWSHT_REC.INPATIENT_DATA_ID = IP_DATA_STORE.INPATIENT_DATA_ID

left outer join PAT_ENC on IP_DATA_STORE.EPT_CSN = PAT_ENC.PAT_ENC_CSN_ID

left outer join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

WHERE FLO_MEAS_ID = '7060420' AND PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G)

GROUP BY PAT_ENC_CSN_ID

SELECT PAT_ENC_CSN_ID, COUNT(CASE when MEAS_VALUE like '%on%' and MEAS_VALUE not like '%off%' and MEAS_VALUE not like '%refused%' then 1 end) as [SCD_ON],

COUNT(CASE when MEAS_VALUE like '%off%' or MEAS_VALUE like '%refused%' then 2 end) as [SCD_OFF]

FROM IP_FLWSHT_MEAS

left outer join IP_FLWSHT_REC on IP_FLWSHT_MEAS.FSD_ID = IP_FLWSHT_REC.FSD_ID

left outer join IP_DATA_STORE on IP_FLWSHT_REC.INPATIENT_DATA_ID = IP_DATA_STORE.INPATIENT_DATA_ID

left outer join PAT_ENC on IP_DATA_STORE.EPT_CSN = PAT_ENC.PAT_ENC_CSN_ID

left outer join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

WHERE FLO_MEAS_ID = '7060420' AND PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G)

GROUP BY PAT_ENC_CSN_ID

ORDER BY PAT_ENC_CSN_ID

--NOTE: This is the one sql codeset that runs two queries. The below is the demographics basics. This can be pasted in as a separate table.

SELECT distinct PAT_ENC_CSN_ID, BMI, ZC_MARITAL_STATUS.NAME as [Marital Status], DATEDIFF(year, BIRTH_DATE, CONTACT_DATE) as [Age at Admission],

ZC_PATIENT_RACE.NAME as [Race], ZC_ETHNIC_GROUP.NAME [Ethnicity], ZC_SEX.NAME as [Sex], DATEDIFF(DAY, HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) as [Length_of_Stay]

FROM IP_FLWSHT_MEAS

left outer join IP_FLWSHT_REC on IP_FLWSHT_MEAS.FSD_ID = IP_FLWSHT_REC.FSD_ID

left outer join IP_DATA_STORE on IP_FLWSHT_REC.INPATIENT_DATA_ID = IP_DATA_STORE.INPATIENT_DATA_ID

left outer join PAT_ENC on IP_DATA_STORE.EPT_CSN = PAT_ENC.PAT_ENC_CSN_ID

left outer join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join PATIENT_RACE on PATIENT.PAT_ID = PATIENT_RACE.PAT_ID

left outer join ZC_PATIENT_RACE on ZC_PATIENT_RACE.PATIENT_RACE_C = PATIENT_RACE.PATIENT_RACE_C

left outer join ZC_MARITAL_STATUS on ZC_MARITAL_STATUS.MARITAL_STATUS_C = PATIENT.MARITAL_STATUS_C

left outer join ZC_LANGUAGE on ZC_LANGUAGE.LANGUAGE_C = PATIENT.LANGUAGE_C

left outer join ZC_ETHNIC_GROUP on ZC_ETHNIC_GROUP.ETHNIC_GROUP_C = PATIENT.ETHNIC_GROUP_C

left outer join ZC_SEX on ZC_SEX.RCPT_MEM_SEX_C = PATIENT.SEX_C

WHERE FLO_MEAS_ID = '7060420' AND PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #SCD_TEMP)

ORDER BY PAT_ENC_CSN_ID

--Note: Creates Duplicates that have to be removed in Excel due to potential for patient to have multiple race/ethnicity lines. Using random gen script to choose one for each patient.

SQL CODE FOR “TOTAL DEMOGRAPHICS”

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

SELECT distinct PAT_ENC_CSN_ID, BMI, ZC_MARITAL_STATUS.NAME as [Marital Status], DATEDIFF(year, BIRTH_DATE, CONTACT_DATE) as [Age at Admission],

ZC_PATIENT_RACE.NAME as [Race], ZC_ETHNIC_GROUP.NAME [Ethnicity], ZC_SEX.NAME as [Sex], DATEDIFF(DAY, HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) as [Length_of_Stay]

FROM IP_FLWSHT_MEAS

left outer join IP_FLWSHT_REC on IP_FLWSHT_MEAS.FSD_ID = IP_FLWSHT_REC.FSD_ID

left outer join IP_DATA_STORE on IP_FLWSHT_REC.INPATIENT_DATA_ID = IP_DATA_STORE.INPATIENT_DATA_ID

left outer join PAT_ENC on IP_DATA_STORE.EPT_CSN = PAT_ENC.PAT_ENC_CSN_ID

left outer join PATIENT on PAT_ENC.PAT_ID = PATIENT.PAT_ID

left outer join PATIENT_RACE on PATIENT.PAT_ID = PATIENT_RACE.PAT_ID

left outer join ZC_PATIENT_RACE on ZC_PATIENT_RACE.PATIENT_RACE_C = PATIENT_RACE.PATIENT_RACE_C

left outer join ZC_MARITAL_STATUS on ZC_MARITAL_STATUS.MARITAL_STATUS_C = PATIENT.MARITAL_STATUS_C

left outer join ZC_LANGUAGE on ZC_LANGUAGE.LANGUAGE_C = PATIENT.LANGUAGE_C

left outer join ZC_ETHNIC_GROUP on ZC_ETHNIC_GROUP.ETHNIC_GROUP_C = PATIENT.ETHNIC_GROUP_C

left outer join ZC_SEX on ZC_SEX.RCPT_MEM_SEX_C = PATIENT.SEX_C

PAT_ENC.PAT_ENC_CSN_ID in (SELECT * FROM #G)

ORDER BY PAT_ENC_CSN_ID

SQL CODE FOR “TRAUMA SURGERY”

--Create temporary tables. Must be run at beginning of EACH session connection.

--First create Temporary Table for all surgery encounters (age>=18, not pregnant)

--Normal exclusion criteria can't be used here. Surgery encoutners don't have HOSP_ADMSN_TIME, only CONTACT_DATE.

SELECT distinct(V_SURGERY.PAT_ENC_CSN_ID), V_SURGERY.PAT_ID, V_SURGERY.CASE_SCHED_STATUS_C, V_SURGERY.SCHED_PAT_OUT_ROOM_DATETIME INTO #TS_TEMP_SURG

FROM V_SURGERY

inner join PATIENT on V_SURGERY.PAT_ID = PATIENT.PAT_ID

inner join PAT_ENC on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(year, BIRTH_DATE, CONTACT_DATE) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND V_SURGERY.SCHED_PAT_OUT_ROOM_DATETIME > '2012-07-01'

--Then create Temporary Table for all hospital encounters (this is where you MUST have all the exclusion criteria in where clause)

--QUESTION: Is encounter of 51 best choice, or should be using V_Surgery and using SCHED_PAT_OUT_ROOM_DATETIME which

--is only populated for CASE_SCHED_STATUS_C = '8' (for complete)

SELECT PAT_ENC.PAT_ID, PAT_ENC_CSN_ID, ENC_TYPE_C, CONTACT_DATE, HOSP_ADMSN_TIME INTO #TS_TEMP_HOSP

FROM PAT_ENC

inner join PATIENT on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

(DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null))

SELECT distinct(PAT_ENC_CSN_ID) into #G

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

SELECT DISTINCT(PAT_ENC.PAT_ENC_CSN_ID),

CASE

when PAT_ENC.PAT_ENC_CSN_ID in

(SELECT #TS_TEMP_HOSP.PAT_ENC_CSN_ID

FROM #TS_TEMP_HOSP

left outer join #TS_TEMP_SURG on #TS_TEMP_HOSP.PAT_ID = #TS_TEMP_SURG.PAT_ID

WHERE DATEDIFF(day, #TS_TEMP_SURG.SCHED_PAT_OUT_ROOM_DATETIME, #TS_TEMP_HOSP.HOSP_ADMSN_TIME) between 0 and 31 If you remove this line you get all the patients, with it in everyone will have TS_Score =2

) then 2

when PAT_ENC.PAT_ENC_CSN_ID in

(SELECT DISTINCT(PAT_ENC.PAT_ENC_CSN_ID)

FROM PAT_ENC_DX

left outer join HSP_ADMIT_DIAG on PAT_ENC_DX.PAT_ENC_CSN_ID = HSP_ADMIT_DIAG.PAT_ENC_CSN_ID

left outer join PAT_ENC on PAT_ENC_DX.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID

left outer join PATIENT on PAT_ENC_DX.PAT_ID = PATIENT.PAT_ID

left outer join CLARITY_EDG on PAT_ENC_DX.DX_ID = CLARITY_EDG.DX_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND

(CLARITY_EDG.ICD9_CODE between '800' and '959.9'

OR

ADMIT_DIAG_TEXT like '%trauma%')

) then 2

else 0 end [TraumaSurgery]

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

PAT_ENC.PAT_ENC_CSN_ID IN (SELECT * FROM #G)

ORDER BY PAT_ENC_CSN_ID

SQL CODE FOR “VTE EVENT”

--Should never look at first ICD9_CODE since POA = Yes and it being first ICD9 code makes no sense

--POA = W (clinically indeterminate age of dx)

--INCLUSION/EXCLUSION:

--1. 18 yo, not pregnant, not hospice, LOS >2 days

--2. VTE as principal diagnosis, OR VTE as secondary dx with POA=Y, OR on therapeutic anticoagulation (warfarin, enox tx dose (not 30 QD, 30 BID, or 40 QD), or heparin drip)

--OUTCOMES ASSESSMENT:

--in-hospital VTE (the thing we are trying to predict) is:

--VTE in positions diagnosis positions 2 and up, AND VTE POA=N or U

--VTE in any position in a subsequent hospitalization if no VTE during this admission

Select distinct(PAT_ENC_CSN_ID),

CASE when

PAT_ENC_CSN_ID in (SELECT PAT_ENC.PAT_ENC_CSN_ID FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',

'671.31','671.33','671.44','673.2',

)

AND (DX_POA_YNU not like 'Y' or DX_POA_YNU is null)

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

) then 1 else 0 end [VTE_Event]

from PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join PATIENT

on PATIENT.PAT_ID = PAT_ENC.PAT_ID

WHERE

DATEDIFF(day,HOSP_ADMSN_TIME, HOSP_DISCHRG_TIME) >= 2

AND ENC_TYPE_C = '3'

AND HOSP_ADMSN_TIME between '2012-07-01' and '2014-04-01'

AND DATEDIFF(year, BIRTH_DATE, HOSP_ADMSN_TIME) >= 18

AND (LMP_OTHER_C not LIKE '4' or LMP_OTHER_C is null)

AND PAT_ENC_CSN_ID not in (SELECT distinct(PAT_ENC_CSN_ID) FROM

PAT_ENC

inner join HSP_ACCT_DX_LIST

on HSP_ACCT_DX_LIST.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID

inner join CLARITY_EDG

on CLARITY_EDG.DX_ID = HSP_ACCT_DX_LIST.DX_ID

WHERE (CLARITY_EDG.ICD9_CODE = 'V66.7')

OR --Ensure no Acute VTE with POA = Y

(CLARITY_EDG.ICD9_CODE in ('415.1','415.11','415.12','415.13','415.19',

'444.21', '444.22','451.1','451.19','451.2','451.81',

'453.2','453.4','453.40','453.41','453.42','453.8','453.82','453.83','453.84','453.85','453.87','453.89','453.9',)

AND (DX_POA_YNU = 'Y' or DX_POA_YNU = 'W'))

)

Order by PAT_ENC_CSN_ID

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download