Click here to Skip to main content
15,867,330 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
 
Share this answer
 

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