You are not selecting
DepId
in CTE
t3
[EDIT] As requested, here is my guess at what the modified query should be. Note that I say "guess" because you have still not given me
enough any information in the form of table schemas, sample data or expected results.
;with t1 as (
SELECT DepId, COUNT(EmpId) AS TotalHeadCount FROM Emploee
WHERE (datepart(yyyy,DOJ) between 2005 and 2017) and Status =0
group by DepId
),
t2 as (
SELECT DepId,COUNT(EmpId) AS NewJoinees FROM Emploee
WHERE (DATEPART(yyyy, DOJ) = 2017) AND (DATEPART(mm, DOJ) = 01) and datepart(mm,DOJ)>= 12 and Status = 0
group by DepId
) ,
t3 as (
SELECT Depid, Tobehired AS TOBEHIRED,OpenPosition AS OPENPOSITION,Status FROM Employee1 )
Select t1.DepId, CASE WHEN TotalHeadCount IS NULL THEN '0' ELSE TotalHeadCount END AS TotalHeadCount,CASE WHEN NewJoinees IS NULL THEN '0'
ELSE NewJoinees END AS NewJoinees,Tobehired,OpenPosition,Status
from t1 full outer join t2 on t1.DepId = t2.DepId
full outer join t3 on t1.DepId = t3.DepId