Query to see complex accounts more clearly

For users or potential users.
Post Reply
nathansparks
Posts: 172
Joined: Mon Aug 04, 2008 12:39 pm

Query to see complex accounts more clearly

Post by nathansparks » Wed Apr 07, 2010 5:36 pm

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

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;
Last edited by nathansparks on Thu Apr 08, 2010 5:29 pm, edited 1 time in total.

User avatar
jordansparks
Site Admin
Posts: 5755
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Query to see complex accounts more clearly

Post by jordansparks » Thu Apr 08, 2010 1:33 pm

Wow.
Jordan Sparks, DMD
http://www.opendental.com

User avatar
drtech
Posts: 1651
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO
Contact:

Re: Query to see complex accounts more clearly

Post by drtech » Thu Apr 08, 2010 2:54 pm

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!
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com

User avatar
jordansparks
Site Admin
Posts: 5755
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Re: Query to see complex accounts more clearly

Post by jordansparks » Thu Apr 08, 2010 3:30 pm

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?
Jordan Sparks, DMD
http://www.opendental.com

nathansparks
Posts: 172
Joined: Mon Aug 04, 2008 12:39 pm

Re: Query to see complex accounts more clearly

Post by nathansparks » Thu Apr 08, 2010 5:30 pm

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

User avatar
drtech
Posts: 1651
Joined: Wed Jun 20, 2007 8:44 am
Location: Springfield, MO
Contact:

Re: Query to see complex accounts more clearly

Post by drtech » Thu Apr 08, 2010 6:19 pm

Jordan...no I was not aware of that...prob should do that... but then who would find the Linux server bugs! :P
David Fuchs
Dentist - Springfield, MO
Smile Dental http://www.887-smile.com

teamhowey
Posts: 39
Joined: Fri Apr 09, 2010 12:44 pm

Re: Query to see complex accounts more clearly

Post by teamhowey » Thu Dec 22, 2011 11:36 am

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?

User avatar
irfan
Posts: 216
Joined: Thu Oct 21, 2010 9:09 am

Re: Query to see complex accounts more clearly

Post by irfan » Thu Dec 22, 2011 6:14 pm

oh thaaaats niiice.

User avatar
Hersheydmd
Posts: 703
Joined: Sun May 03, 2009 9:12 pm

Re: Query to see complex accounts more clearly

Post by Hersheydmd » Fri Dec 23, 2011 3:51 pm

Awesome, really!!!
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

User avatar
Hersheydmd
Posts: 703
Joined: Sun May 03, 2009 9:12 pm

Re: Query to see complex accounts more clearly

Post by Hersheydmd » Tue Dec 27, 2011 4:37 pm

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;
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

Post Reply