AcumaticaSQLQueriesAndFunctions/ExpenseExceedsIncome.sql

IF OBJECT_ID ('dbo.ExpenseExceedsIncome','V') IS NOT NULL DROP VIEW dbo.ExpenseExceedsIncome
GO 
CREATE VIEW dbo.ExpenseExceedsIncome
AS
with cte 
as 
(
SELECT c.CompanyID, 
	   c.ContractID, 
	   c.ContractCD, 
	   sum(case when b.[Type] = 'E' then b.CuryRevisedAmount else 0 end) as RevisedExpense,
	   sum(case when b.[Type] = 'I' then b.CuryRevisedAmount else 0 end) as RevisedIncome
FROM PMBudget b 
inner join Contract c on b.CompanyID = c.CompanyID and b.ProjectID = c.ContractID 
group by c.CompanyID, c.ContractID, c.ContractCD
)
SELECT c.CompanyID, 
	   c.ContractID, 
	   c.ContractCD, 
	   c.RevisedExpense,
	   c.RevisedIncome,
	   case when c.RevisedExpense > c.RevisedIncome then 1 else 0 end as ExpenseExceedsIncome,
	   case when c.RevisedExpense > c.RevisedIncome then c.RevisedIncome-c.RevisedExpense else 0 end as ExpenseExceedsIncomeBy   
from cte c