Select where date is closest to today

[piscomatt]

Hi,
I have an employee table which links to a pay history table. One employee could have multiple rows in the pay history table. When I join the two tables, how can I select only one row per person where I choose the row in the pay history table that has the date closest to today.

Hope that makes sense. Thanks.

 

[mrstyxjdm]

Is the History table indexed on Pay-Day (Date column)?

You can select using Max().

 

[blom0344]

This is more complex that it may sound. There are 2 issues to be solved:

1. Calculate difference between date(s) and the system date
2. Select record for each employee where the calculated diff is the smallest.

This is a typical scenario where you may need a 2-step approach.

First step is to build a view that contains the data you want from the history table with the diff as a calculated field.
Second step is a select over the view , adding a ranking based on the diff with break on each employee.

With the proper ranking definition this will yield an object that contains all data with an indicator '1' that you can use to select one record for each employee you need.

Let us know if you need code examples..

Ties Blom