如何根据条件选择列？ [英] How do I select a column based on condition?

问题描述

嗨

如何将条件应用于列并水平获取值在单个表中



什么我试过了：

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

这篇关于如何根据条件选择列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！