MDedge
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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.