AcumaticaSQLQueriesAndFunctions/PMBudgetTotals.sql

DROP VIEW IF EXISTS PMBudgetTotals
GO 
Create View PMBudgetTotals
as
SELECT p.CompanyID, p.ProjectID, 
	   sum(case when p.[TYPE] = 'E' then p.CuryAmount else 0 end) as OriginalExpenseBudgetSum,
	   sum(case when p.[TYPE] = 'E' then p.CuryRevisedAmount else 0 end) as RevisedExpenseBudgetSum,
	   sum(case when p.[TYPE] = 'E' then p.CuryCommittedOrigAmount else 0 end) as OriginalExpenseCommittedSum,
	   sum(case when p.[TYPE] = 'E' then p.CuryCommittedAmount  else 0 end) as RevisedExpenseCommittedSum, 
	   sum(case when p.[TYPE] = 'E' then p.CuryCommittedInvoicedAmount   else 0 end) as CommittedExpenseInvoicedAmountSum,
	   sum(case when p.[TYPE] = 'E' then p.CuryActualAmount   else 0 end) as ActualExpenseAmount,
	   sum(case when p.[TYPE] = 'I' then p.CuryAmount else 0 end) as OriginalIncomeBudgetSum,
	   sum(case when p.[TYPE] = 'I' then p.CuryRevisedAmount else 0 end) as RevisedIncomeBudgetSum,
	   sum(case when p.[TYPE] = 'I' then p.CuryCommittedOrigAmount else 0 end) as OriginalIncomeCommittedSum,
	   sum(case when p.[TYPE] = 'I' then p.CuryCommittedAmount  else 0 end) as RevisedIncomeCommittedSum, 
	   sum(case when p.[TYPE] = 'I' then p.CuryCommittedInvoicedAmount   else 0 end) as CommittedIncomeInvoicedAmountSum,
	   sum(case when p.[TYPE] = 'I' then p.CuryActualAmount   else 0 end) as ActualIncomeAmount,
	   sum(case when p.[TYPE] = 'L' then p.CuryAmount else 0 end) as OriginalLiabilityBudgetSum,
	   sum(case when p.[TYPE] = 'L' then p.CuryRevisedAmount else 0 end) as RevisedLiabilityBudgetSum,
	   sum(case when p.[TYPE] = 'L' then p.CuryCommittedOrigAmount else 0 end) as OriginalLiabilityCommittedSum,
	   sum(case when p.[TYPE] = 'L' then p.CuryCommittedAmount  else 0 end) as RevisedLiabilityCommittedSum, 
	   sum(case when p.[TYPE] = 'L' then p.CuryCommittedInvoicedAmount   else 0 end) as CommittedLiabilityInvoicedAmountSum,
	   sum(case when p.[TYPE] = 'L' then p.CuryActualAmount   else 0 end) as ActualLiabilityAmount,
	   sum(case when p.[TYPE] = 'A' then p.CuryAmount else 0 end) as OriginalAssetBudgetSum,
	   sum(case when p.[TYPE] = 'A' then p.CuryRevisedAmount else 0 end) as RevisedAssetBudgetSum,
	   sum(case when p.[TYPE] = 'A' then p.CuryCommittedOrigAmount else 0 end) as OriginalAssetCommittedSum,
	   sum(case when p.[TYPE] = 'A' then p.CuryCommittedAmount  else 0 end) as RevisedAssetCommittedSum, 
	   sum(case when p.[TYPE] = 'A' then p.CuryCommittedInvoicedAmount   else 0 end) as CommittedAssetInvoicedAmountSum,
	   sum(case when p.[TYPE] = 'A' then p.CuryActualAmount   else 0 end) as ActualAssetAmount
from PMBudget p 
group by p.CompanyID, p.ProjectID