Standard Report Syntax Question

This forum is for programmers who have questions about the source code.
Post Reply
baoluan1
Posts: 13
Joined: Wed Jan 19, 2022 8:37 am

Standard Report Syntax Question

Post by baoluan1 » Fri Feb 04, 2022 7:11 am

To Whom it May Concern,

I am constructing custom sql reports for our offices and have found my equations dont match those found in the standard monthly (day by day) Production and Income report. Particularly the 'Ins Income' numbers. I was wondering if I might be able to see the whole sql syntax used for that report or an explanation of how each of the (noncomputed) fields are pulled. Below is how I pull them and it never matches the report and I dont know why.

Ins Income:

Code: Select all

SET  @FromDate='2022-1-1' , @ToDate='2022-1-31';
SELECT SUM(InsPayAmt)
FROM claim
WHERE ClinicNum =1
AND DateReceived BETWEEN @FromDate AND @ToDate
AND InsPayAmt != 0;
This syntax gives a result about 6% off the summarized amount provided by the standard report for 'Ins Income'. Ive tried using different date fields (ie DateService, DateSent, DateReceived,SecDateEntry,DateSentOrig) just in case with no luck. I've also tried using +- 1 day intervals on the date window just incase. I've been unable to match the report so clearly I am missing computing some other field somewhere.

Any help you could provide would be greatly appreciated.

allends
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Standard Report Syntax Question

Post by allends » Fri Feb 04, 2022 8:23 am

I recommend using the Claimproc table instead of the Claim table.

Code: Select all

SET  @FromDate='2022-1-1' , @ToDate='2022-1-31';
SELECT SUM(InsPayAmt)
FROM claimproc
WHERE ClinicNum =1
AND DateReceived BETWEEN @FromDate AND @ToDate
AND InsPayAmt != 0;
That should get you the same insurance info used in the Production/Income report
Allen
Open Dental Software
http://www.opendental.com

baoluan1
Posts: 13
Joined: Wed Jan 19, 2022 8:37 am

Re: Standard Report Syntax Question

Post by baoluan1 » Fri Feb 04, 2022 10:24 am

That worked. Thank you. If you happen to know why getting patient payments strictly from the payments table isnt adding up right either I'll take one last asssit. If not no worries. I'll figure these tables out eventually.

Thanks again.

allends
Posts: 235
Joined: Fri Aug 23, 2013 11:29 am

Re: Standard Report Syntax Question

Post by allends » Fri Feb 04, 2022 10:40 am

You will want to look at paysplits instead of payments.
Total payment amounts should match total paysplit amounts, but there are times when that may not be the case (3rd parties, data corruption, old conversion data, etc.)
Allen
Open Dental Software
http://www.opendental.com

baoluan1
Posts: 13
Joined: Wed Jan 19, 2022 8:37 am

Re: Standard Report Syntax Question

Post by baoluan1 » Fri Feb 04, 2022 1:14 pm

Thank you. I really appreciate the insight.

Post Reply