Another date selection question 1

[cisscott]

CR 9.1
SQL Server 2K

I have an audit table that stores a record including the date and time each time something changes in an order. I need to be able to pull only the record containing the very first entry for an order (the record that was created when the sale was made). This seems liek it should be simple, but it escapes me. Thanks.

 

[lbass]

You could do one of the following:

Group on {audit.orderID} and then go to edit selection formula->GROUP and enter:

{audit.date} = minimum({audit.date},{audit.OrderID})

Or you might be able to create a SQL expression as follows:

(select min(AKA.`date`) from audit AKA where
AKA.`OrderID` = audit.`OrderID`)

You would substitute your exact table name for "audit" wherever you see it and the exact field names for "date" and "OrderId". Leave "AKA" as is, since it is an alias table name. The advantage to the second solution is that you only this record would be returned to your report, while in the first solution all records would be returned, so that you would need to use running totals for some calcultions instead of inserting summaries.

-LB

 

[cisscott]

thanks lbass.... the second solution is the one I needed. I wasn't aware that the minimum function would work for a date field. I had only used it in integer and currency fields before. THANKS !!