Dear Baan Experts,
i do have an user input date 03/04/2011 field and i have list of transaction Date and on hand. what is the script to get the closest transaction date with user input date.

example:

user input date 03/04/2011

Transaction date on hand
06/11/2010 50
23/12/2010 20
05/01/2011 60
28/03/2011 120
23/04/2011 90

Result:-
28/03/2011 120

I assume your transaction date on hand is a field value in a table. If so, the simplest solution would be to select all the relevant records from the table, and in the selectdo, for each record you calculate the diff in seconds (convert to utc) with your date. And keep maintaining the lowest difference in a variable.

Another option is to split it into 2 queries, something like this:

select ppmmmttt.date:hist.date
from ppmmmttt
where ppmmmttt.date <= :your_date
order by ppmmmttt.date desc
as set with 1 rows

This retrieves the date in the past, closest to your date, and then you write a second query:

select ppmmmttt.date:fut.date
from ppmmmttt
where ppmmmttt.date >= :your_date
order by ppmmmttt.date
as set with 1 rows

This retrieves the future date closest to your date.

Next you just calculate the diff between
1) your date and the hist.date AND
2) your date and the fut.date

Whichever of the 2 is smallest, "wins".


Good luck!
Eli Nager

Hi,

You can include Eli Nager's suggestion in before.checks event of field section.

Dear Baan Experts,
Thank you so much of the solution.