AcumaticaSQLQueriesAndFunctions/ActualExpenseExceedsIncome.sql

IF OBJECT_ID ('dbo.ActualExpenseExceedsIncome','V') IS NOT NULL DROP VIEW dbo.ActualExpenseExceedsIncome
GO 
CREATE VIEW dbo.ActualExpenseExceedsIncome
AS
with cte 
as 
(
SELECT c.CompanyID, 
	   c.ContractID, 
	   c.ContractCD, 
	   sum(case when b.[Type] = 'E' then b.CuryActualAmount else 0 end) as ActualExpense,
	   sum(case when b.[Type] = 'I' then b.CuryActualAmount else 0 end) as ActualIncome
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.ActualExpense,
	   c.ActualIncome,
	   case when c.ActualExpense > c.ActualIncome then 1 else 0 end as ExpenseExceedsIncome,
	   case when c.ActualExpense > c.ActualIncome then c.ActualIncome-c.ActualExpense else 0 end as ExpenseExceedsIncomeBy   
from cte c