≡ Menu

EAP Extract, Epic’s Clarity

Below is a handy SQL script to get the most recent contacts of procedure orders (EAPs) from Epic’s Clarity tables.

/***********************************************************
EAP Extract, SQL
Description: This SQL script extracts ALL EAPs from Clarity.
The script extracts the most recent contact ONLY,
thereby eliminating unnecessary duplicates. It also
associates the chargeable EAPs back to the original EAP.
Author: robert@rebelwords.org
Date: April 10th, 2015
************************************************************/
SELECT
EAP.PROC_ID as "ID [.1]"
,EAP.PROC_NAME as "Record Name [.2]"
,EAP.PROC_CODE as "Procedure Code [100]"
,EAP.IS_ACTIVE_YN as "Status [60]"
,EAP.SHORT_NAME as "Short Name [107]"
,EAP.PROC_CAT_ID as "Category Code [200]"
,PROC_CAT_NAME as "Category Name [200]"
,EAP.PROC_GROUP_ID as "Cluster/Group [205]"
,EPG.PROC_GROUP_NAME as "Cluster/Grop Name [205]"
,EAP.IS_EC_INACTIVE_YN as "EpicCare Inactive Flag [207]"
,CPT_CODE as "CPT Code [2000]"
,CODE_TYPE_C as "Code Type [2005]"
,ORDERABLE_YN as "Orderable [6660]"
,PERFORMABLE_YN as "Performable [6661]"
,CHARGEABLE_YN as "Chargeable [6662]"
,HISTORICAL_YN as "Historical [6663]"
,LINKED_CHARGES.LINKED_CHRG_ID as "Chargeable ID [.1]"
,LINKED_CHARGES.PROC_NAME as "Chargeable Name [.2]"
,LINKED_CHARGES.PROC_CODE as "Chargeable Code [100]"
,LINKED_CHARGES.CHRG_LINK_TYPE_C as "Chargeable Link Type [6667]"
,LINKED_CHARGES.CHARGE_TYPE_C as "Link Charge Type [6668]"
FROM
CLARITY_EAP EAP
LEFT JOIN (SELECT *
FROM
(
SELECT
DENSE_RANK() OVER (PARTITION BY PROC_ID ORDER BY CONTACT_DATE_REAL DESC) as "RANK"
,PROC_ID
,ORDERABLE_YN
,PERFORMABLE_YN
,CHARGEABLE_YN
,HISTORICAL_YN
,CODE_TYPE_C
,CPT_CODE
FROM
CLARITY_EAP_OT
) temp1
WHERE RANK = 1) EAP_OT
ON EAP.PROC_ID = EAP_OT.PROC_ID
LEFT JOIN EDP_PROC_CAT_INFO EDP
ON EAP.PROC_CAT_ID = EDP.PROC_CAT_ID
LEFT JOIN
(SELECT *
FROM
(
SELECT
DENSE_RANK() OVER (PARTITION BY LINKED_CHARGEABLES.PROC_ID ORDER BY CONTACT_DATE_REAL) as "RANK"
,LINKED_CHARGEABLES.PROC_ID
,LINKED_CHRG_ID
,CHRG_LINK_TYPE_C
,CHARGE_TYPE_C
,PROC_NAME
,PROC_CODE
FROM
LINKED_CHARGEABLES
LEFT JOIN
CLARITY_EAP
ON LINKED_CHARGEABLES.LINKED_CHRG_ID=CLARITY_EAP.PROC_ID
) temp2
WHERE RANK = 1) LINKED_CHARGES
ON EAP.PROC_ID = LINKED_CHARGES.PROC_ID
LEFT JOIN CLARITY_EPG EPG
ON EAP.PROC_GROUP_ID = EPG.PROC_GROUP_ID
;