Open Dental Forum

This query is designed to see patient account information more by visit date than by accounting date, much like old accounting module. This may be useful when answering a patient who asks 'why is my balance $52.32?'

Note that by default it will run summed by date, but can be easily expanded by running with @Detail=True

This is a first run, if you think it might be useful, test
(just paste to your Reports>User Query window see http://www.opendental.com/manual/queryoverview.html)
and comment, it is complex enough that I wanted to run it by some folks before posting to web site.

When first posted, this query was listed twice, now it has been reposted. Also, it was not accounting for fees where they had not been sent to insurance. That has been fixed.
Accounts for payment plans now.
Accounts for capitation (HMOs) now too

Wow.

can the capitalization be fixed? won't run on Linux server...all table names should be lower cases (paysplit is the only one i know causes and error...don't know if there are any more)...thanks..looks promising!

Are you aware that you can add a server variable to your my.ini (or cnf) to make capitalization work the same as on Windows?

drtech- Fixed, I only found the paysplit as PaySplit, thanks for the find.

Jordan...no I was not aware of that...prob should do that... but then who would find the Linux server bugs!

This query would be great as a report, hint hint. But is there an easy way to not only show the total insurance paid, but split it into primary insurance and secondary insurance?

Also is there a list of the all the data fields for OD available to look at?

oh thaaaats niiice.

Awesome, really!!!

NOTE: There is a bug in the code above that causes all running balances that are over $999 to display only the first digit of the number. For example $2,362 would display as 2.00. The final balance displays correctly, only the running balances are affected.
I spoke to Nathan earlier and he corrected it.
Here is the corrected code:

/*455 Special Statement grouped by procedure date*/
/*-- Special Statement shows transactions grouped by procedure date, helps to understand a families account, alternate to going line by line through someones account*/ SET @GPatNum='78', @Detail=False /*Set to True or False to show procedure level rows*/;
/*-- Run by putting in Guarantor's Patient Number and setting @Detail to True if you want procedure level detail*/
/*-- it would be very unusual, but you could limit to a given date range if you like, and would NOT bring balance forward*/ SET @FromDate='1900-01-01' , @ToDate='2099-12-31';
/*-- Accounts for payment plans and capitation (HMOs) now too
-- Show transactions by service date, then sum by date*/ DROP TABLE IF EXISTS tmp1, tmp2, tmp3; Set @Pos=0;/*counter for manipulation later*/ CREATE TABLE tmp1(FName VARCHAR(15),TranType VARCHAR(11),ProcDate DATE,Description VARCHAR(20),PatNum BIGINT,Fee DOUBLE,WriteOff DOUBLE,InsPayAmt DOUBLE,OutIns DOUBLE,Adjust DOUBLE,PatPaid DOUBLE,PatPort DOUBLE,ToBalance DOUBLE,RunningBal DOUBLE);
/*-- insert all completed procedures and any associated insurance claimprocs*/ INSERT INTO tmp1 SELECT p.FName, 'Charge' AS TranType,pl.ProcDate,(CASE WHEN pl.ToothNum<1 THEN pc.ProcCode ELSE CONCAT(pc.ProcCode, '-',pl.ToothNum) END) AS Description, pl.PatNum, pl.ProcFee*(pl.UnitQty+pl.BaseUnits) Fee,
SUM(cp.Writeoff) AS WriteOff,SUM(cp.InsPayAmt) AS InsPayAmt, 0 AS OutIns, 0 AS Adjust, IFNULL((SELECT SUM(ps.SplitAmt) FROM paysplit ps WHERE ps.ProcNum=pl.ProcNum AND ps.PayPlanNum=0),0) AS PatPaid, NULL AS 'PatPort', NULL AS 'ToBalance', NULL AS RunningBal FROM procedurelog pl INNER JOIN patient p ON p.PatNum=pl.PatNum INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum LEFT JOIN claimproc cp ON cp.ProcNum=pl.ProcNum AND cp.Status IN(1,4, 5, 7) /* received, supplemental, capclaim,capreceived*/ WHERE pl.ProcStatus=2 AND p.Guarantor=@GPatNum /*(SELECT g.Guarantor FROM patient g WHERE g.PatNum=@GPatNum)*/ AND pl.ProcDate BETWEEN @FromDate AND @ToDate GROUP BY pl.ProcNum ORDER BY PatNum, ProcDate;
/*-- insurance payments by total*/
INSERT INTO tmp1
SELECT p.FName, 'Other Payments' AS TranType, cp.ProcDate, 'Ins Pay by Total' AS Description, cp.PatNum, 0 AS Fee, SUM(cp.Writeoff) AS Writeoff, SUM(cp.InsPayAmt) AS InsPayAmt,0 AS OutIns, 0 AS Adjust, 0 AS PatPaid, NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal FROM claimproc cp INNER JOIN patient p ON cp.PatNum=p.PatNum WHERE cp.Status IN(1,4) /*rec, suppl*/ AND p.Guarantor=@GPatNum /*(SELECT g.Guarantor FROM patient g WHERE g.PatNum=@GPatNum)*/ AND cp.ProcNum=0 AND cp.ProcDate BETWEEN @FromDate AND @ToDate GROUP BY cp.PatNum, cp.ProcDate; INSERT INTO tmp1
/*-- Adjustments*/
SELECT FName, 'DaySumAdj' AS TranType, a.ProcDate, 'Adjustments' AS Description, a.PatNum, 0 AS FEE, 0 AS Writeoff, 0 AS InsPayAmt,0 AS OutIns, SUM(AdjAmt) AS Adjust,0 AS 'PatPaid', NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal FROM adjustment a INNER JOIN patient p on p.PatNum=a.PatNum WHERE p.Guarantor=@GPatNum /*(SELECT g.Guarantor FROM patient g WHERE g.PatNum=@GPatNum)*/ AND a.ProcDate BETWEEN @FromDate AND @ToDate GROUP BY a.PatNum, a.ProcDate UNION ALL
/*-- Remaining Patient Payments*/
SELECT FName, 'DaySumPay' AS TranType, ps.ProcDate, 'Pat Pay by Total' AS Description, p.PatNum, 0 AS FEE, 0 AS Writeoff, 0 AS InsPayAmt,0 AS OutIns, 0 AS Adjust, SUM(SplitAmt) AS PatPaid, NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal FROM paysplit ps INNER JOIN patient p on p.PatNum=ps.PatNum WHERE p.Guarantor=@GPatNum AND ps.ProcNum=0 AND ps.PayPlanNum=0 AND ps.ProcDate BETWEEN @FromDate AND @ToDate /*(SELECT g.Guarantor FROM patient g WHERE g.PatNum=@GPatNum)*/ GROUP BY ps.PatNum, ps.ProcDate UNION ALL
/*-- Outstanding Claims*/
SELECT p.FName, 'DayOutIns' AS TranType, cp.ProcDate, 'Day Outstand Ins' AS Description, cp.PatNum, 0 AS Fee, 0 AS Writeoff,0 AS InsPayAmt,(SUM(cp.Writeoff) + SUM(cp.InsPayEst)) AS OutIns, 0 AS Adjust, 0 AS PatPaid, NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal FROM claimproc cp INNER JOIN patient p ON cp.PatNum=p.PatNum WHERE cp.Status IN(0) /*sent*/ AND p.Guarantor=@GPatNum /*(SELECT g.Guarantor FROM patient g WHERE g.PatNum=@GPatNum)*/ AND cp.ProcDate BETWEEN @FromDate AND @ToDate GROUP BY cp.PatNum, cp.ProcDate UNION ALL /*Payment Plans*/ SELECT p.FName, 'PPComplete' AS TranType, pp.PayPlanDate as ProcDate, 'Payment Plan' AS Description, p.PatNum, 0 AS Fee, 0 AS Writeoff,0 AS InsPayAmt,0 AS OutIns,-pp.CompletedAmt AS Adjust, 0 AS PatPaid, NULL AS 'PatPort', NULL AS ToBalance, NULL AS RunningBal FROM payplan pp INNER JOIN patient p ON pp.PatNum=p.PatNum WHERE p.Guarantor=@GPatNum GROUP BY pp.PatNum, pp.PayPlanDate;
/*-- Add totals by date*/
CREATE TABLE tmp2
SELECT * FROM tmp1
UNION ALL
SELECT FName, 'Total' AS TranType, ProcDate, ' Total For Date' AS Description, PatNum, SUM(Fee) AS Fee, SUM(Writeoff) AS Writeoff, SUM(InsPayAmt) AS InsPayAmt, SUM(OutIns) AS OutIns,SUM(Adjust) AS Adjust, SUM(PatPaid) AS PatPaid, NULL AS PatPort, NULL AS ToBalance, NULL AS RunningBal
FROM tmp1 GROUP BY PatNum, ProcDate;
UPDATE tmp2
SET ToBalance=(IFNULL(Fee,0)-IFNULL(Writeoff,0)-IFNULL(InsPayAmt,0)-IFNULL(PatPaid,0)+IFNULL(Adjust,0))
WHERE TranType = 'Total' AND (IFNULL(Fee,0)-IFNULL(Writeoff,0)-IFNULL(InsPayAmt,0)-IFNULL(PatPaid,0)+IFNULL(Adjust,0))<>0;
UPDATE tmp2
SET PatPort=(IFNULL(Fee,0)-IFNULL(Writeoff,0)-IFNULL(InsPayAmt,0)-IFNULL(OutIns,0)) WHERE TranType = 'Total' AND Fee<>0; DROP TABLE IF EXISTS tmp1;

/*-- Now do running totals, using duplicate table*/ CREATE TABLE tmp1(FName VARCHAR(15),TranType VARCHAR(11),ProcDate DATE,Description VARCHAR(20),PatNum BIGINT,Fee DOUBLE,WriteOff DOUBLE,InsPayAmt DOUBLE,OutIns DOUBLE,Adjust DOUBLE,PatPaid DOUBLE,PatPort DOUBLE,ToBalance DOUBLE,RunningBal DOUBLE);
/*-- insert all completed procedures and any associated insurance claimprocs*/ INSERT INTO tmp1 SELECT * FROM tmp2;
UPDATE tmp2 SET RunningBal=(SELECT SUM(0+IFNULL(tmp1.ToBalance,0)) FROM tmp1 WHERE DATE(tmp1.ProcDate)<=DATE(tmp2.ProcDate) AND tmp1.PatNum=tmp2.PatNum AND tmp1.Description=' Total For Date')
WHERE tmp2.Description=' Total For Date';

/*-- Total by Patient, use fake date to indicate we want it blank later, must be large date to sort to bottom*/ INSERT INTO tmp2
SELECT '' AS FName, 'Total Ind' AS TranType, '3000-01-01' AS ProcDate, CONCAT(' Tot ', FName) AS Description, PatNum,
SUM(Fee) AS Fee, SUM(Writeoff) AS Writeoff,
SUM(InsPayAmt) AS InsPayAmt, SUM(OutIns) AS OutIns,SUM(Adjust) AS Adjust, SUM(PatPaid) AS PatPaid,
SUM(PatPort) AS PatPort, SUM(ToBalance) AS ToBalance, SUM(ToBalance) AS RunningBal
FROM tmp1 WHERE tmp1.Description=' Total For Date'
GROUP BY PatNum;
/*-- add Blank Row after each Patient in Family*/ INSERT INTO tmp2 SELECT '' AS FName, 'Total IndB' AS TranType, '3000-01-01' AS ProcDate, '' AS Description, PatNum, NULL AS Fee, NULL AS WriteOff,NULL AS InsPayAmt,NULL AS OutIns,NULL AS Adjust,NULL AS PatPaid,NULL AS PatPort,NULL AS ToBalance,NULL AS RunningBal
FROM tmp1 WHERE tmp1.Description=' Total For Date'
GROUP BY PatNum;
/*-- add family total*/
INSERT INTO tmp2
SELECT 'Family' AS FName, 'Total Fam' AS TranType, '3000-01-01' AS ProcDate, ' Total OverAll' AS Description, '999999999999' AS PatNum,
SUM(Fee) AS Fee, SUM(Writeoff) AS Writeoff,
SUM(InsPayAmt) AS InsPayAmt, SUM(OutIns) AS OutIns,SUM(Adjust) AS Adjust, SUM(PatPaid) AS PatPaid,
SUM(PatPort) AS PatPort, SUM(ToBalance) AS ToBalance, SUM(ToBalance) AS RunningBal
FROM tmp1 WHERE tmp1.Description=(' Total For Date');
/*-- now add position record to allow us to eliminate date repetition in detail version*/ CREATE TABLE tmp3 SELECT @pos:=@pos+1 AS RecNum,FName,TranType,ProcDate,Description,PatNum,Fee,WriteOff,InsPayAmt,OutIns,Adjust,PatPaid,PatPort,ToBalance,RunningBal
FROM tmp2
ORDER BY PatNum, ProcDate, TranType;
DROP TABLE IF EXISTS tmp2;
/*-- copy back to temp2, we will need two tables to avoid self reference during update*/ CREATE TABLE tmp2 SELECT * FROM tmp3;
/*-- Now choose which results to display, we add a column 'Detail' to allow a virtual if statement*/ ALTER TABLE tmp3 ADD COLUMN Detail BOOL; UPDATE tmp3 SET Detail=@Detail; UPDATE tmp3, tmp2 SET tmp3.ProcDate='3000-01-01' WHERE tmp3.RecNum=tmp2.RecNum+1 AND tmp3.ProcDate=tmp2.ProcDate AND Detail=True; DELETE FROM tmp3 WHERE TranType NOT LIKE 'Total%' AND Detail=False;
/*-- output results, hiding useless columns, doing some formatting*/ SELECT /*RecNum, */ FName AS Name, /*TranType,*/ (CASE WHEN ProcDate='3000-01-01' THEN '' ELSE DATE_FORMAT(ProcDate, '%m/%d/%Y') END) AS Date, Description, /*PatNum,*/
FORMAT(Fee,2) AS Fee,
FORMAT(WriteOff,2) AS WriteOff,
FORMAT(InsPayAmt,2) AS InsPaid,
FORMAT(OutIns,2) AS OutIns,
FORMAT(Adjust,2) AS Adjust,
FORMAT(PatPaid,2) AS PatPaid,
FORMAT(PatPort,2) AS PatPort,
(CASE WHEN TranType='Total Fam' THEN ' >'
WHEN TranType='Total Ind' THEN ' >'
ELSE FORMAT(ToBalance,2) END) AS ToBalance, (CASE WHEN TranType='Total Fam' THEN CONCAT('Tot: $', FORMAT((RunningBal),2)) WHEN TranType='Total Ind' THEN FORMAT(IFNULL(RunningBal,0),2) WHEN ISNULL(ToBalance) THEN NULL ELSE FORMAT(RunningBal,2) END) AS RunningBal FROM tmp3 ; DROP TABLE IF EXISTS tmp1, tmp2, tmp3;