Good Morning,
I cannot figure out how to add a join to this already complicated statement, can anyone help?
I have two tables [TBL_WD_HIRE_TERM_MOVE] and [TBL_FSC_CAL] I need to join two columns from [TBL_FSC_CAL]
Joining to:
[TBL_WD_HIRE_TERM_MOVE] join on column [Effective Date]
Joining from:
[TBL_FSC_CAL] join to column [T625_CAL_DT] and bring back [FSC_WK_NBR] and [FSC_YR_NBR]
Here is my Statement:
SELECT
CONVERT(date,[Effective Date]) AS Effective_Date,
CAST(right([Location - Proposed],4) as int) [Location],[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,
SUM(CASE WHEN [Business Process Type] = 'Hire' THEN 1 ELSE 0 END)-SUM(CASE WHEN [Business Process Type] = 'Termination' THEN 1 ELSE 0 END) [Net Hires]
FROM [TA_HST_DATA].[dbo].[TBL_WD_HIRE_TERM_MOVE]
WHERE [L2] = 'John Smith'
AND ([Business Process Type] = 'Hire' OR [Business Process Type] = 'Termination')
AND [Effective Date] >= '2021-12-25'
AND Status = 'Successfully Completed'
GROUP BY [Effective Date], cast(right([Location - Proposed],4)as int), [Job Code - Proposed]