-
Notifications
You must be signed in to change notification settings - Fork 4
Expand file tree
/
Copy pathCharge Lag by Department.sql
More file actions
118 lines (105 loc) · 4.46 KB
/
Copy pathCharge Lag by Department.sql
File metadata and controls
118 lines (105 loc) · 4.46 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
declare @start_date as date = EPIC_UTIL.EFN_DIN('mb-1')
declare @end_date as date = EPIC_UTIL.EFN_DIN('me-1')
select
REGION
,DEPARTMENT
,sum([days lag]) as 'Lag Days'
,sum([Distinct Count]) as 'Distinct Count'
,convert(decimal(18,2),round(convert(decimal(18,2),round(sum([days lag]),2))/convert(decimal(18,2),round(sum([Distinct Count]),2)),2)) as [Avg Lag Days]
from
(select *
from
(select
case when loc.loc_id in (11106,11124,11149) then 'SPRINGFIELD'
when loc.loc_id in (11101,11102,11103,11104,11105,11115,11116,11122,11139,11140,1114,11142,11143,11144,11146,11151,11132,11138) then 'CINCINNATI'
when loc.loc_id in (13104,13105,13116) then 'YOUNGSTOWN'
when loc.loc_id in (16102,16103,16104,19132,19133,19134) then 'LIMA'
when loc.loc_id in (17105,17106,17107,17108,17109,17110,17112,17113,19135,19136,19137,19138,19139,19140,19141) then 'LORAIN'
when loc.loc_id in (18120,18121,19120,19127) then 'DEFIANCE'
when loc.loc_id in (18101,18102,18103,18104,18105,18130,18131,18132,18133,19119,19128,19129,19130,19131,19121,19122,19123,19124) then 'TOLEDO'
when loc.loc_id in (19101,19102,19106) then 'KENTUCKY'
when loc.loc_id in (131201,131202) then 'SUMMA'
end as 'REGION'
,loc.loc_name as 'LOCATION'
,dep.department_name as 'DEPARTMENT'
,date.monthname_year as 'MONTH'
,pos.pos_name as 'POS_NAME'
,pat.pat_mrn_id as 'PAT_MRN_ID'
,tdl.charge_slip_number as 'CHARGE_SLIP_NUMBER'
,tdl.pat_enc_csn_id as 'ORIG_CSN'
,tdl.pat_id as 'ORIG_PAT_ID'
,arpb_match.pat_enc_csn_id as 'MATCHED_CSN'
,tdl.orig_service_date as 'ORIG_SERVICE_DATE'
,tdl.post_Date as 'POST_DATE'
,min(arpb_match.post_date) as 'Earliest Post Date'
-->>>>>>>> Calculation for Total Charges <<<<<<<<<<<
,sum(case when tdl.detail_type in (1,10) then tdl.amount end) as 'Total Charges'
,case when tdl.post_date = min(arpb_match.post_date) then datediff(day,tdl.orig_service_date,tdl.post_date) end as 'Days Lag'
,case when tdl.post_date = min(arpb_match.post_date) then 1
when tdl.post_date = tdl.orig_service_date then 1
else 0
end as 'Distinct Count'
from clarity_tdl_tran tdl
left join arpb_transactions arpb_match on arpb_match.patient_id = tdl.int_pat_id and tdl.orig_service_date = arpb_match.service_date
left join patient pat on pat.pat_id = tdl.int_pat_id
left join clarity_pos pos on pos.pos_id = tdl.pos_id
left join clarity_loc loc on loc.loc_id = tdl.loc_id
left join clarity_dep dep on dep.department_id = tdl.dept_id
left join date_dimension date on date.calendar_dt_str = tdl.post_date
where
tdl.post_date >= @start_date
and tdl.post_date <=@end_date
and tdl.detail_type in (1,10)
--and loc.loc_id in (11106,11124,11149 -- then 'SPRINGFIELD'
-- ,11101,11102,11103,11104,11105,11115,11116,11122,11139,11140,1114,11142,11143,11144,11146,11151,11132,1113 -- 'CINCINNATI'
-- ,13104,13105,13116 -- 'YOUNGSTOWN'
-- ,16102,16103,16104,19132,19133,19134 -- 'LIMA'
-- ,17105,17106,17107,17108,17109,17110,17112,17113,19135,19136,19137,19138,19139,19140,19141 -- 'LORAIN'
-- ,18120,18121,19120,19127 -- 'DEFIANCE'
-- ,18101,18102,18103,18104,18105,18130,18131,18132,18133,19119,19128,19129,19130,19131,19121,19122,19123,19124 -- 'TOLEDO'
-- ,19101,19102,19106 -- 'KENTUCKY'
-- ,131201,131202) -- 'SUMMA'
group by
case when loc.loc_id in (11106,11124,11149) then 'SPRINGFIELD'
when loc.loc_id in (11101,11102,11103,11104,11105,11115,11116,11122,11139,11140,1114,11142,11143,11144,11146,11151,11132,11138) then 'CINCINNATI'
when loc.loc_id in (13104,13105,13116) then 'YOUNGSTOWN'
when loc.loc_id in (16102,16103,16104,19132,19133,19134) then 'LIMA'
when loc.loc_id in (17105,17106,17107,17108,17109,17110,17112,17113,19135,19136,19137,19138,19139,19140,19141) then 'LORAIN'
when loc.loc_id in (18120,18121,19120,19127) then 'DEFIANCE'
when loc.loc_id in (18101,18102,18103,18104,18105,18130,18131,18132,18133,19119,19128,19129,19130,19131,19121,19122,19123,19124) then 'TOLEDO'
when loc.loc_id in (19101,19102,19106) then 'KENTUCKY'
when loc.loc_id in (131201,131202) then 'SUMMA'
end
,loc.loc_name
,dep.department_name
,tdl.billing_provider_id
,date.monthname_year
,pos.pos_name
,pat.pat_mrn_id
,tdl.charge_slip_number
,tdl.pat_enc_csn_id
,tdl.pat_id
,arpb_match.pat_enc_csn_id
,tdl.orig_service_date
,tdl.post_date
) as t
group by
region
,location
,department
,month
,pos_name
,pat_mrn_id
,charge_slip_number
,orig_csn
,orig_pat_id
,matched_csn
,orig_service_date
,post_date
,[earliest post date]
,[total charges]
,[days lag]
,[distinct count]
) as lag
group by region
,department