First let me say a huge THANK YOU to all the helpful people that hanging out
on this.
I am changing from one type of work, going back to some database work for a
project, as my old job was eliminated. I have made great progress on this,
thanks to the time and effort of lots of folks from this list.
Now, here is my latest stumbling block. I have three "data streams" that all
contribute to the total costs of a project:
* labor cost
* material cost
* expense report cost
I have a view that summarizes the burdened cost from each of these 3
streams, and i am trying to create a view that shows the total project cost.
Here is the test data from each of the 3 streams:
stan=> select * from labor_cost_sum_view ;
proj_no | labor_bill_sum | labor_cost_sum
---------+----------------+----------------
45 | 10810.0000 | 3133.17500000
764 | 8712.0000 | 810.75000000
789 | 46335.5400 | 7015.57500000
(3 rows)
stan=> select * from material_cost_sum_view ;
proj_no | mtrl_cost
---------+-----------
45 | 5394.6800
764 | 7249.4800
7456 | 4007.3000
(3 rows)
stan=> select * from expense_report_cost_sum_view ;
proj_no | incured_sum | burdened_cost
---------+-------------+---------------
45 | 2564.98 | 2564.980000
7456 | 1747.11 | 1747.110000
(2 rows)
And here is the clause for creating the summary table that I presently have:
DROP VIEW overall_cost_sum_view ;
CREATE view overall_cost_sum_view as
select
material_cost_sum_view.proj_no as l_proj_vo ,
labor_cost_sum_view.proj_no as m_proj_vo ,
expense_report_cost_sum_view.proj_no as x_proj_vo ,
cast (labor_cost_sum_view.labor_cost_sum as money) as l_burdened_cost,
cast (material_cost_sum_view.mtrl_cost as money)as m_burdened_cost,
cast (expense_report_cost_sum_view.burdened_cost as money)as x_burdened_cost ,
cast (
coalesce( labor_cost_sum_view.labor_cost_sum, 0)
+
coalesce(material_cost_sum_view.mtrl_cost, 0)
+
coalesce(expense_report_cost_sum_view.burdened_cost, 0)
as money) as ttl_cost
from
labor_cost_sum_view
full join material_cost_sum_view on
material_cost_sum_view.proj_no = labor_cost_sum_view.proj_no
full join expense_report_cost_sum_view on
expense_report_cost_sum_view.proj_no = labor_cost_sum_view.proj_no
;
Which results in the following:
stan=> select * from overall_cost_sum_view ;
-----------+-----------+-----------+-----------------+-----------------+-------- ---------+------------
45 | 45 | 45 | $3,133.18 | $5,394.68 | $ 2,564.98 | $11,092.84
764 | 764 | | $810.75 | $7,249.48 | | $8,060.23
| 789 | | $7,015.58 | | | $7,015.58
7456 | | | | $4,007.30 | | $4,007.30
| | 7456 | | | $ 1,747.11 | $1,747.11
(5 rows)
As you can see this statement seems to work correctly on the join of the
labor and material costs, but fails when I add the expense report stream.
What am I doing wrong here?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
