Standard Extract Queries (Snowflake)
We've put together some sample queries that show common joins and reports that can be created using tables with the ‘TYPE’: Standard Extract.
SAMPLE QUERIES
EMR: Patient Cases, Notes, and Diagnosis Codes
with icd as (select distinct i.case_id,
i.icd_type,
i.icd_code,
row_number() OVER (partition BY i.case_id, i.icd_type order by c.CASEID) as code_number
from datamart.icd_codes i)
select
case when stage_visit = 1 and emr_charge_record_id = FIRST_VALUE(emr_charge_record_id) OVER (PARTITION BY "Emr Visit Id" ORDER BY emr_charge_record_id rows between unbounded preceding and unbounded following) then 1 else 0 end as "Visits"
,case when stage_IE = 1 and emr_charge_record_id = FIRST_VALUE(emr_charge_record_id) OVER (PARTITION BY "Emr Visit Id" ORDER BY emr_charge_record_id rows between unbounded preceding and unbounded following) then 1 else 0 end as "IEs"
,case when stage_discharge = 1 and emr_charge_record_id = FIRST_VALUE(emr_charge_record_id) OVER (PARTITION BY "Emr Visit Id" ORDER BY emr_charge_record_id rows between unbounded preceding and unbounded following) then 1 else 0 end as "Discharges"
,case when stage_visit <> 0 then "Units" else 0 end as "Billed Units"
,*
from (
select case when v.apttype in ('DN', 'OD')
AND v.doc_id > 0
AND v.status = 'A'
AND v.date_finalized is not null then 1 else 0 end as stage_visit,
case when v.apttype in ('IE')
AND v.doc_id > 0
AND v.status = 'A'
and v.date_finalized is not null then 1 else 0 end as stage_IE,
case when c.status IN ('A','D')
AND v.status IN ('A','P')
AND v.apttype NOT IN ('CN','NS')
AND v.includedailynote = 0
AND v.date_finalized IS NOT NULL
AND p.patient_status != 'I' then 1 else 0 end stage_discharge
,CASE WHEN b.cpt IN('97010','97012','97014','G0283','G0282','G0281','97016','97018','97022','97024','97026','97028','97039','96920','97610') AND COALESCE(v.visit_type,'') != 'Initial Examination' THEN b.units ELSE 0 END AS "Common Indirect Units"
,CASE WHEN b.cpt IN( '97110','97530','97140','97112') AND COALESCE(v.visit_type,'') != 'Initial Examination' THEN b.units ELSE 0 END AS "Core Direct Units"
,v.doc_id
,v.includedailynote as "Include Daily Note"
,v.visit_id as "Emr Visit Id"
,v.case_id as "Case Id"
,(date_trunc('day', v.visit_date::date)) as "Visit Date"
,v.visit_type as "Visit Type"
,la.visit_type as "Included Visit Type"
,v.status as "Visit Status"
,v.apttype as "Visit Type Id"
,(date_trunc('day', v.date_finalized::date)) as "Date Finalized"
,v.clinic_id as "Clinic Id"
,v.place_of_service_code as "Pos Identifier"
,v.is_billable_visit as "Is Billable Visit"
,c.case_name as "Case Name"
,c.medicare as "Is Medicare"
,c.status as "Case Status"
,(date_trunc('day', c.case_creation_date::date)) as "Case Creation Date"
,(date_trunc('day', c.case_closed_date::date)) as "Case Closed Date"
,v.patient_id as "Patient Id"
,v.treating_pt as "Therapist Id"
,b.units as "Units"
,b.cpt as "Cpt Code"
,b.record_id as emr_charge_record_id
,cl.place_of_service_id "Place Of Service Id"
,cl.gpms_code as "Gpms Code"
,cl.revflow_company_id as "Revflow Company Id"
,cl.company_id as "Company Id"
,p.patient_account_number as "Patient Account Number"
,pi1.subscriber as "Subscriber"
,coalesce(pi1.insurance_company_name,'') as "Primary Insurance"
,coalesce(pi2.insurance_company_name,'') as "Secondary Insurance"
,coalesce(pi1.coinsurance,'') as "Primary Coinsurance"
,coalesce(pi2.coinsurance,'') as "Secondary Coinsurance"
,coalesce(pi1.copay,'') as "Primary Copay"
,coalesce(pi2.copay,'') as "Secondary Copay"
,ic101.icd_code as "ICD10_1"
,ic102.icd_code as "ICD10_2"
,ic103.icd_code as "ICD10_3"
,ic104.icd_code as "ICD10_4"
,ic105.icd_code as "ICD10_5"
,ic91.icd_code as "ICD9_1"
,ic92.icd_code as "ICD9_2"
,ic93.icd_code as "ICD9_3"
,ic94.icd_code as "ICD9_4"
,ic95.icd_code as "ICD9_5"
FROM SVW_VISITS v
INNER JOIN SVW_CASES c ON v.case_id = c.case_id
INNER JOIN SVW_CHARGES b ON v.visit_id = b.visit_id AND v.case_id = b.case_id AND v.visit_date = b.visit_date
LEFT JOIN SVW_PATIENT p ON v.PATIENT_ID = p.patient_id
LEFT JOIN SVW_PHYSICIAN ph ON c.referring_doc = ph.physician_id
LEFT JOIN SVW_VISITS la ON v.visit_id = la.includedailynoteid
INNER JOIN SVW_CLINICS cl ON v.clinic_id = cl.clinic_id
LEFT JOIN SVW_PATIENT_INSURANCE pi1 ON c.case_id = pi1.case_id AND c.primaryins = pi1.patient_identity_id AND pi1.insurance_order = 1
LEFT JOIN SVW_PATIENT_INSURANCE pi2 ON c.case_id = pi2.case_id AND c.secondaryins = pi2.patient_identity_id AND pi2.insurance_order = 2
left join icd ic101 ON c.case_id = ic101.case_id AND ic101.code_number = 1 and ic101.icd_type = 'Icd10'
left join icd ic102 ON c.case_id = ic102.case_id AND ic102.code_number = 2 and ic102.icd_type = 'Icd10'
left join icd ic103 ON c.case_id = ic103.case_id AND ic103.code_number = 3 and ic103.icd_type = 'Icd10'
left join icd ic104 ON c.case_id = ic104.case_id AND ic104.code_number = 4 and ic104.icd_type = 'Icd10'
left join icd ic105 ON c.case_id = ic105.case_id AND ic105.code_number = 5 and ic105.icd_type = 'Icd10'
left join icd ic91 ON c.case_id = ic91.case_id AND ic101.code_number = 1 and ic101.icd_type = 'Icd9'
left join icd ic92 ON c.case_id = ic92.case_id AND ic102.code_number = 2 and ic102.icd_type = 'Icd9'
left join icd ic93 ON c.case_id = ic93.case_id AND ic103.code_number = 3 and ic103.icd_type = 'Icd9'
left join icd ic94 ON c.case_id = ic94.case_id AND ic104.code_number = 4 and ic104.icd_type = 'Icd9'
left join icd ic95 ON c.case_id = ic95.case_id AND ic105.code_number = 5 and ic105.icd_type = 'Icd9') x;
Scheduled Visits Report
select appointment_type,
appointment_id,
calendar_title,
case_id,
check_in_time,
check_out_time,
clinic_id,
end_date,
start_date_time,
parent_appointment_id,
patient_id,
start_date,
therapist_id,
appointment_creator_id,
case when check_out_time is not null then 'Checked-Out'
when check_in_time is not null then 'Checked-In'
when v.apttype = 'CN' then 'Cancel'
when v.apttype = 'NS' then 'No Show'
else 'Other' end
as appointment_status
from (select appointmnet_id as appointment_id,
parent_appointment_id AS parent_appointment_id,
to_char(sa.start_date,'MM/DD/YYYY') AS start_date,
to_char(sa.start_date, 'HH12:MI AM') AS start_date_time,
to_char(sa.end_date, 'HH12:MI AM') AS end_date,
to_char(sa.check_in_time, 'HH12:MI AM') AS check_in_time,
to_char(sa.check_out_time, 'HH12:MI AM') AS check_out_time,
calendar_title as calendar_title,
cl.clinic_id as clinic_id,
therapist_id as therapist_id,
appointment_type as appointment_type,
case_id as case_id,
patient_id as patient_id,
appointment_creator_id as appointment_creator_id,
row_number () over (partition by appointmnet_id order by start_date) as rnk
from datamart.appointments sa
inner join datamart.clinic cl on sa.clinic_id = cl.clinic_id
left join datamart.visit v on sa.patient_id = v.patient_id and trunc(sa.start_date) = v.visit_date and v.apttype in ('CN','NS'))t
where rnk = 1
Missed Notes
select count(distinct sa.appointment_id) as notes
,c.clinic_name
,CASE WHEN coalesce(p.last_name,'') = '' THEN '' ELSE coalesce(p.last_name,'') || ',' || ' ' || coalesce(p.first_name) END as patient_name
,t.full_name as therapist_name --calendar_name could be used here or the treating_therapist from the case if/when this is unavailable
,'Missing' as note_type
,trunc(start_date) as visit_date
from datamart.appointments sa
inner join datamart.clinic c on sa.clinic_id = c.clinic_id
inner join datamart.patient p on sa.patient_id = p.patient_id
left join datamart.therapist t on sa.therapist_id = t.therapist_id
left join datamart.visits v on sa.visit_id = v.visit_id and trunc(sa.start_date) = v.visit_date and v.apttype not in ('CN','NS') --Exclude cancel/no-show
where (appointment_status = 'active' or check_in_time is not null)
and sa.appointment_id is not null
and sa.patient_id !=0
and v.visit_id is null
group by c.clinic_name
,CASE WHEN coalesce(p.last_name,'') = '' THEN '' ELSE coalesce(p.last_name,'') || chr(8218) || ' ' || coalesce(p.first_name) END
,t.full_name
,'Missing'
,trunc(start_date)
Open/Draft Notes
select count(distinct sa.appointment_id) as notes
SELECT COUNT(DISTINCT la.visit_id) AS notes
,cl.clinic_name
,CASE WHEN coalesce(p.last_name,'') = '' THEN '' ELSE coalesce(p.last_name,'') || ',' || ' ' || coalesce(p.first_name) END as patient_name
,full_name as therapist_name
,'Draft' AS document_status
,la.apttype
,la.visit_date
FROM datamart.visits la
INNER JOIN datamart.patient p ON la.patient_id = p.patient_id
inner join datamart.clinic cl on la.clinic_id = cl.clinic_id
inner join datamart.therapist t on la.treating_pt = t.therapist_id
left join datamart.cases c on la.case_id = c.case_id and c.status = 'I'
where la.patient_id IS NOT NULL
AND la.apttype NOT IN('CN','NS')
AND la.date_finalized is null
AND la.status = 'A'
AND p.patient_status != 'I' --exclude inactive patients
AND la.visit_date between '1/1/2020' and '1/31/2020' --Just an example of a date range that could be used
group by cl.clinic_name
,CASE WHEN coalesce(p.last_name,'') = '' THEN '' ELSE coalesce(p.last_name,'') || ',' || ' ' || coalesce(p.first_name) END
,full_name
,'Draft'
,la.apttype
,la.visit_date
Charges, Payments, and Adjustments (Ledger)
SELECT dc.gpms_code as "Gpms Code"
,dc.charge_id as "Charge Id"
,dc.patient_account_number as "Patient Account Number"
,(date_trunc('day', dc.date_of_service::date)) as "Date of Service"
,(date_trunc('day', dc.charge_date::date)) as "Charge Date"
,dc.place_of_service_id as "Place Of Service Id"
,dc.pos_identifier as "Pos Identifier"
,dc.company_id as "Revflow Company Id"
,dc.webpt_billing_therapist_id as "Therapist Id"
,dc.cpt_code as "Cpt Code"
,dc.mod1 as "Mod1"
,dc.mod2 as "Mod2"
,dc.visit_id as "Revflow Visit Id"
,CASE
WHEN (
dpa.payment_adjustment_id = FIRST_VALUE(dpa.payment_adjustment_id) OVER( partition BY dc.charge_id ORDER BY dpa.payment_adjustment_id rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row)) THEN COALESCE(dc.charge_amount, (0)::DOUBLE PRECISION)
WHEN (
dpa.payment_adjustment_id IS NULL) THEN COALESCE(dc.charge_amount, (0)::DOUBLE PRECISION)
ELSE (0)::DOUBLE PRECISION
END AS "Total Charges",
CASE
WHEN (
dpa.payment_adjustment_id = FIRST_VALUE(dpa.payment_adjustment_id) OVER( partition BY dc.charge_id ORDER BY dpa.payment_adjustment_id rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row)) THEN COALESCE(dc.total_pay_amt, (0)::DOUBLE PRECISION)
WHEN (
dpa.payment_adjustment_id IS NULL) THEN COALESCE(dc.total_pay_amt, (0)::DOUBLE PRECISION)
ELSE (0)::DOUBLE PRECISION
END AS "Total Payments",
CASE
WHEN (
dpa.payment_adjustment_id = FIRST_VALUE(dpa.payment_adjustment_id) OVER( partition BY dc.charge_id ORDER BY dpa.payment_adjustment_id rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row)) THEN COALESCE(dc.total_ins_pay_amt, (0))
WHEN (
dpa.payment_adjustment_id IS NULL) THEN COALESCE(dc.total_ins_pay_amt, (0))
ELSE (0)
END AS "Total Ins Payments",
CASE
WHEN (
dpa.payment_adjustment_id = FIRST_VALUE(dpa.payment_adjustment_id) OVER( partition BY dc.charge_id ORDER BY dpa.payment_adjustment_id rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row)) THEN COALESCE(dc.total_pat_pay_amt, (0))
WHEN (
dpa.payment_adjustment_id IS NULL) THEN COALESCE(dc.total_pat_pay_amt, (0))
ELSE (0)
END AS "Total Pat Payments",
CASE
WHEN (
dpa.payment_adjustment_id = FIRST_VALUE(dpa.payment_adjustment_id) OVER( partition BY dc.charge_id ORDER BY dpa.payment_adjustment_id rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row)) THEN COALESCE(dc.total_adj_amt, (0)::DOUBLE PRECISION)
WHEN (
dpa.payment_adjustment_id IS NULL) THEN COALESCE(dc.total_adj_amt, (0)::DOUBLE PRECISION)
ELSE (0)::DOUBLE PRECISION
END AS "Total Adjustments",
CASE
WHEN (
dpa.payment_adjustment_id = FIRST_VALUE(dpa.payment_adjustment_id) OVER( partition BY dc.charge_id ORDER BY dpa.payment_adjustment_id rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row)) THEN COALESCE(dc.total_ins_adj_amt, (0))
WHEN (
dpa.payment_adjustment_id IS NULL) THEN COALESCE(dc.total_ins_adj_amt, (0))
ELSE (0)
END AS "Total Ins Adjust",
CASE
WHEN (
dpa.payment_adjustment_id = FIRST_VALUE(dpa.payment_adjustment_id) OVER( partition BY dc.charge_id ORDER BY dpa.payment_adjustment_id rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row)) THEN COALESCE(dc.total_pat_adj_amt, (0))
WHEN (
dpa.payment_adjustment_id IS NULL) THEN COALESCE(dc.total_pat_adj_amt, (0))
ELSE (0)
END AS "Total Pat Adjust",
CASE
WHEN (
dpa.payment_adjustment_id = FIRST_VALUE(dpa.payment_adjustment_id) OVER( partition BY dc.charge_id ORDER BY dpa.payment_adjustment_id rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row)) THEN COALESCE(dc.owed_amt, (0))
WHEN (
dpa.payment_adjustment_id IS NULL) THEN COALESCE(dc.owed_amt, (0))
ELSE (0)
END AS "Owed Amount",
CASE
WHEN (
dpa.payment_adjustment_id = FIRST_VALUE(dpa.payment_adjustment_id) OVER( partition BY dc.charge_id ORDER BY dpa.payment_adjustment_id rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row)) THEN COALESCE(dc.units, (0))
WHEN (
dpa.payment_adjustment_id IS NULL) THEN COALESCE(dc.units, (0))
ELSE (0)
END AS "Total Units",
(date_trunc('day', dpa.deposit_date::date)) as "Deposit Date",
dpa.payment_code_id as "Payment Code Id",
dpa.adjustment_code as "Adjustment Code",
dpa.adjustment_description as "Adjustment Description",
dpa.payment_amount as "Payment Amount",
dpa.payment_type as "Payment Type",
dpa.adjustment_amount as "Adjustment Amount",
dpa.created_by as "Created By",
(date_trunc('day', dpa.post_date::date)) AS "Post Date",
dc.cur_insurance_class as "Cur Insurance Class",
dc.cur_ins_class_desc as "Cur Insurance Class Desc",
dc.cur_ins_name as "Cur Insurance Name",
dc.cur_payer_name as "Cur Payer Name",
dc.cur_payer_desc as "Cur Payer Desc",
dc.primary_insurance_class as "Pri Insurance Class",
dc.pri_ins_class_desc as "Pri Insurance Class Desc",
dc.pri_ins_name as "Pri Insurance Name",
dc.pri_payer_name as "Pri Payer Name",
dc.pri_payer_desc as "Pri Payer Desc",
dc.secondary_insurance_class as "Sec Insurance Class",
dc.sec_ins_class_desc as "Sec Insurance Class Desc",
dc.sec_ins_name as "Sec Insurance Name",
dc.sec_payer_name as "Sec Payer Name",
dc.sec_payer_desc as "Sec Payer Desc",
dc.tertiary_insurance_class as "Ter Insurance Class",
dc.ter_ins_class_desc as "Ter Insurance Class Desc",
dc.ter_ins_name as "Ter Insurance Name",
dc.ter_payer_name as "Ter Payer Name",
dc.ter_payer_desc as "Ter Payer Desc",
dc.revenue_center_id "Revenue Center Id"
FROM SVW_BILLING_CHARGES dc
LEFT JOIN SVW_PAYMENT_ADJUSTMENTS dpa ON dc.company_id = dpa.company_id AND dc.charge_id = dpa.charge_id
Aging Insurance
select cur_insurance_class as "Cur Insurance Class"
,cur_ins_class_desc as "Cur Insurance Class Desc"
,cur_ins_name as "Cur Insurance Name"
,amt_current as "Amount Current"
,amt_30 as "30+ Days"
,amt_60 as "60+ Days"
,amt_90 as "90+ Days"
,amt_120 as "120+ Days"
,amt_150 as "150+ Days"
,amt_180 as "180+ Days"
,balance as "Balance"
,charges as "Total Charges"
,payments as "Total Payments"
,adjustments as "Total Adjustments"
,y.company_id as "Revflow Company Id"
,c.gpms_code as "Gpms Code"
,y.place_of_service_id as "Place Of Service Id"
,y.webpt_therapist_id as "Billing Therapist Id"
,y.patient_account_number as "Patient Account Number"
,y.CPT_CODE as "Cpt Code"
from (
select dc.cur_insurance_class::varchar as cur_insurance_class
,dc.cur_ins_class_desc::varchar as cur_ins_class_desc
,dc.cur_ins_name::varchar as cur_ins_name
,sum(case when datediff('d', dc.age_start_date, current_date()) < 30 then COALESCE(dc.owed_amt, (0)::DOUBLE PRECISION) else 0 end) as amt_current
,sum(case when datediff('d', dc.age_start_date, current_date()) between 30 and 59 then COALESCE(dc.owed_amt, (0)::DOUBLE PRECISION) else 0 end) as amt_30
,sum(case when datediff('d', dc.age_start_date, current_date()) between 60 and 89 then COALESCE(dc.owed_amt, (0)::DOUBLE PRECISION) else 0 end) as amt_60
,sum(case when datediff('d', dc.age_start_date, current_date()) between 90 and 119 then COALESCE(dc.owed_amt, (0)::DOUBLE PRECISION) else 0 end) as amt_90
,sum(case when datediff('d', dc.age_start_date, current_date()) between 120 and 149 then COALESCE(dc.owed_amt, (0)::DOUBLE PRECISION) else 0 end) as amt_120
,sum(case when datediff('d', dc.age_start_date, current_date()) between 150 and 179 then COALESCE(dc.owed_amt, (0)::DOUBLE PRECISION) else 0 end) as amt_150
,sum(case when datediff('d', dc.age_start_date, current_date()) > 179 then COALESCE(dc.owed_amt, (0)::DOUBLE PRECISION) else 0 end) as amt_180
,sum(COALESCE(dc.owed_amt, (0)::DOUBLE PRECISION)) AS balance
,sum(COALESCE(dc.charge_amount, (0)::DOUBLE PRECISION)) AS charges
,sum(COALESCE(dc.total_pay_amt, (0)::DOUBLE PRECISION)) AS payments
,sum(COALESCE(dc.total_adj_amt, (0)::DOUBLE PRECISION)) AS adjustments
,dc.company_id
,dc.place_of_service_id
,dc.webpt_billing_therapist_id as webpt_therapist_id
,dc.patient_account_number
,dc.CPT_CODE
from SVW_BILLING_CHARGES dc
left join SVW_PATIENT p on dc.patient_account_number = p.patient_account_number
where dc.owed_amt <> 0
and dc.cur_insurance_class is not null
and dc.age_start_date is not null
group by dc.cur_insurance_class::varchar,
dc.cur_ins_class_desc::varchar,
dc.cur_ins_name::varchar,
dc.company_id,
dc.place_of_service_id,
dc.webpt_billing_therapist_id,
dc.patient_account_number,
dc.CPT_CODE
union all
select
'ZZ - Credit Amounts' AS cur_insurance_class,
'ZZ - Credit Amounts' AS cur_ins_class_desc,
'ZZ - Credit Amounts' AS cur_ins_name,
(- sum(COALESCE(x.amt_current, (0)::DOUBLE PRECISION))) AS amt_current,
(- sum(COALESCE(x.amt_30, (0)::DOUBLE PRECISION))) AS amt_30,
(- sum(COALESCE(x.amt_60, (0)::DOUBLE PRECISION))) AS amt_60,
(- sum(COALESCE(x.amt_90, (0)::DOUBLE PRECISION))) AS amt_90,
(- sum(COALESCE(x.amt_120, (0)::DOUBLE PRECISION))) AS amt_120,
(- sum(COALESCE(x.amt_150, (0)::DOUBLE PRECISION))) AS amt_150,
(- sum(COALESCE(x.amt_180, (0)::DOUBLE PRECISION))) AS amt_180,
(- sum(((((((COALESCE(x.amt_current, (0)::DOUBLE PRECISION) + COALESCE(x.amt_30, (0)::DOUBLE PRECISION)) + COALESCE(x.amt_60, (0)::DOUBLE PRECISION)) + COALESCE(x.amt_90, (0)::DOUBLE PRECISION)) + COALESCE(x.amt_120, (0)::DOUBLE PRECISION)) + COALESCE(x.amt_150, (0)::DOUBLE PRECISION)) + COALESCE(x.amt_180, (0)::DOUBLE PRECISION)))) AS balance,
0 AS charges,
sum(((((((COALESCE(x.amt_current, (0)::DOUBLE PRECISION) + COALESCE(x.amt_30, (0)::DOUBLE PRECISION)) + COALESCE(x.amt_60, (0)::DOUBLE PRECISION)) + COALESCE(x.amt_90, (0)::DOUBLE PRECISION)) + COALESCE(amt_120, (0)::DOUBLE PRECISION)) + COALESCE(x.amt_150, (0)::DOUBLE PRECISION)) + COALESCE(x.amt_180, (0)::DOUBLE PRECISION))) AS payments,
0 AS adjustments,
x.company_id,
x.place_of_service_id,
x.webpt_therapist_id,
x.patient_account_number,
'ZZ - Credit Amounts' AS CPT_CODE
from (
select sum(case when datediff('d', c.created_date, current_date()) < 30 then COALESCE(c.amount, (0)::DOUBLE PRECISION) else 0 end) as amt_current
,sum(case when datediff('d', c.created_date, current_date()) between 30 and 59 then COALESCE(c.amount, (0)::DOUBLE PRECISION) else 0 end) as amt_30
,sum(case when datediff('d', c.created_date, current_date()) between 60 and 89 then COALESCE(c.amount, (0)::DOUBLE PRECISION) else 0 end) as amt_60
,sum(case when datediff('d', c.created_date, current_date()) between 90 and 119 then COALESCE(c.amount, (0)::DOUBLE PRECISION) else 0 end) as amt_90
,sum(case when datediff('d', c.created_date, current_date()) between 120 and 149 then COALESCE(c.amount, (0)::DOUBLE PRECISION) else 0 end) as amt_120
,sum(case when datediff('d', c.created_date, current_date()) between 150 and 179 then COALESCE(c.amount, (0)::DOUBLE PRECISION) else 0 end) as amt_150
,sum(case when datediff('d', c.created_date, current_date()) > 179 then COALESCE(c.amount, (0)::DOUBLE PRECISION) else 0 end) as amt_180
,c.company_id
,c.place_of_service_id
,c.webpt_therapist_id
,c.patient_account_number
from SVW_PATIENT_CREDIT c
left join SVW_CLINICS cl on c.place_of_service_id = cl.place_of_service_id
where amount <> 0
group by c.company_id, c.place_of_service_id, c.webpt_therapist_id, c.patient_account_number) x
group by x.company_id, x.place_of_service_id, x.webpt_therapist_id, x.patient_account_number) y
left join SVW_CLINICS c on y.place_of_service_id = c.place_of_service_id
Payment by Location
select gpms_code as "Gpms Code",
company_id as "Revflow Company Id",
place_of_service_id as "Place Of Service Id",
deposit_date as "Deposit Date",
payment_code as "Payment Code",
payment_desc as "Payment Desc",
sum(payments) as "Total Payments"
from (
select dc.GPMS_CODE,
dc.company_id,
dc.place_of_service_id,
(date_trunc('day', p.deposit_date::date)) as deposit_date,
coalesce(p.payment_code_code,'0000') as payment_code,
coalesce(p.payment_desc,'PATIENT PAYMENTS') as payment_desc,
sum(p.payment_amount) as payments
from SVW_BILLING_CHARGES dc
inner join SVW_PAYMENT_ADJUSTMENTS p on dc.charge_id = p.charge_id
where p.created_by not in ('2','125')
group by dc.GPMS_CODE, dc.company_id, dc.place_of_service_id, (date_trunc('day', p.deposit_date::date)), coalesce(p.payment_code_code,'0000'), coalesce(p.payment_desc,'PATIENT PAYMENTS')
UNION ALL
select c.gpms_code,
c.company_id,
p.place_of_service_id,
(date_trunc('day', p.deposit_date::date)) as deposit_date,
coalesce(p.payment_code_code,'0000') as payment_code,
coalesce(p.payment_desc,'PATIENT PAYMENTS') as payment_desc,
coalesce(sum(original_amount),0) as payments
from SVW_PATIENT_CREDIT p
inner join SVW_CLINICS c on p.place_of_service_id = c.place_of_service_id
where p.created_by not in ('2','125')
group by c.gpms_code, c.company_id, p.place_of_service_id, (date_trunc('day', p.deposit_date::date)), coalesce(p.payment_code_code,'0000'), coalesce(p.payment_desc,'PATIENT PAYMENTS')) x
group by gpms_code, company_id, place_of_service_id, deposit_date, payment_code, payment_desc
Tank (Unassigned) Patient Payments
SELECT cl.GPMS_CODE as "Gpms Code",
cl.REVFLOW_COMPANY_ID as "Revflow Company Id",
pc.PLACE_OF_SERVICE_ID as "Place Of Service Id",
pc.PATIENT_ACCOUNT_NUMBER as "Patient Account Number",
CASE WHEN coalesce(pc.FROM_PAYER_DESC,'') = '' THEN 'Cash Patient' ELSE pc.FROM_PAYER_DESC END AS "Payer Desc",
date_trunc('day',pc.created_date::date) as "Post Date",
date_trunc('day',pc.deposit_date::date) as "Deposit Date",
SUM(coalesce(pc.Original_Amount,0)) AS "Original Amt",
SUM(coalesce(pc.amount,0)) AS "Deposit Amt"
FROM SVW_PATIENT_CREDIT pc
INNER JOIN SVW_CLINICS cl ON cl.PLACE_OF_SERVICE_ID = pc.place_of_service_id
WHERE pc.created_by NOT IN ( '2', '125' )
GROUP BY cl.GPMS_CODE,
cl.REVFLOW_COMPANY_ID,
pc.PLACE_OF_SERVICE_ID,
pc.PATIENT_ACCOUNT_NUMBER,
CASE WHEN coalesce(pc.FROM_PAYER_DESC,'') = '' THEN 'Cash Patient' ELSE pc.FROM_PAYER_DESC END,
date_trunc('day',pc.created_date::date),
date_trunc('day',pc.deposit_date::date)
Unapplied Insurance Payments
SELECT dc.CHARGE_ID as "Charge Id",
dc.PATIENT_ACCOUNT_NUMBER as "Patient Account Number",
(date_trunc('day', dc.DATE_OF_SERVICE::date)) as "DOS",
(date_trunc('day', dpa.deposit_date::date)) as "Payment_Adjustment Deposit",
dc.CPT_CODE,
dpa.PAYMENT_ADJUSTMENT_ID,
dpa.PAYMENT_AMOUNT,
pc.PATIENT_CREDIT_ID,
pc.ORIGINAL_AMOUNT,
pc.AMOUNT
FROM PUBLIC.SVW_BILLING_CHARGES dc
JOIN PUBLIC.SVW_PAYMENT_ADJUSTMENTS dpa on dc.CHARGE_ID = dpa.CHARGE_ID
JOIN PUBLIC.SVW_PATIENT_CREDIT pc on dpa.PATIENT_CREDIT_ID = pc.PATIENT_CREDIT_ID
WHERE dc.CPT_CODE = 'INSOVER'
AND pc.PATIENT_CREDIT_TYPE = 2
Patient_Credit connected to Payment_Adjustment
SELECT pa.PATIENT_CREDIT_ID, pa.PAYMENT_AMOUNT, pc.AMOUNT, pc.ORIGINAL_AMOUNT --Notice the pc.AMOUNT is now less than the PC.ORIGINAL_AMOUNT, this is because the transferred amount is pa.PAYMENT_AMOUNT. -- The pc.ORIGINAL_AMOUNT is the static field on the PATIENT_CREDIT table FROM PUBLIC.SVW_PAYMENT_ADJUSTMENTS pa JOIN PUBLIC.SVW_PATIENT_CREDIT pc ON pa.PATIENT_CREDIT_ID = pc.PATIENT
Payment/Adjustments by Patient/Insurance Breakout
SELECT adj.PAYMENT_ADJUSTMENT_ID
, ch.PATIENT_ACCOUNT_NUMBER
, adj.ADJUSTMENT_AMOUNT
, adj.PAYMENT_AMOUNT
, adj.FROM_INSURANCE_CODE
, adj.FROM_INSURANCE_CLASS
, ch.PRI_INS_CODE
, ch.PRIMARY_INSURANCE_CODE
, ch.PRIMARY_INSURANCE_CLASS
, ch.SECONDARY_INSURANCE_CODE
, ch.SECONDARY_INSURANCE_CLASS
, ch.TERTIARY_INSURANCE_CODE
, ch.TERTIARY_INSURANCE_CLASS
, CASE WHEN ifnull(ADJ.FROM_INSURANCE_CODE,0) = CH.PRIMARY_INSURANCE_CODE and
ADJ.FROM_INSURANCE_CLASS = CH.PRIMARY_INSURANCE_CLASS THEN 'Primary'
WHEN ifnull(ADJ.FROM_INSURANCE_CODE,0) = CH.SECONDARY_INSURANCE_CODE and
ADJ.FROM_INSURANCE_CLASS = CH.SECONDARY_INSURANCE_CLASS THEN 'Secondary'
WHEN ifnull(ADJ.FROM_INSURANCE_CODE,0) = CH.TERTIARY_INSURANCE_CODE and
ADJ.FROM_INSURANCE_CLASS = CH.TERTIARY_INSURANCE_CLASS THEN 'Tertiary'
ELSE 'Other' END AS WhichInsurance
FROM SVW_PAYMENT_ADJUSTMENTS adj
JOIN SVW_BILLING_CHARGES ch ON adj.CHARGE_ID = ch.CHARGE_ID
Refunds by Patient
SELECT PATIENT_ACCOUNT_NUMBER, sum(ORIGINAL_AMOUNT) as "Total Refunds by Patient" FROM PUBLIC.SVW_PATIENT_CREDIT WHERE PATIENT_CREDIT_TYPE = 7 GROUP BY PATIENT_ACCOUNT_NUMBER
Balances: Insurance vs. Patient
SELECT CHARGE_ID,
--Charge_id can be swapped for some other granularity
sum(CASE ifnull(CUR_INS_CODE,0)
WHEN 0 THEN 0 ELSE CUR_BALANCE END) AS "Ins Balance",
sum(CASE ifnull(CUR_INS_CODE,0)
WHEN 0 THEN CUR_BALANCE ELSE 0 END) AS "Pat Balance"
FROM PUBLIC.SVW_BILLING_CHARGES
WHERE CPT_CODE NOT IN ('COPAY','INSOVER')
GROUP BY CHARGE_ID