Hi Hilmar

I'm always running into this type of query. I rarely have the luxury of using a VFP function. I often do 2 SQL selects. The first produces a set of current wages for all employees using max(datefield) grouping on employee id. That result would be joined with the employees in the final select. If there are fields in the wages table one would have to do another query to get the wage records for all employees where the datefield is equal to the max datefield from the first query. In all 3 queries would have to be done.

I asked about doing these kinds of queries with SQL Server in an earlier thread and got some really good suggestions. How would you get a list of all current complete wage records for all employees in SQL server using SPT?

Can it be done using derived tables with a subselect? I'd prefer to duplicate the VFP 3 query approach in SQL Server, since I'm most comfortable with that approach. I think that would require temporary result sets. Can I make SQL Server do the 3 queries without using a stored procedure? If it can be done using temporary result sets, the results must be specific to each user i.e. if user A runs the query and produces a temp result set, those same results should not be available to user B.

>>Hi all,
>>
>>I've been struggling for many days trying to create a query for the following situation:
>>
>>Employees are stored in a wages table with a field for name, date, wage, etc . Wages change periodically.The employee name, along with the date of change and the new wage are appended periodically.
>>
>>Daily work is stored in a seperate table which has a field for the employee name, man-hours etc.
>>
>>Now, I require a query, so that over a period of time, as the date changes, it keeps pace with the date of wage change, and picks up the relevant wage of the day.
>>
>>Not been possible !!
>>
>>This is the last thing I have done :
>>
>>In wage table, indexing on date in descending order. When a particular date is encountered in the dailywork table, look for which wage-period it belongs to in the wage table.
>>For example, skip on the wage table in a do while loop, until the date of the dailywork table is less than the current date in the wage table. The previous date, along with the attached wage is the wage for that day/period.
>>
>>Had no success at all ! Help !
>>
>>Steve.
>
>I had a similar situation (material prices, updated periodically). You need a UDF which searches in the wages table. Use SET NEAR ON, do a SEEK, and then, if you don't have an exact amtch, SKIP back one record.
>
>The UDF accepts two parameters: in your case, the employee PK, and the date. It should return the wage.
>
>Then, you can integrate it into a SELECT - SQL statement:
>
>

>select EmployeeId, WageByDate(EmployeeId, RelevantDateField) as Wage...
>

>
>HTH,
>
>Hilmar.