嗨 如何将条件应用于列并水平获取值在单个表中 什么我试过了：
Hi How to apply condition to the column and get the values horizontally Which is in a single table What I have tried:
<pre>SELECT COUNT(EmpID) AS NewJoinees FROM EmployeeDetails AS EmployeeDetails_7 WHERE (DATEPART(yyyy, DOJ) = 2017) AND (DATEPART(mm, DOJ) = 02) and datepart(mm,DOJ)<='02' and status=0 SELECT COUNT(EmpID) AS Resigned FROM EmployeeDetails AS EmployeeDetails_7 WHERE (DATEPART(yyyy, deactivate) = 2017) AND (DATEPART(mm, deactivate) <= '04') and Status=1 SELECT COUNT(EmpID) AS TotalEmployee FROM EmployeeDetails AS EmployeeDetails_7 where (datepart(yyyy,DOJ) between 2005 and 2017)and datepart(mm,DOJ) <='12' and status=0推荐答案
Below code should work as per your requirement ;WITH CTE AS( SELECT TotalEmployee=case when (datepart(yyyy,DOJ) between 2005 and 2017)and datepart(mm,DOJ) <='12' and status=0 THEN COUNT(EmpID) ELSE 0 END, Resigned=case when (DATEPART(yyyy, deactivate) = 2017) AND (DATEPART(mm, deactivate) <= '04') and Status=1 THEN COUNT(EmpID) ELSE 0 END, NewJoinees=case when (DATEPART(yyyy, DOJ) = 2017) AND (DATEPART(mm, DOJ) = 02) and datepart(mm,DOJ)<='02' and status=0 THEN COUNT(EmpID) ELSE 0 END FROM EmployeeDetails AS EmployeeDetails_7 group by DOJ,deactivate,status ) SELECT SUM(TotalEmployee) TotalEmployee,SUM(Resigned)Resigned,SUM(NewJoinees) NewJoinees FROM CTE