Standard Report Syntax Question

[baoluan1]

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]

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

[baoluan1]

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]

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.)

[baoluan1]

Thank you. I really appreciate the insight.