quote:

---

Originally posted by d4vemcc

Hi, I have a table that has number of hours remaining and taskID. I need to find the number of orginal hours remaining within each task and total this number up and output it to a label. I'm having some trouble with the SQL query at the minute, as I am unsure of how to find the minimum date and also sum up these totals. When a user adds a task they have to add the number of hours remaining on each task, they can then update these hours. The orginal value they enter is the orginal number of hours estimated. So a user may have several task id's. For example in the table below task 1 has two different hours remaining, one is the original input value and the next is the updated. We need to find the sum of all of the orginal values of all of the tasks. We were assuming we'd have to use the MIN and SUM functions.



TaskID Hours Remaining Date

1 12 31/10/11

1 10 01/11/11

2 30 15/10/11

2 0 01/11/11

3 22 30/10/11

4 19 10/10/11

4 10 01/11/11



Here is an example of the query we already have, it also uses several other sprint tables:



SELECT tblTasks.TaskID, tblUserTaskHours.HoursRemaining
FROM tblTasks INNER JOIN
tblUserTasks ON tblTasks.TaskID = tblUserTasks.fkTaskID INNER JOIN
tblUserTaskHours ON tblUserTasks.UserTaskID = tblUserTaskHours.fkUserTaskID
WHERE (tblTasks.TaskID IN
(SELECT TOP (100) PERCENT MIN(tblUserTaskHours_2.Date) AS MinDate, tblTasks_2.TaskID
FROM tblTasks AS tblTasks_2 INNER JOIN
tblUserTasks AS tblUserTasks_2 ON tblTasks_2.TaskID = tblUserTasks_2.fkTaskID INNER JOIN
tblUserTaskHours AS tblUserTaskHours_2 ON tblUserTasks_2.UserTaskID = tblUserTaskHours_2.fkUserTaskID
WHERE (tblTasks_2.TaskID IN
(SELECT TOP (100) PERCENT tblTasks_1.TaskID
FROM tblSprintStories INNER JOIN
tblSprints ON tblSprintStories.fkSprintID = tblSprints.SprintID INNER JOIN
tblTasks AS tblTasks_1 ON tblSprintStories.SprintStoryID = tblTasks_1.fkSprintStoryID INNER JOIN
tblUserTaskHours AS tblUserTaskHours_1 INNER JOIN
tblUserTasks AS tblUserTasks_1 ON tblUserTaskHours_1.fkUserTaskID = tblUserTasks_1.UserTaskID ON
tblTasks_1.TaskID = tblUserTasks_1.fkTaskID
WHERE (tblSprints.SprintID = @SprintID)
ORDER BY tblTasks_1.TaskID))
GROUP BY tblTasks_2.TaskID
ORDER BY tblTasks_2.TaskID, MinDate))



Any help would be appreciated guys.



Thanks!

---

SELECT SUM([Hours Remaining])
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY TaskID ORDER BY [Date] ASC) AS Rn,*
FROM table
)t
WHERE Rn=1