Function to fetch minimum date

mkpjsr

Hi all,
can anybody tell me a function to fetch minimum date from a table.

Marije_Brummel

The easiest is to create an index (key) on that field and do a findfirst. (1)

Other solutions are to browse through the table with a variable (2) or to use the date table as temp table.(3)

If you can spare the index I would go for option 1

mkpjsr

Mark Brummel wrote:

The easiest is to create an index (key) on that field and do a findfirst. (1)

Other solutions are to browse through the table with a variable (2) or to use the date table as temp table.(3)

If you can spare the index I would go for option 1

Can u give me some hint on solution (2) and (3)

Marije_Brummel

Why do you think solution 1 does not work?

If you have more than let's say a thousand records in your table, solutions 2 and 3 will be dreadfully slow.

mkpjsr

Mark Brummel wrote:

Why do you think solution 1 does not work?

If you have more than let's say a thousand records in your table, solutions 2 and 3 will be dreadfully slow.

If the table contains more then one keys then will it work

matttrax

mkpjsr wrote:

If the table contains more then one keys then will it work

Sure it will. In fact, most tables have more than one key. It's very common to need your data sorted a certain way. In your case you want it sorted by some data field. So you add a key / index.

The trade off is that SQL (assuming you're on SQL) will take a slightly, not noticeable, longer amount of time to insert records. This is because it has to keep up with all of the indexes. You are trading a very small amount of time up front for less time down the road when you need to do calculations.

Marije_Brummel

Native DB needs more time as well.

If you only do this function once a day or something and if you run on SQL you can have the key in NAV but disable it on SQL.

jevgjem

mkpjsr wrote:

Can u give me some hint on solution (2) and (3)

//variable MinDate (date)

if MyRec.findfirst then
repeat
if (MinDate > MyRec.Date) OR
(MinDate = 0D) then
MinDate := MyRec.Date;
until myRec.next=0;

But this solution is really slow on big amounts of data.

Belias

jevgjem wrote:

mkpjsr wrote:

Can u give me some hint on solution (2) and (3)

//variable MinDate (date)

if MyRec.findfirst then
repeat
if (MinDate > MyRec.Date) OR
(MinDate = 0D) then
MinDate := MyRec.Date;
until myRec.next=0;

But this solution is really slow on big amounts of data.

even slower with your code!!
NEVER USE FINDFIRST WITH "REPEAT...UNTIL" STATEMENTS, PLEASE READ ONLINE HELP!

This function should be used instead of FIND('-') when you only need the first record.

You should only use this function when you explicitly want to find the first record in a table or set. Do not use this function in combination with REPEAT..UNTIL.

For further informations about WHY don't use it, look here http://www.mibuso.com/howtoinfo.asp?FileID=22 or search mibuso or waldo's blog or microsoft manuals