If you can use SQL, this might work:
Select mj.job, sum(coalesce(lp.hours,0)), sum(coalesce(le.hours,0))
From master_job mj
Left outer join labor_plan lp on (mj.job=lp.job)
Left outer join labor_exec le on (mj.job=le.job)
If you get duplicates or off-totals from this, try:
With letemp as (select job, sum(hours) from labor_exec),
Lptemp as (select job, sum(hours) from labor_plan)
Select mj.job, sum(coalesce(lp.hours,0)), sum(coalesce(le.hours,0))
From master_job mj
Left outer join lptemp lp on (mj.job=lp.job)
Left outer join letemp le on (mj.job=le.job)
HTH,
Loyd
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Adam West
Sent: Thursday, May 14, 2009 10:51 AM
To: Midrange Systems Technical Discussion
Subject: SQL with several tables
Hi everybody,
I have 3 tables, one with the master_job, other is with Labor planed and
other with labor executed - those labors could have many lines in it.
I need to create a report that shows for each job the total amout of
planed labor and the total amount of executed labor.
Because the labor tables are linked to the master_job, when i create the
report the result is duplicating the lines.
Anyone knows how to extract just the totals for each job with the total
planed and total executed ?
midrange.com
