Sum of total hours as well as minimum dateSQL

Closed
Lee - Dec 1, 2011 at 12:18 PM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Dec 9, 2011 at 06:24 AM
Hello,

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!


1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Dec 9, 2011 at 06:24 AM
why this sound like you are working in AGILE methodology :P

Is it possible to have original time estimate for each task go into one table and updated time estimates go into other table. Or at least have a column that says this is the origianal estimate for the task

From what I see, first one has to identify the original estimate. That would mean I need to use group by and min clause just to find out which is my original estimate.

Then i would have have to group on task id and find out time remaining
0