Click here to Skip to main content
14,921,476 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi , is this below query can we use differently..

SQL
SELECT
(SELECT COUNT(id) FROM Employees WHERE YEAR(hire_date)=2011) as hirecnt,
(SELECT COUNT(id) FROM Employees WHERE YEAR(termination_date)=2011)as termcnt



i try below query like this ...its not working properly


select count(id) as cnt,employment_status from Employees
where ( YEAR(termination_date)=2010 and employment_status='Terminated')
or
( YEAR(hire_date)=2010 and employment_status='Active')
group by employment_status
Posted
Comments
Bernhard Hiller 3-Apr-14 2:24am
   
What do you want to achieve? Count the employess who were hired ina specific year and are still active - or does their current status not matter? Or count those who were hired and fired in the same specified year? Or...?
AndrewCharlz 3-Apr-14 2:36am
   
please show us the output of ur query so that we can give some solution
thatraja 3-Apr-14 5:04am
   
Not clear

1 solution

SQL
;WITH EMP_CTE AS
(SELECT *   from Employees WHERE (YEAR(hire_date)=2011) OR (YEAR(termination_date)=2011))
SELECT SUM(hirecnt) as hirecnt, SUM(termcnt) as termcnt FROM
(
SELECT COUNT(*)  as hirecnt, 0 as termcnt FROM EMP_CTE WHERE YEAR(hire_date)=2011
UNION ALL
SELECT COUNT(*) as termcnt , 0 as hirecnt FROM EMP_CTE WHERE YEAR(termination_date)=2011
) as y
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900