forked from mtikoian/sql-scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathBad Debt 20171215 .sql
More file actions
168 lines (160 loc) · 9.83 KB
/
Copy pathBad Debt 20171215 .sql
File metadata and controls
168 lines (160 loc) · 9.83 KB
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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
declare @start_date as date = DATEADD(month,-17,CONVERT(DATETIME, CONVERT(VARCHAR(7), getdate(), 120) + '-01'))
declare @end_date as date = DATEADD(day,-1,DATEADD(month,-3,CONVERT(DATETIME, CONVERT(VARCHAR(7), getdate(), 120) + '-01')));
with charges as
(select
*
from
(select
tdl.tx_id
,tdl.match_trx_id
,tdl.post_date
,tdl.loc_id
,tdl.dept_id
,tdl.pos_id
,tdl.account_id
,tdl.orig_service_date
,tdl.orig_post_date
,tdl.proc_id
,tdl.original_payor_id
,tdl.orig_amt
,tdl.match_proc_id
,tdl.amount
,ROW_NUMBER() OVER(PARTITION BY tdl.tx_id ORDER BY tdl.post_date asc) as Row#
from clarity.dbo.clarity_tdl_tran tdl
left join clarity.dbo.arpb_tx_void atv on atv.tx_id = tdl.tx_id
where tdl.serv_area_id in (11,13,16,17,18,19)
and tdl.match_proc_id = 7064 --COLLECTIONS BAD DEBT WRITE OFF (ACCOUNT)
and tdl.detail_type in (21) -- MATCH/UNMATCH (CHARGE->CREDIT ADJUSTMENT)
and atv.tx_id is null
group by
tdl.tx_id
,tdl.match_trx_id
,tdl.post_date
,tdl.loc_id
,tdl.dept_id
,tdl.pos_id
,tdl.account_id
,tdl.orig_service_date
,tdl.orig_post_date
,tdl.proc_id
,tdl.original_payor_id
,tdl.orig_amt
,tdl.match_proc_id
,tdl.amount
)a
where row# = 1
),
eob as
(select *
from
(select
tdl.tx_id
,tdl.match_trx_id
,tdl.post_date
,eob.paid_amt
,eob.cvd_amt
,eob.noncvd_amt
,eob.ded_amt
,eob.copay_amt
,eob.coins_amt
,ROW_NUMBER() OVER(PARTITION BY tdl.tx_id ORDER BY eob.tdl_id desc) as Row#
from charges
left join clarity.dbo.clarity_tdl_tran tdl on tdl.tx_id = charges.tx_id
left join clarity.dbo.pmt_eob_info_i eob on eob.tdl_id = tdl.tdl_id
where
detail_type = 20 -- MATCH/UNMATCH (CHARGE->PAYMENT)
group by
tdl.tx_id
,eob.tdl_id
,tdl.match_trx_id
,tdl.post_date
,eob.paid_amt
,eob.cvd_amt
,eob.noncvd_amt
,eob.ded_amt
,eob.copay_amt
,eob.coins_amt
)a
where row# = 1
),
remit as
(select *
from
(
select
remit.payment_tx_id
,remit.match_chg_tx_id
,remit.remit_code_name
,remit.remit_code_cat_name
,remit.payor_nm_wid
,remit.payment_post_date
,remit.remark_code_1_id
,ROW_NUMBER() OVER(PARTITION BY remit.payment_tx_id, remit.match_chg_tx_id ORDER BY remit.payment_post_date) as Row#
from eob
left join clarity.dbo.v_arpb_remit_codes remit on remit.payment_tx_id = eob.match_trx_id and remit.match_chg_tx_id = eob.tx_id
where
remit.remit_action = 9
group by
remit.payment_tx_id
,remit.match_chg_tx_id
,remit.remit_code_name
,remit.remit_code_cat_name
,remit.payor_nm_wid
,remit.payment_post_date
,remit.remark_code_1_id
)a
where row#=1
)
select
charges.tx_id as 'Chg ID'
,charges.match_trx_id as 'Adj ID'
,charges.post_date as 'Adj Post Date'
,upper(sa.name) as 'Region'
,loc.loc_name as 'Location'
,dep.department_name as 'Department'
,dep.specialty as 'Specialty'
,dep16.name as 'Service Line'
,pos.pos_name as 'POS'
,pos.pos_type as 'POS Type'
,charges.account_id as 'Account'
,charges.orig_service_date as 'Service Date'
,charges.orig_post_date as 'Chg Post Date'
,eap_chg.proc_code as 'Proc Code'
,eap_chg.proc_name as 'Proc Desc'
,charges.orig_amt as 'Charge Amt'
,eap_adj.proc_code as 'Adj Code'
,eap_adj.proc_name as 'Adj Desc'
,charges.amount as 'Adj Amount'
,eob.match_trx_id as 'Pymnt ID'
,charges.tx_id as 'Matched Chg ID'
,charges.post_date as 'Pymnt Date'
,eob.paid_amt as 'Pymnt Amt'
,eob.cvd_amt as 'CVD Amt'
,eob.noncvd_amt as 'NonCVD Amt'
,eob.ded_amt as 'Ded Amt'
,eob.copay_amt as 'Copay Amt'
,eob.coins_amt as 'Coins Amt'
,date.year_month as 'Adj Month'
,case when remit.payor_nm_wid is null then 'Self-Pay' else 'Insurance' end as 'Coverage'
,upper(remit.remit_code_name) as 'Remit'
,remit.payor_nm_wid as 'Transaction Payor'
,case when rmc1.remit_code_name is not null then upper(zrcc.name) else upper(remit_code_cat_name) end as 'Remit Category'
from charges
left join eob on eob.tx_id = charges.tx_id
left join clarity.dbo.clarity_loc loc on loc.loc_id = charges.loc_id
left join clarity.dbo.zc_loc_rpt_grp_10 sa on sa.rpt_grp_ten = loc.rpt_grp_ten
left join clarity.dbo.clarity_pos pos on pos.pos_id = charges.pos_id
left join clarity.dbo.clarity_dep dep on dep.department_id = charges.dept_id
left join clarity.dbo.zc_dep_rpt_grp_16 dep16 on dep16.rpt_grp_sixteen_c = dep.rpt_grp_sixteen_c
left join clarity.dbo.clarity_eap eap_chg on eap_chg.proc_id = charges.proc_id
left join clarity.dbo.clarity_eap eap_adj on eap_adj.proc_id = charges.match_proc_id
left join clarity.dbo.date_dimension date on date.calendar_dt = charges.post_date
left join remit on remit.payment_tx_id = eob.match_trx_id and remit.match_chg_tx_id = eob.tx_id
left join clarity.dbo.clarity_rmc rmc1 on rmc1.remit_code_id = remit.remark_code_1_id
left join clarity.dbo.ZC_RMC_CODE_CAT zrcc on zrcc.RMC_CODE_CAT_C = rmc1.CODE_CAT_C
where charges.post_date >= @start_date
and charges.post_date <= @end_date
--and (remit.remit_action = 9 or remit.remit_action is null) --denied
--and charges.tx_id in (8105312, 7403001)
order by charges.tx_id asc
--558,704