> On 13 Aug 2019, at 13:10, stan <stanb@panix.com> wrote:
>
> select
> project.proj_no ,
Removed columns that get in the way of your desired result. You can’t have both details and the sum over them in a
meaningfulway.
> SUM (rate.rate * task_instance.hours)
> from
> task_instance
> join rate on
> rate.employee_key = task_instance.employee_key
> AND
> rate.work_type_key = task_instance.work_type_key
(break)
> inner join employee on
> rate.employee_key = employee.employee_key
> inner join work_type on
> rate.work_type_key = work_type.work_type_key
These are now probably redundant, you don’t need them unless they filter your results.
> inner join project on
> project.project_key = task_instance.project_key
And this JOIN could be dropped if project_key and proj_no weren’t different fields. If both are unique in project, you
coulddrop one of them and keep the same functionality with fewer joins. That said, in the “war” between surrogate and
naturalkeys I’m on the natural keys side. Clearly, not everyone agrees on that.
> GROUP BY
> project.project_key ,
Same columns removed here too.
> ORDER BY
> project.proj_no
> ;
That should give you the total cost for each project.
You could get the same result repeated per employee and per work type as you tried originally, by putting the above
revisedquery as a subquery and joining that back into the full query in the place of your project-related tables (add
theproject_key so you have something to join against).
The repeated sum risks getting multiplied in the final output though, especially if unaware people will be putting the
resultsin an Excel sheet or something. From experience, that either results in people reporting the wrong financial
results(several orders too high) or blaming your query.
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
