Do you have an index on PEI.PermId, PEI._Year, History.PermId and History._Year?
>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?
>
>
>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
> 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
> 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
>
