Join tables by closest date

[koolskins]

Crystal 10 against an Informix database...
I have an inventory transaction table and a work order status table where I need to show the work order status at the time of the inventory transaction. This won't necessarily be a matching date as the work orders are statused totally separate from the inventory transactions. What is the syntax to select the appropriate work order status that was active at the time of the inventory transaction? Thanks.

Rw

 

[Madawc]

I don't think you can do it in selection. Just link using the code, using left-outer if you want to see inventory transaction table items with nothing on the work order status table.

Group by the common code. Use Summary Totals to get the latest date (maximum) from the work order status table. Suppress record details and show the dates in the group header or footer. (If you're not already familiar with Crystal totals, the basics are explained at FAQ767-6524.)

Note that maximum date might be null, so use ISNULL to test before trying anything else.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[koolskins]

The work order status date isn't necessarily the maximum date. An inventory item might have been updated on 2/21/07, but the work order statuses change on a completely different event driven cycle so that the work order may have had different statuses on 1/21/07, 2/07/07, 2/28/07, etc. In this case I would need the work order status for 2/07/07 as that would have been the work order status at the time of this inventory item update. Based on your suggestion already, then I would assume I would need a test to determine which status had the closest date less than the inventory date, but I'm not sure of the exact syntax for that test?

Rw

 

[lbass]

Can we assume you have a linking field other than date,e.g., on item? Or if you have the possibility of doing a <= link, you could use that for the date fields as a first step. If not, then in report->selection formula->record use a formula like:

{workorder.date} <= {inventory.date}

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

{workorder.date} = maximum({workorder.date},{inventory.item})

...assuming you have a group on {inventory.item}.

-LB

 

[koolskins]

Thanks guys. The combined select and group select statement gave me exactly what I needed. As always, really appreciate the assistance. Have a nice day.

Rw