SQLTeam.com | Weblogs | Forums

Amend SQL statement to add join

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]

I'm not sure what the issue is. I don't see a join to the calendar table