Crystal Reports Filter by Most Recent Date of Field

crystal-reports

I have a report I am creating through an ODBC connection. The report includes several invoices, where each invoice has several products. There is also a table which contains all the historical price changes for each product (field: unit-price). Currently there are duplicate product records being pulled, one for each time there was a price change. Therefore, I need to filter my data so that only the most recent unit-price is shown (date field: effective-date). How can I do this via the "Select Expert?"

In short, show the product's unit-price for the most recent effective-date.

Thank you!

Best Answer

You'll need to create a sql-expression field to get the most-recent effective date, then use this field in the record-selection formula.

// {%MAX_EFFECTIVE_DATE}
// most-likely you'll need to alias the table in the main report for this to work
(
SELECT Max(effective_date)
FROM   price_history
WHERE  product_id = price_history_alias.product_id
)

Record-selection formula:

{price_history_alias.effective_date}={%MAX_EFFECTIVE_DATE}
Related Topic