August 11, 2008 at 6:52 am
How can I merge the two queries to return a count of the employees and a count of the employees using some function. The columns returned should be location group name, count of employees, count of employees using function? Thanks in advance.
-- loc list by date&age
select LOCGRPNAME AS 'calc_location', COUNT(empid) AS 'calc_num_employees'
from LOCATION loc, LOCATIONGROUP locgrp, EMPLOYEE emp
where loc.LOCID = emp.LOCID
and loc.LOCGRPID = locgrp.LOCGRPID
and [dbo].[ufn_GetAge] ( [DOB], GETDATE() ) >= 60
group by LOCGRPNAME
--
select LOCGRPNAME AS 'location', COUNT(empid) AS 'num_employees', COUNT([dbo].[ufn_GetAge] ( [DOB], GETDATE() )) AS AGE
from LOCATION loc, LOCATIONGROUP locgrp, EMPLOYEE emp
where loc.LOCID = emp.LOCID
and loc.LOCGRPID = locgrp.LOCGRPID
group by LOCGRPNAME
August 11, 2008 at 10:01 am
Is this what you are after ?
select LOCGRPNAME AS 'location', COUNT(empid) AS 'num_employees',
SUM(
CASE WHEN [dbo].[ufn_GetAge] ( [DOB], GETDATE() ) >= 60 THEN 1 ELSE 0 END
) AS AGE
from LOCATION loc, LOCATIONGROUP locgrp, EMPLOYEE emp
where loc.LOCID = emp.LOCID
and loc.LOCGRPID = locgrp.LOCGRPID
group by LOCGRPNAME
* Noel
August 11, 2008 at 10:25 am
Works great!!! Thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply