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
Code: Select all
-- Special Statement shows transactions grouped by procedure date, helps to understand a families account
SET @GPatNum='7018', @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),SUM(cp.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
-- 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
-- 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
/*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
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 SELECT * FROM tmp2;
UPDATE tmp2 SET RunningBal=FORMAT((SELECT SUM(tmp1.ToBalance) FROM tmp1 WHERE tmp1.ProcDate<=tmp2.ProcDate AND tmp1.PatNum=tmp2.PatNum AND tmp1.Description=' Total For Date'),2)
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, NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
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 ot 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;
