-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries.txt
119 lines (90 loc) · 5.28 KB
/
queries.txt
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
#### Current Billed Amount ####
SELECT SUM(P.charged)
FROM payments P
LEFT JOIN appointments A ON A.id=P.appointmentID
LEFT JOIN customers CU ON CU.id=A.customerID
LEFT JOIN clinics CL ON CL.id=CU.clinicID
LEFT JOIN casetypes CT ON CT.id=CU.casetypeID
LEFT JOIN providers PR ON PR.id=P.providerID
WHERE P.tranSubType='SV' AND P.transactionDate>='2022-05-01' AND P.transactionDate<'2022-06-01' AND CL.name='Chandler' AND PR.name NOT LIKE '%cryo t%' AND CT.name NOT LIKE '%cryo%' AND P.tranSubType<>'OT'
#### Insurance Billed Amount ####
SELECT SUM(P.charged)
FROM payments P
LEFT JOIN appointments A ON A.id=P.appointmentID
LEFT JOIN customers CU ON CU.id=A.customerID
LEFT JOIN clinics CL ON CL.id=CU.clinicID
LEFT JOIN casetypes CT ON CT.id=CU.casetypeID
LEFT JOIN providers PR ON PR.id=P.providerID
WHERE P.tranSubType='SV' AND P.transactionDate>='2022-05-01' AND P.transactionDate<'2022-06-01' AND CL.name='Chandler' AND PR.name NOT LIKE '%cryo t%' AND CT.name NOT LIKE '%cryo%' AND P.tranSubType<>'OT' AND CT.name NOT LIKE '%PI%' AND CT.name NOT LIKE '%WC%'
#### PI Billed Amount ####
SELECT SUM(P.charged)
FROM payments P
LEFT JOIN appointments A ON A.id=P.appointmentID
LEFT JOIN customers CU ON CU.id=A.customerID
LEFT JOIN clinics CL ON CL.id=CU.clinicID
LEFT JOIN casetypes CT ON CT.id=CU.casetypeID
LEFT JOIN providers PR ON PR.id=P.providerID
WHERE P.tranSubType='SV' AND P.transactionDate>='2022-05-01' AND P.transactionDate<'2022-06-01' AND CL.name='Chandler' AND PR.name NOT LIKE '%cryo t%' AND CT.name NOT LIKE '%cryo%' AND P.tranSubType<>'OT' AND ( CT.name LIKE '%PI%' OR CT.name LIKE '%WC%' )
#### Insurance Collections #### Copay/Coins/Ded must be added
SELECT SUM(P.charged)
FROM payments P
LEFT JOIN appointments A ON A.id=P.appointmentID
LEFT JOIN customers CU ON CU.id=A.customerID
LEFT JOIN clinics CL ON CL.id=CU.clinicID
LEFT JOIN casetypes CT ON CT.id=CU.casetypeID
LEFT JOIN providers PR ON PR.id=P.providerID
WHERE P.tranSubType='IN' AND P.transactionDate>='2022-05-01' AND P.transactionDate<'2022-06-01' AND CL.name='Chandler' AND PR.name NOT LIKE '%cryo t%' AND CT.name NOT LIKE '%cryo%' AND P.tranSubType<>'OT' AND ( CT.name NOT LIKE '%PI%' AND CT.name NOT LIKE '%WC%' )
#### PI/WC collections ####
SELECT SUM(P.charged)
FROM payments P
LEFT JOIN appointments A ON A.id=P.appointmentID
LEFT JOIN customers CU ON CU.id=A.customerID
LEFT JOIN clinics CL ON CL.id=CU.clinicID
LEFT JOIN casetypes CT ON CT.id=CU.casetypeID
LEFT JOIN providers PR ON PR.id=P.providerID
WHERE P.tranType='P' AND P.transactionDate>='2022-05-01' AND P.transactionDate<'2022-06-01' AND CL.name='Chandler' AND PR.name NOT LIKE '%cryo t%' AND CT.name NOT LIKE '%cryo%' AND P.tranSubType<>'OT' AND ( CT.name LIKE '%PI%' OR CT.name LIKE '%WC%' )
### OTC Collections ###
SELECT SUM(P.charged)
FROM payments P
LEFT JOIN appointments A ON A.id=P.appointmentID
LEFT JOIN customers CU ON CU.id=A.customerID
LEFT JOIN clinics CL ON CL.id=CU.clinicID
LEFT JOIN casetypes CT ON CT.id=CU.casetypeID
LEFT JOIN providers PR ON PR.id=P.providerID
WHERE P.tranType='P' AND P.tranSubType IN ('CC','CK','CS','DD') AND P.transactionDate>='2022-05-01' AND P.transactionDate<'2022-06-01' AND CL.name='Chandler' AND PR.name NOT LIKE '%cryo t%' AND CT.name NOT LIKE '%cryo%' AND P.tranSubType<>'OT' AND ( CT.name NOT LIKE '%PI%' AND CT.name NOT LIKE '%WC%')
#### Copay/Coins/Ded ###
SELECT SUM(P.charged)
FROM ayments P
LEFT JOIN appointments A ON A.id = P.appointmentID
LEFT JOIN customers CU ON CU.id = A.customerID
LEFT JOIN clinics CL ON CL.id = CU.clinicID
LEFT JOIN casetypes CT ON CT.id = CU.casetypeID
LEFT JOIN providers PR ON PR.id = P.providerID
WHERE P.tranType = 'P' AND P.tranSubType <> 'IN' AND P.transactionDate >= '2022-05-01' AND P.transactionDate < '2022-06-01' AND CL.name = 'Chandler' AND CT.name NOT LIKE '%cryo%' AND CT.name NOT LIKE '%denied care%' AND CT.name NOT IN('Weight Loss','DO NOT USE','Wellness','Weight Loss Cas') AND CT.name NOT LIKE '%cash%' AND CT.name NOT LIKE '%vip%' AND CT.name NOT LIKE '%massage%'
### Chiro Charges ###
SELECT SUM(P.charged)
FROM payments P
LEFT JOIN appointments A ON A.id = P.appointmentID
LEFT JOIN customers CU ON CU.id = A.customerID
LEFT JOIN clinics CL ON CL.id = CU.clinicID
LEFT JOIN casetypes CT ON CT.id = CU.casetypeID
LEFT JOIN providers PR ON PR.id = P.providerID
WHERE P.tranSubType ='SV' AND P.transactionDate >= '2022-05-01' AND P.transactionDate < '2022-06-01' AND CL.name = 'Chandler' AND PR.Specialty='Chiro'
### MD Charges ###
SELECT SUM(P.charged)
FROM payments P
LEFT JOIN appointments A ON A.id = P.appointmentID
LEFT JOIN customers CU ON CU.id = A.customerID
LEFT JOIN clinics CL ON CL.id = CU.clinicID
LEFT JOIN casetypes CT ON CT.id = CU.casetypeID
LEFT JOIN providers PR ON PR.id = P.providerID
WHERE P.tranSubType ='SV' AND P.transactionDate >= '2022-05-01' AND P.transactionDate < '2022-06-01' AND CL.name = 'Chandler' AND PR.Specialty='MD'
### PT Charges ###
SELECT SUM(P.charged)
FROM payments P
LEFT JOIN appointments A ON A.id = P.appointmentID
LEFT JOIN customers CU ON CU.id = A.customerID
LEFT JOIN clinics CL ON CL.id = CU.clinicID
LEFT JOIN casetypes CT ON CT.id = CU.casetypeID
LEFT JOIN providers PR ON PR.id = P.providerID
WHERE P.tranSubType ='SV' AND P.transactionDate >= '2022-05-01' AND P.transactionDate < '2022-06-01' AND CL.name = 'Chandler' AND PR.Specialty='PT'