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
- (
- (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 bit) else cast(1 as bit) End [select] ,convert(varchar,mock.JoiningDate,106)JoiningDate,
- case when DATEDIFF(day,mock.JoiningDate,'1/31/'+cast(DATEPART(year,getdate())as varchar)) > 60 then 'Old'
- else 'New' end as StatusEmployee
- from (
- 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
- FROM PostingDetail pd, Employee_Profile E, DESIGNATION D
- 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
-
- ))
Thanks in advance