Summation Problem? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Summation Problem?

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2001-07-30 : 11:34:14

How do I only sum parts, telephone,and travel based for each job # but Estimated
Cost summed for all subtasks???
I know this can be done with a subquery but is it possible to do in one query and if so how??Thanks

Current Query

SELECT
Task.[Job #],
Revisions.[Revision #],
Sum(Subtask.Hours*[Rate Schedule].IJDRate+Subtask.[OT Hours]*[Rate Schedule].IJDRateOT) AS [Estimated Cost],
Revisions.Parts,
Revisions.Telephone,
Revisions.Travel
FROM
(((((Task INNER JOIN Revisions ON Task.[Job #] = Revisions.[Job #])
INNER JOIN Subtask ON Revisions.[Revision #] = Subtask.[Revision #] AND
Revisions.[Job #] = Subtask.[Job #])
INNER JOIN [Worker Extended] ON Subtask.Name = [Worker Extended].EmpName)
INNER JOIN [Rate Schedule] ON [Worker Extended].IntJobDesc1 = [Rate Schedule].IntJobDesc)
INNER JOIN [Tracking Metrics] ON [Rate Schedule].[Rate Schedule] = [Tracking Metrics].[Rate Schedule] AND
[Tracking Metrics].[Tracking Code] = Task.[Tracking Code])
WHERE
Task.[Job #] = '3423508'
GROUP BY Task.[Job #], Revisions.[Revision #],Revisions.Parts,Revisions.Telephone,Revisions.Travel

Current Results:

Job Revision # Estimated Cost Parts Telephone Travel
3423508 0 10.00 $0.00 $0.00 $0.00
3423508 1 10.00 $0.00 $0.00 $0.00
3423508 2 5.00 $0.00 $0.00 $0.00
3423508 3 20.00 $0.00 $0.00 $5.00
3423508 4 10.00 $0.00 $0.00 $0.00
3423508 5 10.00 $0.00 $0.00 $0.00
3423508 6 5.00 $0.00 $0.00 $0.00
3423508 7 0.00 $0.00 $0.00 $0.00
3423508 8 5.00 $0.00 $2.00 $2.00
3423508 9 25.00 $0.00 $0.00 $0.00

Desired:

Job Max Revision # Total Cost
3423508 9 $109.00

Total Cost = Sum(Estimated Cost) for all subtasks +
Sum(Parts) for each revision +
Sum(Telephone) for each revision +
Sum(Travel) for each revision

The join between Revisions and Subtask Tables is a one-to-many relationship.

Edited by - ValterBorges on 07/30/2001 11:41:15

Edited by - ValterBorges on 07/30/2001 11:41:58

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources