Click here to Skip to main content
15,918,617 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
SQL
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
Posted
Updated 22-Sep-13 20:27pm
v2

1 solution

No! It doesn't works like this. ;)

What above query does? It fetches latest task for each user in a date range. You can achieve the same using MAX[^] function on end_date field.
 
Share this answer
 
Comments
Member 10256268 23-Sep-13 3:12am    
if i use max function i will get result like this
task_id FullName (Max_end_date)
1890 Deepti Injeti 2013-08-15 00:00:00
1913 Deepti Injeti 2013-08-26 00:00:00
1916 Deepti Injeti 2013-08-22 00:00:00
u can see for different task-id we have different date if i use max function for end_date even then i will get same records ,i want only latest date.
Maciej Los 23-Sep-13 3:33am    
Remove task_id from select statement and see what happen ;)
Member 10256268 23-Sep-13 3:35am    
if i remove task_id frm select statment i am getting same result .
the result is
FullName (Max_end_date)

Deepti Injeti 2013-08-15 00:00:00
Deepti Injeti 2013-08-26 00:00:00
Deepti Injeti 2013-08-22 00:00:00
Maciej Los 23-Sep-13 3:38am    
Does yous query looks like:
SELECT FullName, MAX(end_date)
FROM TableName
GROUP BY FullName

?
Member 10256268 23-Sep-13 3:39am    
soory maciej los no use sme result

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900