How to use where clause in Count Function?

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

hey kiran

try this

select SUM (CASE WHEN tbl.StatusEmployee= 'New' then 1 else 0 end ) NewEmployee,SUM (CASE WHEN tbl.StatusEmployee= 'Old' then 1 else 0 end ) OldEmployee
from (
(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
)) tbl

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Mohsin Arif7y ago
Accepted answer
hey kiran
try this
select  SUM (CASE WHEN tbl.StatusEmployee= 'New' then 1 else 0 end ) NewEmployee,SUM (CASE WHEN tbl.StatusEmployee= 'Old' then 1 else 0 end ) OldEmployee
from (
(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

)) tbl