Adding the additional indexes may have helped some but not nearly enough. Setting the order to DESC and changing the other code to correspond actually lengthened the time. The 'less than or equal to' in the select is based on the business rule that there will always be a record for the current year.

I'm beginning to wonder if I should break the normalization rule about storing the numbers twice and then I wouldn't have to mess with this complicated code. I could just display it directly from the sites table.


>>I have an application that includes data including the number of employees at a business. The number of employees is reported twice per year and eachreport is maintained in different tables. Each of these 2 tables contains multiple businesses and multiple years. The challenge is to obtain the most recent employee number for the business. The logic goes something like this:
>>1. Look in table1 (pei) for this business and select the employee number for the current year.
>>2. If the number thus obtained is 0, look in table2 (history) for this business and select the employee number for the current year.
>>3. If the number thus obtained is still 0, go back to table1 and select the employee number for this business for the previous year.
>>
>>I have written the following function to perform this task which works, but it is slow. Does anyone have any suggestions on how to speed this up, OR is there a totally different approach to the task?
>>
>>Thanks for any thoughts.
>>
>>

>>function GetRecentFTE()
>>	lparameters lcPermID
>>	local closeit, lnFTE, lnFTE2, lnOldWorkArea
>>	lnOldWorkArea = select()
>>	closeit = used('pei')
>>	select (fulltime + parttime) as tFTE ;
>>		from new_pe_pei_database!pei ;
>>		where PermId = lcPermID and _year <= year(date()) ;
>>		order by _year ;
>>		into cursor crsTempFTE
>>	if _tally > 0				&&	If I found at least 1 record
>>		go bott
>>		lnFTE = tFTE
>>		if ! bof()
>>			skip -1
>>		endif
>>		lnFTE2 = tFTE
>>		use in crsTempFTE
>>		if ! closeit
>>			use in pei
>>		endif
>>	else
>>		lnFTE = 0
>>		lnFTE2= 0
>>	endif
>>	closeit = used('history')
>>	if lnFTE = 0				&&	There was no data in PEI so ck the survey history
>>		select surveys_distributed ;
>>			from newsurveys!history ;
>>			where PermId = lcPermID and _year <= year(date()) ;
>>			order by _year ;
>>			into cursor crsTempFTE
>>		if _tally > 0
>>			go bott
>>			lnFTE = surveys_distributed
>>		else
>>			lnFTE = 0
>>		endif
>>		use in crsTempFTE
>>		if ! closeit
>>			use in history
>>		endif
>>	endif
>>	select(lnOldWorkArea)
>>	if lnFTE > 0
>>		return lnFTE
>>	else
>>		return lnFTE2
>>	endif

>Hello Don,
>
>Can you do you order by _year desceding order that way what you want is at the top of the table and you don't have to do a go bott which if the file is large could take time.
>Just a thought, don't know if it will work or not.
>
>Tim