SQLTeam.com | Weblogs | Forums

SQL Statement

Hi,

New to SQL, I need to add a new column to a statement I created before but it does not work for me.

I need Hire_Count to be substracted from Term_Count and output as a new 'Net Hires' column

SELECT [Effective Date],[Location - Proposed],[Job Code - Proposed],
SUM(CASE WHEN [Business Process Type] = 'Hire' THEN 1 ELSE 0 END) HIRE_Count,
SUM(CASE WHEN [Business Process Type] = 'Termination' THEN 1 ELSE 0 END) TERM_Count

FROM [DATA].[dbo].[HIRE_TERM]

WHERE [L2] = 'John Smith'
AND ([Business Process Type] = 'Hire' OR [Business Process Type] = 'Termination')
AND [Effective Date] >= '2021-12-01'
AND Status = 'Successfully Completed'

GROUP BY [Effective Date],[Location - Proposed],[Job Code - Proposed]
ORDER BY [Effective Date]

...
SUM(CASE WHEN [Business Process Type] = 'Hire' THEN 1 ELSE 0 END) -
SUM(CASE WHEN [Business Process Type] = 'Termination' THEN 1 ELSE 0 END) AS Net_Hires
...

1 Like

Thank you sooooo much!

You're welcome!