Insurance Info Per Patient, Grouped by Service Year?

This forum is for programmers who have questions about the source code.

Insurance Info Per Patient, Grouped by Service Year?

Postby ajhalls » Mon Mar 19, 2018 2:01 pm

I am trying to output a table with the following columns to match Dentrix info:
plan_id
patient_id
current_year_benefits
patient_count
family_deductible_met
family_preventive_deductible_met
family_benefits_last_year
family_deductible_last_year
family_preventive_deductible_last_year
max_family_coverage
max_person_coverage
secondary_plan_id
secondary_current_year_benefits
secondary_patient_count
secondary_family_deductible_met
secondary_family_preventive_deductible_met
secondary_family_benefits_last_year
secondary_family_deductible_last_year
secondary_family_preventive_deductible_last_year
secondary_max_family_coverage
secondary_max_person_coverage


So far I have the following query, which is kind of a catchall of everything I can make sense of, hoping to then whittle it down:
Code: Select all
SELECT * FROM (SELECT
   claim.ClaimNum,
   patient.patnum,
   patient.Guarantor,
   benefit.percent,
   CASE
WHEN benefit.TimePeriod = '0' THEN
   'None'
WHEN benefit.TimePeriod = '1' THEN
   'ServiceYear'
WHEN benefit.TimePeriod = '2' THEN
   'CalendarYear'
WHEN benefit.TimePeriod = '3' THEN
   'Lifetime'
WHEN benefit.TimePeriod = '4' THEN
   'Years'
ELSE
   'Undefined'
END AS benefit_time_period,
CASE
WHEN benefit.CoverageLevel = '0' THEN
   'None specified - Typical for percentages and copayments.'
WHEN benefit.CoverageLevel = '1' THEN
   'Individual'
WHEN benefit.CoverageLevel = '2' THEN
   'Family'
ELSE
   'Undefined'
END AS benefit_coverage_level,
benefit_cat.description,
benefit_codes.fromcode,
benefit_codes.tocode,
benefit_cat.defaultpercent,
CASE
WHEN YEAR (claim.DateService) = YEAR (now()) THEN
   "current"
ELSE
   YEAR (claim.DateService)
END AS year_service,
claim.ClaimStatus,
claim.PlanNum,
claim.PlanNum2,
claim.claimfee,
claim.inspayamt,
claim.WriteOff,
(ClaimFee - InsPayAmt - WriteOff) as balance_due,
plan.GroupName,
plan2.GroupName AS GroupName2,
plan.monthrenew,
plan2.monthrenew AS monthrenew2,
b2.monetaryamt AS monetaryamt2,
benefit.monetaryamt AS plan_benefit_limit
FROM
   patient
LEFT JOIN claim AS claim ON claim.PatNum = patient.PatNum
LEFT JOIN insplan AS plan ON plan.plannum = claim.plannum
LEFT JOIN insplan AS plan2 ON plan2.plannum = claim.PlanNum2
AND claim.PlanNum2 != "0"
LEFT JOIN benefit AS benefit ON benefit.plannum = claim.PlanNum
LEFT JOIN benefit AS b2 ON b2.plannum = claim.PlanNum2
AND claim.PlanNum2 != "0"
LEFT JOIN covcat AS benefit_cat ON benefit_cat.covcatnum = benefit.covcatnum
LEFT JOIN covspan AS benefit_codes ON benefit_codes.covcatnum = benefit.covcatnum
)as results
where results.year_service > "2016"
group by ClaimNum


Here is the data I am trying to match it up to:
http://www.sqlfiddle.com/#!9/219cf7/1/0

I am trying to figure out how to get the current insurance usage from each patient for the current year and the previous year. The query I have will only return patients that have had work done, not those who have not used insurance at all. We also need to take into consideration the renew month. I am not sure yet what to do about grouping them by guarantor. Any help would be appreciated.
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - The $50/month Alternative - For When Good Enough... Isn't
ajhalls
 
Posts: 17
Joined: Fri Jan 10, 2014 2:41 pm
Location: Utah

Re: Insurance Info Per Patient, Grouped by Service Year?

Postby ajhalls » Tue Mar 20, 2018 1:08 pm

Got it working in ~140 lines of MySql :) I am in the first stages of integrating it into https://www.ReminderDental.com 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.

I hope to have integrations done by end of the week.

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
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - The $50/month Alternative - For When Good Enough... Isn't
ajhalls
 
Posts: 17
Joined: Fri Jan 10, 2014 2:41 pm
Location: Utah


Return to Developers

Who is online

Users browsing this forum: No registered users and 3 guests

cron