I am trying to create a report that selects the last date that a non-zero payment was posted against an account. If there was no payment, the result should be a blank field. The date is listed in a batch table which is linked back to the account through several tables. I’ve created the following SQL expression; however it does not always pull the correct information.
(
Select MAX("Batch"."Entry")
From Batch
Where "PatientVisit"."PatientVisitId"="VisitTransactions"."PatientVisitid" and
"VisitTransactions"."PaymentMethodId"="PaymentMethod"."PaymentMethodId" and
"PaymentMethod"."BatchId"="Batch"."BatchId" and "VisitTransactions"."Payments" <>0
)
Example 1: Payment dates are as follows:
6/23/10 - $20
8/3/10 - $64
7/6/11 - $0
Result of the formula should pull 8/3/10
Example 2: Payment dates are as follows:
8/28/10 - $0
Result of the formula should be a blank
Example 3: Payment dates are as follows:
8/5/10 - $0
10/17/10 - $91
10/5/11 - $-20
10/10/11 - $48
Result of the formula should be 10/10/11
The formula above is working for Example 2 & 3, but for Example 1 returns a blank. Also, I do not want the detail on the report and have the formula placed in the Group Footer, but have found that unless the data is ordered in descending order it will select whatever is either at the bottom of the list and not the MAX date. I’ve resolved this by placing the detail on the report, sorting it and then hiding the detail.
(
Select MAX("Batch"."Entry")
From Batch
Where "PatientVisit"."PatientVisitId"="VisitTransactions"."PatientVisitid" and
"VisitTransactions"."PaymentMethodId"="PaymentMethod"."PaymentMethodId" and
"PaymentMethod"."BatchId"="Batch"."BatchId" and "VisitTransactions"."Payments" <>0
)
Example 1: Payment dates are as follows:
6/23/10 - $20
8/3/10 - $64
7/6/11 - $0
Result of the formula should pull 8/3/10
Example 2: Payment dates are as follows:
8/28/10 - $0
Result of the formula should be a blank
Example 3: Payment dates are as follows:
8/5/10 - $0
10/17/10 - $91
10/5/11 - $-20
10/10/11 - $48
Result of the formula should be 10/10/11
The formula above is working for Example 2 & 3, but for Example 1 returns a blank. Also, I do not want the detail on the report and have the formula placed in the Group Footer, but have found that unless the data is ordered in descending order it will select whatever is either at the bottom of the list and not the MAX date. I’ve resolved this by placing the detail on the report, sorting it and then hiding the detail.