Procedures by insurance carrier

Collaboration:  Feel free to  join below fb groups to receive new updates/queries/reports etc.

Fb group: https://www.facebook.com/groups/549203762775872

Fb page: https://www.facebook.com/HRDSQ/?ref=pages_you_manage

Step1a:Copy the below SUMMARY query and paste into your Report query. Save as favorite Report template.
SET @FromDate = '2020-01-01';
SET @ToDate ='2021-12-31';
DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (
ProcNum INT(5),
Patients INT(5),
Appointments INT(5),
CarrierName TEXT,
Production DOUBLE NOT NULL DEFAULT 0,
Adjustments DOUBLE NOT NULL DEFAULT 0,
Writeoffs DOUBLE NOT NULL DEFAULT 0);
INSERT INTO t1(ProcNum,Patients,Appointments,Production,CarrierName)
SELECT ProcNum, COUNT(DISTINCT pl.PatNum) AS 'Patients', COUNT(DISTINCT pl.AptNum) AS 'Appointments', ROUND(SUM(pl.procfee),0) AS 'Production',c.CarrierName
FROM procedurelog pl
INNER JOIN patient p ON p.PatNum=pl.PatNum
LEFT JOIN patplan pp ON p.PatNum = pp.PatNum
LEFT JOIN inssub ins ON pp.InsSubNum = ins.InsSubNum
LEFT JOIN insplan ip ON ins.PlanNum = ip.PlanNum
LEFT JOIN carrier c ON ip.CarrierNum = c.CarrierNum
WHERE (pl.ProcStatus=2 AND pl.ProcDate BETWEEN @FromDate AND @ToDate) AND p.PatStatus NOT IN (2,3,4,5)
GROUP BY ProcNum,c.CarrierName;
CREATE TABLE t2
SELECT ProcNum, ROUND(SUM(a.AdjAmt),0) AS 'Adjustments'
FROM adjustment a
INNER JOIN patient p ON p.PatNum=a.PatNum
GROUP BY ProcNum;
UPDATE t1,t2 SET t1.Adjustments=t2.Adjustments WHERE t1.ProcNum=t2.ProcNum;
DROP TABLE IF EXISTS t2;
CREATE TABLE t2
SELECT ProcNum,
ROUND(SUM(cp.WriteOff),0) AS 'Writeoffs'
FROM claimproc cp
INNER JOIN patient p ON p.PatNum=cp.PatNum
WHERE (cp.Status=1 OR cp.Status=4 OR cp.Status=0) AND cp.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY ProcNum;
UPDATE t1,t2 SET t1.Writeoffs=t2.Writeoffs WHERE t1.ProcNum=t2.ProcNum;
DROP TABLE IF EXISTS t2;
SELECT t1.CarrierName, pc.ProcCode, SUM(Patients) AS 'NumPats', SUM(Production)+SUM(Adjustments)-SUM(Writeoffs) AS 'NetProd', CONCAT(ROUND(((SUM(Production)+SUM(Adjustments)-SUM(Writeoffs))/(SELECT SUM(Production)+SUM(Adjustments)-SUM(Writeoffs) FROM t1)*100),0),'%') AS 'NetPct', SUM(Appointments) AS 'Appts', CONCAT(ROUND((SUM(Appointments)/(SELECT SUM(Appointments) FROM t1)*100),0),'%') AS 'ApptsPct', ROUND(SUM(Appointments)/SUM(Patients),2) AS 'ApptsPerPat', ROUND((SUM(Production)+SUM(Adjustments)-SUM(Writeoffs))/SUM(Patients),0) AS 'NetPerPat', ROUND((SUM(Production)+SUM(Adjustments)-SUM(Writeoffs))/SUM(Appointments),0) AS 'NetPerApt'
FROM t1 INNER JOIN procedurelog pl ON t1.ProcNum = pl.ProcNum
INNER JOIN procedurecode pc ON pc.codeNum = pl.codeNum
GROUP BY pc.ProcCode, t1.CarrierName WITH ROLLUP ;

Other Reports: HRDSQ got cool out of the box RECALL and TX over due reports, where you can message all your patients in just couple clicks – happy to share more info, if needed.

If you have any reporting need or need  help, feel free to contact .

Other Recommendations:

1. What is HRDSQ? How dental offices benefit using HRDSQ? Click here to learn more.
2. Dr. Peter received 45 reviews on the first day after launching feedback campaign with HRDSQ – click here to learn more.
3. Dr. Larry’s staff saves 15 mins on each patient everyday with use of quick insurance verification on their calendars to quickly spot on the patients failing insurance eligibility. Click  here to learn more.
4. Click here to learn customizable and paper-less patient forms with HRDSQ.
5. Want to launch powerful patient reach outbound campaigns using HRDSQ. Click here to learn more.
6. Looking for a HIPAA compliant website assistant or chatbot to perform concierge services, click here to learn more.

About HRDSQ: Our advance technology integration within Open Dental allows our clients to carry out their day-to-day activities in a hassle-free and paperless manner, with Smart Caller ID, Real-Time Insurance Verification, custom online forms, 2-way text, and much much more. Engaged your  patients like never before and be the #1 Patient Choice.

Click Request Demo button on HRDSQ page, incase you would like to see one.

Thank you.

About Author: Amar Veeramalla is experienced dental professional providing productivity hacks to dentists, dental professionals and staff on Patient Engagement, Case Acceptance & Patient Retention using modern and cutting edge technologies like AI, ML, Advanced SQL languages and other tools. You can reach at amar@hrdsq.com or on LK: https://www.linkedin.com/in/amarveeramalla/

revised 7.23.2021