Code: Select all
/*514 Daily Report.*/
/*Calculate current or historical accounts receivable, collectible, outstanding insurance estimates. Note that when compared to an aging report, the ins estimate includes ins FROM accounts with both positive and negative balances, ALSO now shows Date of Service (DOS) PPO writeoffs as line item*/
/*WARNING: On versions before 14.3.1 that are using replication, do not run this query from multiple computers at the same time or replication will crash.*/
SET @AsOf=CURDATE(); /*use this instead to show any day SET @AsOf='2009-05-20'; */
/*first create transaction table*/
DROP TABLE IF EXISTS tmp1, tmp2, tmp3, tmp4, tmpTotals;
CREATE TABLE tmp1
(TranType VARCHAR(10), PatNum INT DEFAULT 0,
TranDate DATE DEFAULT '0001-01-01',
TranAmount DOUBLE DEFAULT 0);
/*Get the completed procedure dates and charges for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'Fee' AS TranType, pl.PatNum PatNum,pl.ProcDate TranDate,pl.ProcFee*(pl.UnitQty+pl.BaseUnits) TranAmount
FROM procedurelog pl
WHERE pl.ProcStatus=2;
/*Paysplits for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'Pay' AS TranType,ps.PatNum PatNum,ps.ProcDate TranDate,-ps.SplitAmt TranAmount
FROM paysplit ps
WHERE ps.PayPlanNum=0 /*Only splits not attached to payment plans*/;
/*Get the adjustment dates and amounts for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'Adj' AS TranType, a.PatNum PatNum,a.AdjDate TranDate,a.AdjAmt TranAmount
FROM adjustment a;
/*Claim payments for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'InsPay' AS TranType,cp.PatNum PatNum,
cp.DateCp TranDate,-cp.InsPayAmt TranAmount
FROM claimproc cp
WHERE cp.Status IN (1,4,5,7)/*received, supplemental, CapClaim or CapComplete*/;
/*Writeoffs for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'WriteOff' AS TranType,cp.PatNum PatNum,
cp.DateCp TranDate,-cp.Writeoff TranAmount
FROM claimproc cp
WHERE cp.Status IN (1,4,5,7)/*received, supplemental, CapClaim or CapComplete*/;
/*DOS Writeoffs for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'DOSWO' AS TranType,cp.PatNum PatNum,
cp.ProcDate TranDate,-cp.Writeoff TranAmount
FROM claimproc cp
WHERE cp.Status IN (1,4,5,7)/*received, supplemental, CapClaim or CapComplete*/;
/*DOS Writeoff estimates for the entire office history on or before the given date
only count for claims of status sent or waiting to send, hold or unsent*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'DOSWOEst' AS TranType,cp.PatNum PatNum,
cp.ProcDate TranDate,
-cp.Writeoff TranAmount
FROM claimproc cp
INNER JOIN claim cl ON cl.ClaimNum=cp.ClaimNum
WHERE cp.Status IN (0)/*not recieved*/
AND cl.ClaimStatus IN ("U","H","W","S");
/*Payment plan principal for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'PayPlan' AS TranType,pp.PatNum PatNum,
pp.PayPlanDate TranDate,
-pp.CompletedAmt TranAmount
FROM payplan pp;
/*now we need to include the anticipated insurance (sum of payments and writeoffs)
note that if you use PPO type, your anticipated writeoff numbers will have updated to
match your actual writeoff numbers... your best bet is to keep backups or paper copies of your
reports if that change is bothersome */
CREATE TABLE tmp2
SELECT Guarantor, SUM(TranAmount) AS 'FamBal' FROM patient
INNER JOIN tmp1 ON tmp1.PatNum=patient.PatNum
WHERE TranDate<=@AsOf AND TranType NOT LIKE('DOS%')
GROUP BY Guarantor;
/*Note that as we get these numbers we are summing by family, when really we
are looking for a grand total, this is to make it easier to get the details should we need them
FROM the tmp tables*/
CREATE TABLE tmp3 SELECT p.Guarantor,SUM(cp.InsPayEst) InsPayEst,
(CASE WHEN ISNULL(SUM(cp.Writeoff)) THEN 0 ELSE SUM(cp.WriteOff) END) WriteOff
FROM patient p INNER JOIN claimproc cp ON cp.PatNum=p.PatNum
WHERE ((cp.Status=0 AND cp.ProcDate<=@AsOf) OR /* still not received and procedure completed before the date*/
(cp.Status IN(1,4) AND cp.DateCP>@AsOf AND cp.ProcDate<=@AsOf)) /*was received after the date of interest and completed before it*/
GROUP BY p.Guarantor;
/*Now create report*/
CREATE TABLE tmpTotals
(PracticeTotals VARCHAR(45),
Current VARCHAR(20),
MonthToDate VARCHAR(20),
YearToDate VARCHAR(20),
LastMonth VARCHAR(20));
/*Procedure Charges - Gross Production*/
INSERT INTO tmpTotals SELECT '*Production*',@AsOf,'','','';
INSERT INTO tmpTotals SELECT 'Charges, Gross Production:' AS 'PracticeTotals',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='Fee' AND TranDate=@AsOf),2) AS 'Current',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='Fee'
AND MONTH(TranDate)=MONTH(@AsOf) AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'MonthToDate',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='Fee'
AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'YearToDate',
(CASE WHEN (MONTH(@AsOf)=1) THEN (FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='Fee'
AND MONTH(TranDate)=12 AND YEAR(TranDate)=(YEAR(@AsOf)-1)),2))
ELSE
(FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='Fee'
AND MONTH(TranDate)=(MONTH(@AsOf)-1) AND YEAR(TranDate)=YEAR(@AsOf)),2))
END) AS 'LastMonth';
/*Credit Adjustments:*/
INSERT INTO tmpTotals SELECT 'Credit Adjustments:' AS 'PracticeTotals',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranAmount<0 AND TranType='Adj' AND TranDate=@AsOf),2) AS 'Current',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranAmount<0 AND TranType='Adj'
AND MONTH(TranDate)=MONTH(@AsOf) AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'MonthToDate',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranAmount<0 AND TranType='Adj'
AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'YearToDate',
(CASE WHEN (MONTH(@AsOf)=1) THEN (FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranAmount<0 AND TranType='Adj'
AND MONTH(TranDate)=12 AND YEAR(TranDate)=(YEAR(@AsOf)-1)),2))
ELSE
(FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranAmount<0 AND TranType='Adj'
AND MONTH(TranDate)=(MONTH(@AsOf)-1) AND YEAR(TranDate)=YEAR(@AsOf)),2))
END) AS 'LastMonth';
/*Charge Adjustments:*/
INSERT INTO tmpTotals SELECT 'Charge Adjustments:' AS 'PracticeTotals',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranAmount>0 AND TranType='Adj' AND TranDate=@AsOf),2) AS 'Current',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranAmount>0 AND TranType='Adj'
AND MONTH(TranDate)=MONTH(@AsOf) AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'MonthToDate',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranAmount>0 AND TranType='Adj'
AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'YearToDate',
(CASE WHEN (MONTH(@AsOf)=1) THEN (FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranAmount>0 AND TranType='Adj'
AND MONTH(TranDate)=12 AND YEAR(TranDate)=(YEAR(@AsOf)-1)),2))
ELSE
(FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranAmount>0 AND TranType='Adj'
AND MONTH(TranDate)=(MONTH(@AsOf)-1) AND YEAR(TranDate)=YEAR(@AsOf)),2))
END) AS 'LastMonth';
/*Production Subtotals*/
INSERT INTO tmpTotals SELECT 'Production after Adjustments:' AS 'PracticeTotals',
FORMAT((SELECT SUM(REPLACE(Current,',','')) FROM tmpTotals WHERE PracticeTotals IN('Charges, Gross Production:','Credit Adjustments:','Charge Adjustments:')),2) AS 'Current',
FORMAT((SELECT SUM(REPLACE(MonthToDate,',','')) FROM tmpTotals WHERE PracticeTotals IN('Charges, Gross Production:','Credit Adjustments:','Charge Adjustments:')),2) AS 'MonthToDate',
FORMAT((SELECT SUM(REPLACE(YearToDate,',','')) FROM tmpTotals WHERE PracticeTotals IN('Charges, Gross Production:','Credit Adjustments:','Charge Adjustments:')),2) AS 'YearToDate',
FORMAT((SELECT SUM(REPLACE(LastMonth,',','')) FROM tmpTotals WHERE PracticeTotals IN('Charges, Gross Production:','Credit Adjustments:','Charge Adjustments:')),2) AS 'LastMonth';
INSERT INTO tmpTotals SELECT '*Prod: (Ins Writeoffs by DateOfPayment)*','','','','';
/* Insurance Writeoffs, by date of payment:*/
INSERT INTO tmpTotals SELECT 'Ins Writeoffs (DOP):' AS 'PracticeTotals',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='WriteOff' AND TranDate=@AsOf),2) AS 'Current',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='WriteOff'
AND MONTH(TranDate)=MONTH(@AsOf) AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'MonthToDate',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='WriteOff'
AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'YearToDate',
(CASE WHEN (MONTH(@AsOf)=1) THEN (FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='WriteOff'
AND MONTH(TranDate)=12 AND YEAR(TranDate)=(YEAR(@AsOf)-1)),2))
ELSE
(FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='WriteOff'
AND MONTH(TranDate)=(MONTH(@AsOf)-1) AND YEAR(TranDate)=YEAR(@AsOf)),2))
END) AS 'LastMonth';
/*Production Totals*/
INSERT INTO tmpTotals SELECT 'Net Production (writeoffs by payment date:' AS 'PracticeTotals',
FORMAT((SELECT SUM(REPLACE(Current,',','')) FROM tmpTotals WHERE PracticeTotals IN('Production after Adjustments:','Ins Writeoffs (DOP):')),2) AS 'Current',
FORMAT((SELECT SUM(REPLACE(MonthToDate,',','')) FROM tmpTotals WHERE PracticeTotals IN('Production after Adjustments:','Ins Writeoffs (DOP):')),2) AS 'MonthToDate',
FORMAT((SELECT SUM(REPLACE(YearToDate,',','')) FROM tmpTotals WHERE PracticeTotals IN('Production after Adjustments:','Ins Writeoffs (DOP):')),2) AS 'YearToDate',
FORMAT((SELECT SUM(REPLACE(LastMonth,',','')) FROM tmpTotals WHERE PracticeTotals IN('Production after Adjustments:','Ins Writeoffs (DOP):')),2) AS 'LastMonth';
INSERT INTO tmpTotals SELECT '*Prod: (Ins Writeoffs by DateOfService)*','','','','';
/* Insurance writeoffs by Date of Service, including estimated writeoffs*/
INSERT INTO tmpTotals SELECT 'Ins Writeoffs (by DOS, including est):' AS 'PracticeTotals',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType IN('DOSWO','DOSWOEst') AND TranDate=@AsOf),2) AS 'Current',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType IN('DOSWO','DOSWOEst')
AND MONTH(TranDate)=MONTH(@AsOf) AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'MonthToDate',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType IN('DOSWO','DOSWOEst')
AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'YearToDate',
(CASE WHEN (MONTH(@AsOf)=1) THEN (FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType IN('DOSWO','DOSWOEst')
AND MONTH(TranDate)=12 AND YEAR(TranDate)=(YEAR(@AsOf)-1)),2))
ELSE
(FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType IN('DOSWO','DOSWOEst')
AND MONTH(TranDate)=(MONTH(@AsOf)-1) AND YEAR(TranDate)=YEAR(@AsOf)),2))
END) AS 'LastMonth';
/*Production Totals DOS*/
INSERT INTO tmpTotals SELECT 'Net Production (writeoffs by DoS, inc est' AS 'PracticeTotals',
FORMAT((SELECT SUM(REPLACE(Current,',','')) FROM tmpTotals WHERE PracticeTotals IN('Production after Adjustments:','Ins Writeoffs (by DOS, including est):')),2) AS 'Current',
FORMAT((SELECT SUM(REPLACE(MonthToDate,',','')) FROM tmpTotals WHERE PracticeTotals IN('Production after Adjustments:','Ins Writeoffs (by DOS, including est):')),2) AS 'MonthToDate',
FORMAT((SELECT SUM(REPLACE(YearToDate,',','')) FROM tmpTotals WHERE PracticeTotals IN('Production after Adjustments:','Ins Writeoffs (by DOS, including est):')),2) AS 'YearToDate',
FORMAT((SELECT SUM(REPLACE(LastMonth,',','')) FROM tmpTotals WHERE PracticeTotals IN('Production after Adjustments:','Ins Writeoffs (by DOS, including est):')),2) AS 'LastMonth';
INSERT INTO tmpTotals SELECT '','','','',''; /* BLANK LINE */
INSERT INTO tmpTotals SELECT '*Income*','','','','';
/*Pat Pay*/
INSERT INTO tmpTotals SELECT 'Patient Payments:' AS 'PracticeTotals',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='Pay' AND TranDate=@AsOf),2) AS 'Current',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='Pay'
AND MONTH(TranDate)=MONTH(@AsOf) AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'MonthToDate',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='Pay'
AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'YearToDate',
(CASE WHEN (MONTH(@AsOf)=1) THEN (FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='Pay'
AND MONTH(TranDate)=12 AND YEAR(TranDate)=(YEAR(@AsOf)-1)),2))
ELSE
(FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='Pay'
AND MONTH(TranDate)=(MONTH(@AsOf)-1) AND YEAR(TranDate)=YEAR(@AsOf)),2))
END) AS 'LastMonth';
/*Ins Pay*/
INSERT INTO tmpTotals SELECT 'Ins Payments:' AS 'PracticeTotals',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='InsPay' AND TranDate=@AsOf),2) AS 'Current',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='InsPay'
AND MONTH(TranDate)=MONTH(@AsOf) AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'MonthToDate',
FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='InsPay'
AND YEAR(TranDate)=YEAR(@AsOf) AND TranDate<=@AsOf),2) AS 'YearToDate',
(CASE WHEN (MONTH(@AsOf)=1) THEN (FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='InsPay'
AND MONTH(TranDate)=12 AND YEAR(TranDate)=(YEAR(@AsOf)-1)),2))
ELSE
(FORMAT((SELECT SUM(TranAmount) FROM tmp1 WHERE TranType='InsPay'
AND MONTH(TranDate)=(MONTH(@AsOf)-1) AND YEAR(TranDate)=YEAR(@AsOf)),2))
END) AS 'LastMonth';
/*Total Income*/
INSERT INTO tmpTotals SELECT 'Total Payments:' AS 'PracticeTotals',
FORMAT((SELECT SUM(REPLACE(Current,',','')) FROM tmpTotals WHERE PracticeTotals IN('Ins Payments:','Patient Payments:')),2) AS 'Current',
FORMAT((SELECT SUM(REPLACE(MonthToDate,',','')) FROM tmpTotals WHERE PracticeTotals IN('Ins Payments:','Patient Payments:')),2) AS 'MonthToDate',
FORMAT((SELECT SUM(REPLACE(YearToDate,',','')) FROM tmpTotals WHERE PracticeTotals IN('Ins Payments:','Patient Payments:')),2) AS 'YearToDate',
FORMAT((SELECT SUM(REPLACE(LastMonth,',','')) FROM tmpTotals WHERE PracticeTotals IN('Ins Payments:','Patient Payments:')),2) AS 'LastMonth';
/*Now get rid of blanks*/
UPDATE tmpTotals SET Current='0.00' WHERE ISNULL(Current);
UPDATE tmpTotals SET MonthToDate='0.00' WHERE ISNULL(MonthToDate);
UPDATE tmpTotals SET YearToDate='0.00' WHERE ISNULL(YearToDate);
UPDATE tmpTotals SET LastMonth='0.00' WHERE ISNULL(LastMonth);
/*New Patients*/
INSERT INTO tmpTotals SELECT '','','','','';
/* Old Way, by DateFirstVisit INSERT INTO tmpTotals SELECT 'New Patients Seen:' AS 'PracticeTotals',
(SELECT COUNT(*) FROM patient WHERE DateFirstVisit=@AsOf AND PatStatus=0) Current,
(SELECT COUNT(*) FROM patient WHERE MONTH(DateFirstVisit)=MONTH(@AsOf) AND
YEAR(DateFirstVisit)=YEAR(@AsOf) AND PatStatus=0) MonthToDate,
(SELECT COUNT(*) FROM patient WHERE YEAR(DateFirstVisit)=YEAR(@AsOf) AND PatStatus=0) YearToDate,
(CASE WHEN (MONTH(@AsOf)=1) THEN (SELECT COUNT(*) FROM patient
WHERE MONTH(DateFirstVisit)=12 AND YEAR(DateFirstVisit)=(YEAR(DateFirstVisit)-1) AND PatStatus=0)
ELSE (SELECT COUNT(*) FROM patient
WHERE MONTH(DateFirstVisit)=(MONTH(@AsOf)-1) AND YEAR(DateFirstVisit)=YEAR(@AsOf) AND PatStatus=0)
END) AS LastMonth; */
INSERT INTO tmpTotals SELECT 'New Patients Seen:' AS 'PracticeTotals',
(SELECT COUNT(*) FROM (SELECT PatNum FROM procedurelog WHERE ProcStatus=2 GROUP BY PatNum HAVING DATE(MIN(ProcDate))=@AsOf) a) Current,
(SELECT COUNT(*) FROM (SELECT PatNum FROM procedurelog WHERE ProcStatus=2 GROUP BY PatNum
HAVING (MONTH(MIN(ProcDate))=MONTH(@AsOf) AND YEAR(MIN(ProcDate))=YEAR(@AsOf) AND MIN(ProcDate)<=@AsOf)) a) MonthToDate,
(SELECT COUNT(*) FROM (SELECT PatNum FROM procedurelog WHERE ProcStatus=2 GROUP BY PatNum
HAVING YEAR(MIN(ProcDate))=YEAR(@AsOf) AND MIN(ProcDate)<=@AsOf) a) YearToDate,
(CASE WHEN (MONTH(@AsOf)=1) THEN
(SELECT COUNT(*) FROM (SELECT PatNum FROM procedurelog WHERE ProcStatus=2 GROUP BY PatNum
HAVING (MONTH(MIN(ProcDate))=12 AND YEAR(MIN(ProcDate))=(YEAR(@AsOf)-1))) a)
ELSE (SELECT COUNT(*) FROM (SELECT PatNum FROM procedurelog WHERE ProcStatus=2 GROUP BY PatNum
HAVING (MONTH(MIN(ProcDate))=(MONTH(@AsOf)-1) AND YEAR(MIN(ProcDate))=YEAR(@AsOf))) a)
END) AS LastMonth;
/*INSERT INTO tmpTotals SELECT 'Appointments:' AS 'PracticeTotals', '','','',''; Add this later */
INSERT INTO tmpTotals SELECT 'Patients Seen:' AS 'PracticeTotals',
(SELECT COUNT(DISTINCT PatNum) FROM procedurelog WHERE ProcDate=@AsOf AND ProcStatus=2) Current,
(SELECT COUNT(DISTINCT PatNum) FROM procedurelog WHERE MONTH(ProcDate)=MONTH(@AsOf) AND
YEAR(ProcDate)=YEAR(@AsOf) AND ProcStatus=2 AND ProcDate<=@AsOf) MonthToDate,
(SELECT COUNT(DISTINCT PatNum) FROM procedurelog WHERE YEAR(ProcDate)=YEAR(@AsOf)AND ProcStatus=2 AND ProcDate<=@AsOf) YearToDate,
(CASE WHEN (MONTH(@AsOf)=1) THEN (SELECT COUNT(DISTINCT PatNum) FROM procedurelog
WHERE MONTH(ProcDate)=12 AND YEAR(ProcDate)=(YEAR(ProcDate)-1) AND ProcStatus=2)
ELSE (SELECT COUNT(DISTINCT PatNum) FROM procedurelog
WHERE MONTH(ProcDate)=(MONTH(@AsOf)-1) AND YEAR(ProcDate)=YEAR(@AsOf) AND ProcStatus=2)
END) AS LastMonth;
INSERT INTO tmpTotals SELECT 'Gross Prod Per Patient:' AS 'PracticeTotals',
FORMAT(((SELECT (SELECT REPLACE(Current,',','') FROM tmpTotals WHERE PracticeTotals='Charges, Gross Production:') /
(SELECT REPLACE(Current,',','') FROM tmpTotals WHERE PracticeTotals='Patients Seen:'))),2) Current,
FORMAT(((SELECT (SELECT REPLACE(MonthToDate,',','') FROM tmpTotals WHERE PracticeTotals='Charges, Gross Production:') /
(SELECT REPLACE(MonthToDate,',','') FROM tmpTotals WHERE PracticeTotals='Patients Seen:'))),2) MonthToDate,
FORMAT(((SELECT (SELECT REPLACE(YearToDate,',','') FROM tmpTotals WHERE PracticeTotals='Charges, Gross Production:') /
(SELECT REPLACE(YearToDate,',','') FROM tmpTotals WHERE PracticeTotals='Patients Seen:'))),2) YearToDate,
FORMAT(((SELECT (SELECT REPLACE(LastMonth,',','') FROM tmpTotals WHERE PracticeTotals='Charges, Gross Production:') /
(SELECT REPLACE(LastMonth,',','') FROM tmpTotals WHERE PracticeTotals='Patients Seen:'))),2) LastMonth;
INSERT INTO tmpTotals SELECT '','','','','';
INSERT INTO tmpTotals SELECT 'Tot Ins Portion Est:' AS PracticeTotals,
FORMAT(SUM(InsPayEst),2) AS Current, '','','' FROM tmp3; /*this is the sum of writeoffs that are part of the patient balance*/
INSERT INTO tmpTotals SELECT 'Tot WriteOff Est:' AS PracticeTotals,
FORMAT(SUM(WriteOff),2) AS Current, '','','' FROM tmp3;
INSERT INTO tmpTotals SELECT 'Tot Patient Portion Est:' AS PracticeTotals,
FORMAT(SUM(tmp2.FamBal)-SUM(tmp3.InsPayEst)-SUM(tmp3.WriteOff),2) AS Current, '','',''
FROM tmp2 LEFT JOIN tmp3 ON tmp2.Guarantor=tmp3.Guarantor;
INSERT INTO tmpTotals SELECT 'Accounts Receivable:' AS PracticeTotals,
FORMAT(SUM(FamBal),2) AS Current, '','','' FROM tmp2 WHERE FamBal>0;
INSERT INTO tmpTotals SELECT 'Accounts Payable:' AS 'PracticeTotals',
FORMAT(SUM(FamBal),2) AS Current, '','','' FROM tmp2 WHERE FamBal<0;
INSERT INTO tmpTotals SELECT 'Tot Practice Balance:' AS PracticeTotals,
FORMAT(SUM(FamBal),2) AS Current, '','','' FROM tmp2;
/*now do previous days balance*/
SET @AsOf=@AsOf-Interval 1 day; /*use this instead to always show today SET @AsOf=CURDATE(); */
/*first create transaction table*/
DROP TABLE IF EXISTS tmp1, tmp2, tmp3, tmp4;
CREATE TABLE tmp1
(TranType VARCHAR(10), PatNum INT DEFAULT 0,
TranDate DATE DEFAULT '0001-01-01',
TranAmount DOUBLE DEFAULT 0);
/*Get the completed procedure dates and charges for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'Fee' AS TranType, pl.PatNum PatNum,pl.ProcDate TranDate,pl.ProcFee*(pl.UnitQty+pl.BaseUnits) TranAmount
FROM procedurelog pl
WHERE pl.ProcStatus=2;
/*Paysplits for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'Pay' AS TranType,ps.PatNum PatNum,ps.ProcDate TranDate,-ps.SplitAmt TranAmount
FROM paysplit ps
WHERE ps.PayPlanNum=0 /*Only splits not attached to payment plans*/;
/*Get the adjustment dates and amounts for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'Adj' AS TranType, a.PatNum PatNum,a.AdjDate TranDate,a.AdjAmt TranAmount
FROM adjustment a;
/*Claim payments for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'InsPay' AS TranType,cp.PatNum PatNum,
cp.DateCp TranDate,-cp.InsPayAmt TranAmount
FROM claimproc cp
WHERE cp.Status IN (1,4,5,7);/*received, supplemental, CapClaim or CapComplete*/
/*Writeoffs for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'WriteOff' AS TranType,cp.PatNum PatNum,
cp.DateCp TranDate,-cp.Writeoff TranAmount
FROM claimproc cp
WHERE cp.Status IN (1,4,5,7);/*received, supplemental, CapClaim or CapComplete*/
/*Payment plan principal for the entire office history on or before the given date*/
INSERT INTO tmp1 (TranType,PatNum,TranDate,TranAmount)
SELECT 'PayPlan' AS TranType,pp.PatNum PatNum,
pp.PayPlanDate TranDate,
-pp.CompletedAmt TranAmount
FROM payplan pp;
/*now we need to include the anticipated insurance (sum of payments and writeoffs)
note that if you use PPO type, your anticipated writeoff numbers will have updated to
match your actual writeoff numbers... your best bet is to keep backups or paper copies of your
reports if that change is bothersome */
CREATE TABLE tmp2
SELECT Guarantor, SUM(TranAmount) AS 'FamBal' FROM patient
INNER JOIN tmp1 ON tmp1.PatNum=patient.PatNum
WHERE TranDate<=@AsOf
GROUP BY Guarantor;
/*Note that as we get these numbers we are summing by family, when really we
are looking for a grand total, this is to make it easier to get the details should we need them
FROM the tmp tables*/
CREATE TABLE tmp3 SELECT p.Guarantor,SUM(cp.InsPayEst) InsPayEst,
(CASE WHEN ISNULL(SUM(cp.Writeoff)) THEN 0 ELSE SUM(cp.WriteOff) END) WriteOff
FROM patient p INNER JOIN claimproc cp ON cp.PatNum=p.PatNum
WHERE ((cp.Status=0 AND cp.ProcDate<=@AsOf) OR /* still not received and procedure completed before the date*/
(cp.Status IN(1,4) AND cp.DateCP>@AsOf AND cp.ProcDate<=@AsOf)) /*was received after the date of interes and completed before it*/
GROUP BY p.Guarantor;
INSERT INTO tmpTotals SELECT 'Previous Day Pract. Bal.:' AS PracticeTotals,
FORMAT(SUM(FamBal),2) AS Current, '','','' FROM tmp2;
/*compute change*/
INSERT INTO tmpTotals SELECT 'Net Change:' AS PracticeTotals,
FORMAT((SELECT REPLACE(Current,',','') FROM tmpTotals WHERE PracticeTotals='Tot Practice Balance:')-
(SELECT REPLACE(Current,',','') FROM tmpTotals WHERE PracticeTotals='Previous Day Pract. Bal.:'),2) AS 'Current',
'','','';
/*Display*/
SELECT * FROM tmpTotals;
DROP TABLE IF EXISTS tmp1, tmp2, tmp3, tmp4, tmpTotals;