to allow actual "Use it or Lose it" emails to patients that don't just go out to all patients, but targets them with a fully customized email with their available unused balance. Give it a try and let me know what you think. The first practice I ran it on had $3.3 million in unused balances.
Code: Select all
SELECT guarantor,patients_on_plan,
primary_group_name,
family_preventive_max_benefit,
max_benefit_per_person,
family_ytd_benefit,
total_plan_benefit,
( total_plan_benefit - family_ytd_benefit ) AS ytd_unused_benefits,
secondary_group_name,
secondary_preventive_max_per_person,
secondary_max_benefit_per_person,
family_ytd_secondary_benefit,
secondary_total_plan_benefit,
( secondary_total_plan_benefit - family_ytd_secondary_benefit ) AS secondary_ytd_unused_benefits
FROM (SELECT precalc_results.guarantor,Count(precalc_results.patnum) AS patients_on_plan,precalc_results.insurance_plan_primary,
precalc_results.primary_group_name,
precalc_results.family_preventive_max_benefit,
precalc_results.family_preventive_percent,
precalc_results.max_benefit_per_person,
precalc_results.family_max_percent,
CASE
WHEN precalc_results.benefit_amount THEN SUM(precalc_results.benefit_amount)
ELSE "0.00"
END AS family_ytd_benefit,
CASE WHEN coverage_level = 1
THEN precalc_results.max_benefit_per_person
WHEN coverage_level = 2
THEN ( Count(precalc_results.patnum) * precalc_results.max_benefit_per_person )
ELSE precalc_results.max_benefit_per_person
END AS total_plan_benefit,
precalc_results.secondary_group_name,
precalc_results.secondary_preventive_max_per_person,
precalc_results.secondary_family_preventive_percent,
precalc_results.secondary_max_benefit_per_person,
precalc_results.secondary_family_max_percent,
CASE
WHEN precalc_results.secondary_benefit_amount THEN SUM(precalc_results.secondary_benefit_amount)
ELSE "0.00"
END AS family_ytd_secondary_benefit,
CASE WHEN secondary_coverage_level = 1
THEN precalc_results.secondary_max_benefit_per_person
WHEN secondary_coverage_level = 2
THEN ( Count(precalc_results.patnum) * precalc_results.secondary_max_benefit_per_person )
ELSE precalc_results.secondary_max_benefit_per_person
END AS secondary_total_plan_benefit
FROM (SELECT patient.patnum,guarantor,primaryplan.plannum AS insurance_plan_primary,
primaryplan.groupname AS primary_group_name,
benefit.preventative_max_benefit AS family_preventive_max_benefit,
benefit.preventative_max_percent AS family_preventive_percent,
benefit.plan_max_benefit AS max_benefit_per_person,
benefit.plan_max_percent AS family_max_percent,
benefit.sum_payamt AS benefit_amount,
benefit.CoverageLevel as coverage_level,
secondaryplan.groupname AS secondary_group_name,
secondary_benefit.preventative_max_benefit AS secondary_preventive_max_per_person,
secondary_benefit.preventative_max_percent AS secondary_family_preventive_percent,
secondary_benefit.plan_max_benefit AS secondary_max_benefit_per_person,
secondary_benefit.plan_max_percent AS secondary_family_max_percent,
secondary_benefit.sum_payamt AS secondary_benefit_amount,
secondary_benefit.CoverageLevel as secondary_coverage_level
FROM patient
left join (SELECT patplan.patnum,patplan.inssubnum,PLAN.groupname,PLAN.plannum
FROM patplan
left join (SELECT inssubnum,plannum
FROM inssub) AS subscriber
ON subscriber.inssubnum = patplan.inssubnum
left join (SELECT plannum,groupname,groupnum,feesched,plantype,monthrenew
FROM insplan) AS PLAN
ON PLAN.plannum = subscriber.plannum
WHERE ordinal = 1) AS primaryplan
ON patient.patnum = primaryplan.patnum
left join (SELECT patplan.patnum,patplan.inssubnum,PLAN.groupname,PLAN.plannum
FROM patplan
left join (SELECT inssubnum,plannum
FROM inssub) AS subscriber
ON subscriber.inssubnum = patplan.inssubnum
left join (SELECT plannum,groupname,groupnum,feesched,plantype,monthrenew
FROM insplan) AS PLAN
ON PLAN.plannum = subscriber.plannum
WHERE ordinal = 2) AS secondaryplan
ON patient.patnum = secondaryplan.patnum
left join (SELECT covcatnum,benefit.plannum,prev.prev_max AS preventative_max_benefit,
prev_max_percent AS preventative_max_percent,
Max(monetaryamt) AS plan_max_benefit,
Max(percent) AS plan_max_percent,
insurance.sum_payamt,
CoverageLevel
FROM benefit
left join (SELECT benefitnum,plannum,Max(percent) AS prev_max_percent,Max(monetaryamt) AS prev_max
FROM benefit
WHERE covcatnum = 11
GROUP BY plannum) AS prev
ON prev.plannum = benefit.plannum
left join (SELECT patnum,dateservice,claimstatus,plannum,plannum2,Round(SUM(claimfee), 2) AS sum_claimfee,
Round(SUM(inspayamt), 2) AS sum_payamt,
Round(SUM(inspayest), 2) AS sum_insurance_estimate,
Round(SUM(writeoff), 2) AS sum_writeoff,
Round(( SUM(claimfee) - SUM(inspayamt) - SUM(writeoff) ), 2) AS total_balance_due,
Round((SUM(claimfee) - SUM(inspayest) - SUM(writeoff)), 2) AS total_balance_estimate
FROM claim
WHERE Year (dateservice) = Year (Now())
AND claimtype = "p"
GROUP BY patnum) AS insurance
ON insurance.plannum = benefit.plannum
WHERE benefit.covcatnum != 11
GROUP BY plannum) AS benefit
ON benefit.plannum = primaryplan.plannum
left join (SELECT covcatnum,benefit.plannum,prev.prev_max AS preventative_max_benefit,
prev_max_percent AS preventative_max_percent,
Max(monetaryamt) AS plan_max_benefit,
Max(percent) AS plan_max_percent,
insurance.sum_payamt,
CoverageLevel
FROM benefit
left join (SELECT benefitnum,plannum,Max(percent) AS prev_max_percent,Max(monetaryamt) AS prev_max
FROM benefit
WHERE covcatnum = 11
GROUP BY plannum) AS prev
ON prev.plannum = benefit.plannum
left join (SELECT patnum,dateservice,claimstatus,plannum,plannum2,Round(SUM(claimfee), 2) AS sum_claimfee,
Round(SUM(inspayamt), 2) AS sum_payamt,
Round(SUM(inspayest), 2) AS sum_insurance_estimate,
Round(SUM(writeoff), 2) AS sum_writeoff,
Round(( SUM(claimfee) - SUM(inspayamt) - SUM(writeoff) ), 2) AS total_balance_due,
Round((SUM(claimfee) - SUM(inspayest) - SUM(writeoff)), 2) AS total_balance_estimate
FROM claim
WHERE Year (dateservice) = Year (Now())
AND claimtype = "p"
GROUP BY patnum) AS insurance
ON insurance.plannum = benefit.plannum
WHERE benefit.covcatnum != 11
GROUP BY plannum) AS secondary_benefit
ON secondary_benefit.plannum = secondaryplan.plannum
WHERE Length(primaryplan.plannum) > 0
GROUP BY patnum) AS precalc_results
GROUP BY precalc_results.guarantor) AS results
order by total_plan_benefit