Hello, On Fri, 2019-08-16 at 07:39 -0400, stan wrote: > 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.burdene > d_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? >
Your view assumes that all three "streams" contain all the proj_no's whereas your test data for expense_report_cost_sum_view has no proj_no = 764. How do you know which of the three "streams" contains all proj_no's? Maybe you should consider the crosstab code so you end up with something like this;- proj_no | Labour | Material | Expenses 45 | 10810 | 5394 | 2564 764 | 8712 | 7249 | 0 789 | 46335 | 4007 | 1747
