Include a record based on two records 2

[cisscott]

Crystal Reports 9.1
SQL Server 2000 8.00.760 on W2K Adv Server

We have recently begun selling product over the internet. Some people chooose to have their purchase mailed to them, others come and pick it up. In the live.audit.dbo table there is a transaction type "H" for held, "R" for Reserved/Paid, and "X" for Cancelled. I designed a report that we run each morning that prints out each order that is status "R" and live.deliverymethod = 'MAIL'. However I have to also print out the 'X' type codes to be sure we don't mail to customers who purchased and then cancelled. So here's an example data set for orderid 851:

Orderid Type Charges datetime
851 H 0.00 2005-09-22 8:00:00am
851 R 21.95 2005-09-22 8:01:17am
851 X (21.95) 2005-09-22 8:32:46am

The question is How can I NOT include orderID 851 on the products to mail list based on the fact that one record is type R and a seperate record for the same order is type X?

 

[crystalabc]

I had seen similar situation in my database. This is what I did. It worked for me.
Connect the table to itself by Orderid (add the same table twice and rename them, say Table-R and Table-X). When you choosing the records, choose the ones with type "R" in Table-R but NOT type "X" in Table-X.

 

[lbass]

You could group on OrderID and then create a formula:

if {table.type} = "X" then 1

Then go to report->selection formula->GROUP and enter:

Sum({table.type},{table.OrderID}) = 0

If you could have a situation where a customer changes his/her mind and "uncancels", so that you need to test for the most recent transaction, then change the group selection formula to:

{table.date} = maximum({table.date},{table.orderID}) and
{table.type} <> "X"

-LB

 

[cisscott]

Excellent suggestions, thanks to both of you!