-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2017-05-02
-- Description:	Calculate Total Montly Income Post Interview Values
-- =============================================
CREATE PROCEDURE [dbo].[sp_STUD1_CalculateTotalMonthlyIncomePostInterview] 
	@surfaceItemId INT 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT 
	SUM(ISNULL((CASE WHEN f.surfaceFieldDisplay IN ('Total Gross Monthly Income from Employer', 'Average Gross Monthly Income') THEN d.surfaceFieldValueDecimal ELSE 0 END),0)) AS 'Employment', 
	SUM(ISNULL((CASE WHEN f.surfaceFieldDisplay IN ('Child Maintenance Received', 'Spousal Maintenance Received') THEN d.surfaceFieldValueDecimal ELSE 0 END),0)) AS 'Maintenance', 
	SUM(ISNULL((CASE WHEN f.surfaceFieldDisplay IN ('Social Grants Received (Child)', 'Social Grants Received (Pension)','Social Grants Received (Foster Child)','Social Grants Received (Disability)') THEN d.surfaceFieldValueDecimal ELSE 0 END),0)) AS 'Social Grant(s)'
	FROM pal_SurfaceField f  
	INNER JOIN pal_SurfaceFieldData d on d.surfaceFieldID = f.recId  
	WHERE d.surfaceItemId = @surfaceItemId 


END

