sql-scripts/Anesthesia Tranaction File.sql at master · mtikoian/sql-scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
SELECT
CLARITY_TDL_TRAN.TDL_ID,
CLARITY_TDL_TRAN.DETAIL_TYPE,
CLARITY_TDL_TRAN.POST_DATE,
CLARITY_TDL_TRAN.ORIG_POST_DATE,
CLARITY_TDL_TRAN.ORIG_SERVICE_DATE,
CLARITY_TDL_TRAN.TX_ID,
CLARITY_TDL_TRAN.TRAN_TYPE,
CLARITY_TDL_TRAN.MATCH_TRX_ID,
CLARITY_TDL_TRAN.ACCOUNT_ID,
CLARITY_TDL_TRAN.PAT_ID,
CLARITY_TDL_TRAN.AMOUNT,
CLARITY_TDL_TRAN.PATIENT_AMOUNT,
CLARITY_TDL_TRAN.INSURANCE_AMOUNT,
CLARITY_TDL_TRAN.RELATIVE_VALUE_UNIT,
CLARITY_TDL_TRAN.CUR_PAYOR_ID,
CLARITY_TDL_TRAN.CUR_PLAN_ID,
CLARITY_TDL_TRAN.PROC_ID,
CLARITY_TDL_TRAN.PERFORMING_PROV_ID,
CLARITY_TDL_TRAN.BILLING_PROVIDER_ID,
CLARITY_TDL_TRAN.ORIGINAL_PAYOR_ID,
CLARITY_TDL_TRAN.ORIGINAL_PLAN_ID,
CLARITY_TDL_TRAN.PROCEDURE_QUANTITY,
CLARITY_TDL_TRAN.CPT_CODE,
CLARITY_TDL_TRAN.MODIFIER_ONE,
CLARITY_TDL_TRAN.MODIFIER_TWO,
CLARITY_TDL_TRAN.MODIFIER_THREE,
CLARITY_TDL_TRAN.MODIFIER_FOUR,
CLARITY_TDL_TRAN.DX_ONE_ID,
CLARITY_TDL_TRAN.DX_TWO_ID,
CLARITY_TDL_TRAN.DX_THREE_ID,
CLARITY_TDL_TRAN.DX_FOUR_ID,
CLARITY_TDL_TRAN.DX_FIVE_ID,
CLARITY_TDL_TRAN.DX_SIX_ID,
CLARITY_TDL_TRAN.SERV_AREA_ID,
CLARITY_TDL_TRAN.LOC_ID,
CLARITY_TDL_TRAN.DEPT_ID,
CLARITY_TDL_TRAN.POS_ID,
CLARITY_TDL_TRAN.INVOICE_NUMBER,
CLARITY_TDL_TRAN.CLM_CLAIM_ID,
CLARITY_TDL_TRAN.PAT_AGING_DAYS,
CLARITY_TDL_TRAN.INS_AGING_DAYS,
CLARITY_TDL_TRAN.ACTION_PAYOR_ID,
CLARITY_TDL_TRAN.REASON_CODE_ID,
LEFT(CLARITY_TDL_TRAN.USER_ID,18),
CLARITY_TDL_TRAN.TX_NUM,
CLARITY_TDL_TRAN.INT_PAT_ID,
CLARITY_TDL_TRAN.RVU_WORK,
CLARITY_TDL_TRAN.RVU_OVERHEAD,
CLARITY_TDL_TRAN.RVU_MALPRACTICE,
CLARITY_TDL_TRAN.REFERRAL_SOURCE_ID,
CLARITY_TDL_TRAN.REFERRAL_ID,
CLARITY_TDL_TRAN.MATCH_PAYOR_ID,
CLARITY_TDL_TRAN.VISIT_NUMBER,
CLARITY_TDL_TRAN.CHARGE_SLIP_NUMBER,
CLARITY_TDL_TRAN.PERIOD,ZC_SPECIALTY.NAME,
/* USE PRE 4/1/2014 FORMULA WHEN ORIG_POST_DATE <= 3/31/14. */
CASE      WHEN CLARITY_TDL_TRAN.ORIG_POST_DATE <= '2014-03-31' THEN
                CASE      WHEN (CLARITY_TDL_TRAN.DETAIL_TYPE IN (1,10)) AND (CPT_CODE NOT IN ('90460', '90461', '90471', '90472', '90473', '90474') OR CPT_CODE IS NULL) THEN
                                CASE      WHEN COALESCE(CLARITY_TDL_TRAN.RVU_WORK,0) > 0 THEN CLARITY_TDL_TRAN.RVU_WORK * COALESCE(CLARITY_TDL_TRAN.PROCEDURE_QUANTITY,0)
                                                                ELSE COALESCE(CLARITY_TDL_TRAN.RELATIVE_VALUE_UNIT,0)
                                END
                                ELSE 0
                END
