set nocount on SET STATISTICS TIME on SELECT [TM_User].[FullName], [a].[end_date] as Last_Task_Completion_Date, [a].[allocated_hrs] FROM [dbo].[TM_User] INNER JOIN ( SELECT [Task].[task_id] , [Task_Hours].[user_id] , [allocated_hrs], ROW_NUMBER() OVER ( PARTITION BY [user_id] ORDER BY [Task].[end_date] DESC ) rtt , [Task].[end_date] FROM [dbo].[Task_Hours] INNER JOIN [dbo].[Task] ON [Task].[task_id] = [Task_Hours].[task_id] AND allocated_hrs=0 AND CONVERT(varchar,Task.end_date,111) >= '2013/08/15' AND CONVERT(varchar,Task.end_date,111) <='2013/08/29' ) a ON [TM_User].[TM_UserID] = [a].[user_id] WHERE [a].[rtt] = 1 AND [is_active]= 1 SET STATISTICS TIME off set nocount off
end_date
SELECT FullName, MAX(end_date)FROM TableNameGROUP BY FullName
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)