>I am using SPT to get data for a chart. I need help with SQL statement so I get teh correct records.
>
>The table is a list of records that define users to particular project platforms with a schedule start date and finish date. One user can have several records but only one record per user can have null in the finish field. I basically want the latest schedule of the user or to know if the user has no current schedule.
>
>Here is the table layout. Null in the finish date indicates user is assigned indefinetly
>
>Userid Platform StartDate FinishDate
>SDE TC 06/05/2008 09/05/2009
>SDE TC 09/06/2009 null
>MJS PC 09/07/2009 09/10/2009
>KGT TC 09/10/2008 null
>JJJ SA 06/28/2009 05/01/2011

One of the solutions - SQL Server 2005 and up

;with cte as (select UserID, Platform, StartDate, FinishDate, row_number() over (partition by UserID order by FinishDate DESC) as RowNum) from myTable)

select * from cte where FinishDate IS NULL or RowNum = 1

See also http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/including-an-aggregated-column-s-related for the comprehensive analysis of available methods.