/* USE POST 4/1/2014 FORMULA WHEN ORIG_POST_DATE > 3/31/14. */
                ELSE
                                CASE      WHEN (CLARITY_TDL_TRAN.DETAIL_TYPE IN (1,10)) AND (CPT_CODE NOT IN ('90460', '90461', '90471', '90472', '90473', '90474', '96360', '96365', '96401', '96413', '96415', '96420', '96422', '96423', '96425', '96440', '96446', '96450', '96542', '96361', '96366', '96367', '96368', '96369', '96370', '96371', '96372',                                                                                                 '96373', '96374', '96375', '96376', '96379', '96402', '96405', '96406', '96409', '96411', '96416', '96417', '96523') OR CPT_CODE IS NULL) THEN
                                                CASE      WHEN COALESCE(CLARITY_TDL_TRAN.RVU_WORK,0) > 0 THEN CLARITY_TDL_TRAN.RVU_WORK * COALESCE(CLARITY_TDL_TRAN.PROCEDURE_QUANTITY,0)
                                                                                ELSE COALESCE(CLARITY_TDL_TRAN.RELATIVE_VALUE_UNIT,0)
                                                END
                                ELSE 0
                                END
                END AS SUB_RVU,
COALESCE((SELECT m.AdjustPct from SSIS.dbo.CHPIT_Anodyne_Modifier_Adjustments_For_RVU m where m.Modifier = CLARITY_TDL_TRAN.MODIFIER_ONE),1.00) AS MODIFIER_ADJUSTMENT1,
COALESCE((SELECT m.AdjustPct from SSIS.dbo.CHPIT_Anodyne_Modifier_Adjustments_For_RVU m where m.Modifier = CLARITY_TDL_TRAN.MODIFIER_TWO),1.00) AS MODIFIER_ADJUSTMENT2,
COALESCE((SELECT m.AdjustPct from SSIS.dbo.CHPIT_Anodyne_Modifier_Adjustments_For_RVU m where m.Modifier = CLARITY_TDL_TRAN.MODIFIER_THREE),1.00) AS MODIFIER_ADJUSTMENT3,
COALESCE((SELECT m.AdjustPct from SSIS.dbo.CHPIT_Anodyne_Modifier_Adjustments_For_RVU m where m.Modifier = CLARITY_TDL_TRAN.MODIFIER_FOUR),1.00) AS MODIFIER_ADJUSTMENT4,
COALESCE((SELECT 1 from SSIS.dbo.CHPIT_Anodyne_Modifier_Adjustments_For_RVU m where m.Modifier = CLARITY_TDL_TRAN.MODIFIER_ONE),0) +
                COALESCE((SELECT 1 from SSIS.dbo.CHPIT_Anodyne_Modifier_Adjustments_For_RVU m where m.Modifier = CLARITY_TDL_TRAN.MODIFIER_TWO),0) +
                COALESCE((SELECT 1 from SSIS.dbo.CHPIT_Anodyne_Modifier_Adjustments_For_RVU m where m.Modifier = CLARITY_TDL_TRAN.MODIFIER_THREE),0) +
                COALESCE((SELECT 1 from SSIS.dbo.CHPIT_Anodyne_Modifier_Adjustments_For_RVU m where m.Modifier = CLARITY_TDL_TRAN.MODIFIER_FOUR),0) AS MODIFIER_FOUND_COUNT

FROM   Clarity.dbo.CLARITY_TDL_TRAN CLARITY_TDL_TRAN
LEFT OUTER JOIN Clarity.dbo.ZC_SPECIALTY ZC_SPECIALTY ON CLARITY_TDL_TRAN.PROV_SPECIALTY_C = ZC_SPECIALTY.SPECIALTY_C

WHERE  CLARITY_TDL_TRAN.SERV_AREA_ID IN (11, 13, 16, 17, 18, 19)
AND ((CLARITY_TDL_TRAN.TDL_EXTRACT_DATE >= '2014-06-01'
AND CLARITY_TDL_TRAN.TDL_EXTRACT_DATE <= '2014-07-0') OR (CLARITY_TDL_TRAN.POST_DATE >= '2014-06-01' AND CLARITY_TDL_TRAN.POST_DATE <= '2014-07-0'))