Open Dental Forum

I'd be interested to know what users are doing in place of "day sheets". By that I mean a report that shows all account activity for the day and correlates that to a running total of accounts receivable.
It would be a combination of the production, adjustment, and payment reports showing the beginning accounts receivable total (or the ending total from the previous day) and showing the ending accounts receivable totals after all the production, adjustments, and payments are posted for the day. This makes it easy to compare day to day to be sure that no entries are made "off the books" that would change the A/R totals from one day to the next. Looking at all the custom reports in the "query examples", it looks like it should be "doable" since all the figures are available on other reports but it would take someone who has a good grasp of the query language! If there's a simple work-around that gets the same information, that would be great, too! (The "day sheets" in the query examples appear to be limited to patients who have had treatment that day and wouldn't show payments received in the mail, if I read it right!).

That should be the daily P&I Report. The only reason I've heard of people using the other daily reports is for when the want more detail or different groupings. I believe there is an existing feature request to show A/R on the P&I reports.

You could try this, it corresponds with some other 'day sheet' expectations. IT gives you a little bit of trend and alot of information in one place. As it is it will automatically adjust to 'today'
Use it in the reports, User query... it may be the longest SQL query I have written so make sure you get it all.
-nathan


/* 291 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*/


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 and capitation writeoffs 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-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 recieved 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;

/*Now create report*/
CREATE TABLE tmpTotals
(PracticeTotals VARCHAR(30),
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:' 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';

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 'Prod Per Patient:' AS 'PracticeTotals',
FORMAT(((SELECT (SELECT REPLACE(Current,',','') FROM tmpTotals WHERE PracticeTotals='Charges:') /
(SELECT REPLACE(Current,',','') FROM tmpTotals WHERE PracticeTotals='Patients Seen:'))),2) Current,
FORMAT(((SELECT (SELECT REPLACE(MonthToDate,',','') FROM tmpTotals WHERE PracticeTotals='Charges:') /
(SELECT REPLACE(MonthToDate,',','') FROM tmpTotals WHERE PracticeTotals='Patients Seen:'))),2) MonthToDate,
FORMAT(((SELECT (SELECT REPLACE(YearToDate,',','') FROM tmpTotals WHERE PracticeTotals='Charges:') /
(SELECT REPLACE(YearToDate,',','') FROM tmpTotals WHERE PracticeTotals='Patients Seen:'))),2) YearToDate,
FORMAT(((SELECT (SELECT REPLACE(LastMonth,',','') FROM tmpTotals WHERE PracticeTotals='Charges:') /
(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;

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 and capitation writeoffs 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-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 recieved 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;

HOLY COMPUTERCODE, BATMAN!! Like I said...someone who has a good grasp of the query language! Might as well be Sanskrit for us mortals Hope you're getting paid REALLY well, Nathan! Keeping it all in the family, eh? Well done.

WOW!

I get an error 'Patient.PatNum' in 'on clause'

Are you sure you grabbed the entire query? I tested it last night when Nathan put it on and I just tested it again and the report works in our system.

yep I got the whole thing...tried it again...I do have a linux server...capitalization problem?

Nice work Nathan - This should become a standard report for OD.

Rick

I agree. That is a great report and should be included with the others. Nice work.

Jim

drtech wrote:yep I got the whole thing...tried it again...I do have a linux server...capitalization problem?

Yes, capitalization. Patient should be patient in the query.

Sorry, fixed capitalization in post above there were 5 places where I used Patient instead of patient, also published on our web site under query examples

Great work, Nathan, but what I'm suggesting is more like a combination of the daily P&I report + the daily Adjustment report with a beginning A/R balance and an ending A/R balance. Sound more like the feature request Jordan mentions for the P&I report, but also including the Adjustment report. Showing each entry for charges, payment, adjustments by patient name with the beginning A/R and ending A/R all in one daily report. The ending balance of yesterday would equal the beginning balance of today unless there were entries made AFTER yesterday's report closed, making it easy to see if any "off the books" entries were made that should not have been.

I still think you could verify that in a number of other ways. For example, if you had a printout from yesterday, just look at the P&I numbers. If they still match, then nobody has made a change. You really are going to have to adapt somewhat to the software. What's wrong with comparing the printout?

Oh, and yes, Nathan, that was a very impressive query. I forgot to say so earlier.

Thanks for your help, Jordan. I'm planning on switching to OD from Patient Base, and I'm trying to sort out all the things we use now and how we would get the same information on OD. The ability to modify existing reports is interesting to me, but I'll have to spend more time learning the query language. How would I find the query language for the P&I and ADJ reports?
From what I can see so far, your system is very versatile and your virtually immediate replies show a tremendous commitment! Thanks for all your information. I'm sure all your members are very grateful for your time!

You realize that adjustments are part of the P&I report, right? I keep hearing you mention adjustments as if they were somehow separate.

Thanks...again, Jordan. Clearly I haven't been looking at the reports right! The P&I report shows ALL the data i'm looking for and the fact that it can be pulled up at a later date to check against a printed copy is all the info I need. I think adding the a/r numbers to the P&I report is still a good idea, though. This is the kind of support you just can't get from the "other guys"

I really like the info on this report...but I am not sure it is entirely accurate. On my Patients Seen column my numbers are MTD:296, Last Month:306 and YTD 529. The YTD should = MTD + LASTMonth since this is Feb. (which would be 602, not 529)

Found a problem with under-counting of new patients for month-to-date and year-to-date. In the 2 places where this string occurs: MIN(ProcDate<=@AsOf) replace it with this one: MIN(ProcDate)<=@AsOf
The parenthesis must be moved in each case because the MIN function needs to be applied to the patient ProcDate only, not to the whole date comparison. As for the total of YTD patients seen, it is working properly, because it's counting distinct patients. Someone may have come in January for a cleaning and exam, then had a filling done in February; the query avoids counting such a person twice. Seems to be quite a nice summary, thanks for the work creating it!

thanks, fixed, updated published query at website and the one published above

Is there a P and I report that breaks down everything by provider (grouping and totaling by provider) and then giving a grand total?

brmsm wrote:Is there a P and I report that breaks down everything by provider (grouping and totaling by provider) and then giving a grand total?

Bump for today

Kudos Nathan. Excellent query.

Nice nathan...excellent