Kiran Kumari

Kiran Kumari

  • NA
  • 142
  • 11.6k

How to use where clause in Count Function?

Jul 1 2019 11:29 PM
hey Everone 
i have a query in which i seggregate old and new Employee i want to count how many New and old employees are there. how can i seggregate and i dont want to use group by cluase. 
here is my Query 
  1. (  
  2. (select mock.EID, mock.ECode, mock.BaseCode, mock.STATUS, mock.EmpName, mock.Desig, mock.CMP_ID, case when podt.empid is null then cast(0 as bitelse cast(1 as bitEnd [select] ,convert(varchar,mock.JoiningDate,106)JoiningDate,  
  3. case when DATEDIFF(day,mock.JoiningDate,'1/31/'+cast(DATEPART(year,getdate())as varchar))  > 60 then 'Old'  
  4. else 'New' end as StatusEmployee  
  5.  from (  
  6. SELECT E.EID, E.ECode, E.BaseCode, CASE WHEN E.EActive = 0 THEN 'ACTIVE' ELSE 'IN ACTIVE' END AS STATUS, E.EmpName, D.DesigDesc As Desig, E.CMP_ID ,CAST(E.JoiningDate AS datetime) JoiningDate  
  7. FROM PostingDetail pd, Employee_Profile E, DESIGNATION D   
  8. Where pd.EmpId = E.EID AND E.Desig = D.DesigCode AND pd.SubLoc in(6700, 20400, 21300, 21900) AND pd.EndDate is null AND CMP_ID IN('SZSPL'and cast(E.JoiningDate as datetime) between cast(E.JoiningDate as datetime) and '1/31/'+cast(DATEPART(year,getdate())as varchar)) mock left outer join PostingDetail_training podt on mock.EID=podt.empid    
  9.   
  10. ))  
Thanks in advance  

Answers (1)