Hi

I would appreciate some suggestions about the best form of an SQL - Select command that will do the following.

I want to select all the tasks from BFHistry into a cursor with the following fields:

Table     Field     Description
-----     -----     -----------
Cursor    Task      Task Name
          Status    Most Recent Status Description for the task
          Event     Event Name
          WorkGroup WorkGroup Description
          Date      Most Recent Status Date for the Task
          Time      Most Recent Status Time for the Task

BFHistry  ID        Unique ID
          TaskID    Task ID From BFTask
          EventID   Event ID From BFEvent
          WorkGrpID WorkGroup ID From BFWrkGrp

BFStatus  ID         Unique ID
          Desc       Status Description

BFEvent   ID         Unique ID
          Desc       Event Description

BFWrkGrp  ID         Unique ID
          Desc       WorkGroup Description

BFTask    ID         Task ID
          Desc       Task Description

BFStaHis  ID         Unique ID
          Date       Status Date
          Time       Status Time
          PID        ID (Parent) From BFHistry
          StatusID   Status ID From BFStatus

The tasks go through a number of status changes over time and each status change is recorded in the BFStaHis table. The goal of the query is to find all the tasks and their most recent status. Therefore, I only want one record per task with the most recent status.

I am already doing this in Visual FoxPro but without using a SQL - Select. This will have to changed to run in SQL-Server that is the reason for my inquiry. If there is a better alternative for SQL-Server I would be interested to know about it.

The last complication would be to only include tasks that have never been completed (ie. whose StatusID was never "999999").

Thanks,
Simon