We have 12 types of expenses in our database, some have fairly differing data minus the Amount fields.  We have multiple places in the application and reports that require single and multiple expense totals and counts per expense Type and Grand totals.  In the end, we want one View for all of these calls but are open to using a stored procedure.

We looked at multiple alternatives for this and found that a CTE allows us to get all the data required without the use of temp tables.  Using joins does not work as we saw records being replicated or removed no matter what we tried.

I’ve attached a subset of the expense tables and the query that includes the CTE.  Does anybody have a better alternative than this? Something faster?  Are we approaching this ‘flattening’ appropriately?  

Please note the execution plan is the same for this query whether it is a View or a Proc and the Proc seems to take twice as long to run.

Below is the code
    
    WITH pe AS
    (
	SELECT 
		EventRegistrationId
		,sum(AmountPaid)			as AmountPaidTotal
		,sum(CommercialValueAmount) as CommercialValueAmountTotal
		,count(1) as ExpenseCount
	FROM PettyExpenses 
	WHERE IsDisputed = 0 AND IsUndisputed = 0
	group by EventRegistrationId
    ),hpe AS
    (
	SELECT 
		EventRegistrationId
		,sum(AmountPaid)			as AmountPaidTotal
		,sum(CommercialValueAmount) as CommercialValueAmountTotal
		,count(1) as ExpenseCount
	FROM HirePremisesExpenses 
	WHERE IsDisputed = 0 AND IsUndisputed = 0
	group by EventRegistrationId
    ), ae AS
    (
	SELECT 
		EventRegistrationId
		,sum(AmountPaid)			as AmountPaidTotal
		,sum(CommercialValueAmount) as CommercialValueAmountTotal
		,count(1) as ExpenseCount
	FROM AdvertisingExpenses 
	WHERE IsDisputed = 0 AND IsUndisputed = 0
	group by EventRegistrationId
    ), se AS
    (
	SELECT 
		EventRegistrationId
		,sum(AmountPaid)			as AmountPaidTotal
		,sum(CommercialValueAmount) as CommercialValueAmountTotal
		,count(1) as ExpenseCount
	FROM ServiceExpenses 
	WHERE IsDisputed = 0 AND IsUndisputed = 0
	group by EventRegistrationId
    ), gse AS
    (
	SELECT 
		EventRegistrationId
		,sum(AmountPaid)			as AmountPaidTotal
		,sum(CommercialValueAmount) as CommercialValueAmountTotal
		,count(1) as ExpenseCount
	FROM GoodsSuppliedExpenses 
	WHERE IsDisputed = 0 AND IsUndisputed = 0
	group by EventRegistrationId
    ), thve AS
    (
	SELECT 
		EventRegistrationId
		,sum(AmountPaid)			as AmountPaidTotal
		,sum(CommercialValueAmount) as CommercialValueAmountTotal
		,count(1) as ExpenseCount
	FROM TravelHireVehicleExpenses 
	WHERE IsDisputed = 0 AND 
	IsUndisputed = 0
	group by EventRegistrationId

    )
    select
	distinct eer.EventRegistrationId
	--Petty Expense
	,ISNULL(pe.AmountPaidTotal,0) as PettyExpenseAmountPaid
	,ISNULL(pe.CommercialValueAmountTotal,0) as PettyExpenseCommercial
	,ISNULL(pe.ExpenseCount,0) as PettyExpenseCount
	--Hire On Premise Expense
	,ISNULL(hpe.AmountPaidTotal,0) as HireOnPremisesExpenseAmountPaid
	,ISNULL(hpe.CommercialValueAmountTotal,0) as HireOnPremisesExpenseCommercial
	,ISNULL(hpe.ExpenseCount,0) as HireOnPremisesExpenseCount
	--Advertising Expense
	,ISNULL(ae.AmountPaidTotal,0) as AdvertisingExpenseAmountPaid
	,ISNULL(ae.CommercialValueAmountTotal,0) as AdvertisingExpenseCommercial
	,ISNULL(ae.ExpenseCount,0) as AdvertisingExpenseExpenseCount
	--Services Expense
	,ISNULL(se.AmountPaidTotal,0) as ServiceExpenseAmountPaid
	,ISNULL(se.CommercialValueAmountTotal,0) as ServiceExpenseCommercial
	,ISNULL(se.ExpenseCount,0) as ServiceExpenseExpenseCount
	--Goods Supplied Expense
	,ISNULL(gse.AmountPaidTotal,0) as GoodsSuppliedExpenseAmountPaid
	,ISNULL(gse.CommercialValueAmountTotal,0) as GoodsSuppliedExpenseCommercial
	,ISNULL(gse.ExpenseCount,0) as GoodsSuppliedExpenseExpenseCount
	--Travel and Vehicle Expense
	,ISNULL(thve.AmountPaidTotal,0) as TravelVehicleExpenseAmountPaid
	,ISNULL(thve.CommercialValueAmountTotal,0) as TravelVehicleExpenseCommercial
	,ISNULL(thve.ExpenseCount,0) as TravelVehicleExpenseExpenseCount
	--All Expenses
	,ISNULL(pe.AmountPaidTotal,0) 
		+ ISNULL(hpe.AmountPaidTotal,0)
		+ ISNULL(ae.AmountPaidTotal,0) 
		+ ISNULL(se.AmountPaidTotal,0)
		+ ISNULL(gse.AmountPaidTotal,0) 
		+ ISNULL(thve.AmountPaidTotal,0) as AllExpenseAmountPaidTotal
	,ISNULL(pe.CommercialValueAmountTotal,0) 
		+ ISNULL(hpe.CommercialValueAmountTotal,0)
		+ ISNULL(ae.CommercialValueAmountTotal,0) 
		+ ISNULL(se.CommercialValueAmountTotal,0)
		+ ISNULL(gse.CommercialValueAmountTotal,0) 
		+ ISNULL(thve.CommercialValueAmountTotal,0) as AllExpenseCommercialValueTotal
	,ISNULL(pe.ExpenseCount,0) 
		+ ISNULL(hpe.ExpenseCount,0)
		+ ISNULL(ae.ExpenseCount,0) 
		+ ISNULL(se.ExpenseCount,0)
		+ ISNULL(gse.ExpenseCount,0) 
		+ ISNULL(thve.ExpenseCount,0) as AllExpenseCount
    from EventRegistrations eer
	left join pe on pe.EventRegistrationId = eer.EventRegistrationId
	left join hpe on hpe.EventRegistrationId = eer.EventRegistrationId
	left join ae on ae.EventRegistrationId = eer.EventRegistrationId 
	left join se on se.EventRegistrationId = eer.EventRegistrationId
	left join gse on gse.EventRegistrationId = eer.EventRegistrationId
	left join thve on thve.EventRegistrationId = eer.EventRegistrationId
  



**UPDATE:** 

Here is the db schema with inserts for those that are interested in seeing it live.

[DB Schema and Inserts][1]



Using SQL Server 2014 Standard
I changed the db schema/inserts to a file (to large for here) which has more inserts as well as  uploaded execution plan and results (2 images go side by side to show all returned columns)

[![execution plan][2]][2]

[![results 1][3]][3]

[![results continued][4]][4]


  [1]: http://www.filedropper.com/databaseschema
  [2]: https://i.sstatic.net/BsHD5.png
  [3]: https://i.sstatic.net/IKg10.png
  [4]: https://i.sstatic.net/j1cwx.png