Selection query for last payment date

[GayleC]

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.

 

[Madawc]

I'm slightly surprised it does not work, but I can give you a simpler method.

Don't try writing your own SQL, let Crystal do it.

Group by type, whatever it is you use for "Example". Suppress the group header and the details.

Use Running Total to get Maximum Date for the group, but with a formula within the running total to select only where Payments <>0.

Running totals for dates normally return a blank if they find nothing. Alternatively you could write and display a formula field that tests IsNull and then maybe formats a non-null result. It could also say "Nothing Found" for null.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[GayleC]

Thanks, I'm using Crystal 11

I already have the Group Header & details surpressed and am summarizing all my data in the footer. The report is being used as a flat file of the total Accounts receivble with one line per account. I tried letting Crystal write the formula by using record selection but found that it was removing accounts with no payments, which is not what I want.

I will try the Running Totals solution, I have not used it in real world situations. I'm assuming the running totals will be run on the details that I am supressing. Correct?

 

[Madawc]

Yes. It would work on details you don't display at all.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[GayleC]

Thank you so much! This worked perfectly and was so much easier than what I was trying to do.