select
pj.zProjectID,
tk.zTaskID,
isnull(bud.zRevenueAmt,0) as BUD_zRevenueAmt,
isnull(bud.zCostAmt,0) AS BUD_zCostAmt,
isnull(tx.zRevenueAmt,0) as TRX_zRevenueAmt,
isnull(tx.zCostAmt,0) as TRX_zCostAmt
from pc00200 pj
join pc00300 tk on pj.zProjectID = tk.zProjectID
left join (
select
bud.zProjectID,
bud.zTaskID,
sum(zRevenueAmt) as zRevenueAmt,
sum(zCostAmt) as zCostAmt
from pc25000 bud
group by bud.zProjectID, zTaskID
) bud on pj.zProjectID = bud.zProjectID and tk.zTaskID = bud.zTaskID
left join (
select
tx.zProjectID, tx.zTaskID,
sum(zRevenueAmt) as zRevenueAmt,
sum(zCostAmt) as zCostAmt
from pc10000 tx
group by tx.zProjectID, zTaskID
) tx on pj.zProjectID = tx.zProjectID and tk.zTaskID = tx.zTaskID
where pj.zProjectID = 'MYPROJECT'
AND tk.zTaskID like 'MYTASK'
order by 1,2