Open Dental Forum

Posted: **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.

Posted: **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

Open Dental Forum

Insurance Info Per Patient, Grouped by Service Year?

Insurance Info Per Patient, Grouped by Service Year?

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