秦淮人家

/*
@date:2004-6-23 12:13
@author:shenqin
@function:
@desc:人力结构统计分析表－员工年龄结构分析表
@ID:M_A_001
*/

DECLARE @departlen int
SET @departlen = [@departlen]
declare @departid nvarchar(30)
SET @departid = '[@departid]%'
--set @departlen ='6'
--set @departid ='001'
declare @age0 int
SET @age0 = '[@age0]'
declare @age1 int
SET @age1 = '[@age1]'
declare @age2 int
SET @age2 = '[@age2]'
declare @age3 int
SET @age3 = '[@age3]'

select t_department.DEPARTname,f5.j1num,f5.j1rate,f5.j2num,f5.j2rate,f5.j3num,f5.j3rate,f5.j4num,
f5.j4rate,f5.j5num,f5.j5rate,f5.num
FROM  
(
select f4.departid,
isnull(f4.j1num, 0.00) as j1num,
cast(cast(isnull(f4.j1num, 0.00)*100.00/f4.num as numeric (9,2)) as varchar(6))+'%' as j1rate ,
isnull(f4.j2num, 0.00) as j2num,
cast(cast(isnull(f4.j2num, 0.00)*100.00/f4.num as numeric (9,2)) as varchar(6))+'%' as j2rate ,
isnull(f4.j3num, 0.00) as j3num,
cast(cast(isnull(f4.j3num, 0.00)*100.00/f4.num as numeric (9,2)) as varchar(6))+'%' as j3rate ,
isnull(f4.j4num, 0.00) as j4num,
cast(cast(isnull(f4.j4num, 0.00)*100.00/f4.num as numeric (9,2)) as varchar(6))+'%' as j4rate ,
isnull(j5.j5num, 0.00) as j5num,
cast(cast(isnull(j5.j5num, 0.00)*100.00/f4.num as numeric (9,2)) as varchar(6))+'%' as j5rate ,
isnull(f4.num, 0.00) as num
FROM (

select f3.departid,
f3.num,
f3.j1num,
f3.j2num,
f3.j3num,
j4.j4num
FROM (
select f2.departid,
f2.j1num,
f2.num,
f2.j2num,
j3.j3num
FROM (
select f1.departid,
f1.j1num,
f1.num,
j2.j2num
FROM (
select sumnum.departid,
sumnum.num,
j1.j1num
FROM (
select substring(t_department.departid, 1, @departlen ) as departid ,count(1) as num
FROM t_department,t_departmentindex,t_employee
where t_department.updepartid like @departid
     AND t_employee.userid=t_departmentindex.userid
     AND t_departmentindex.departid= t_department.departid
     AND t_employee.employeetype NOT IN ('3','4','5')  
     AND t_departmentindex.state='1'
     AND t_employee.employeestate='0'
     AND t_department.state='0'  
GROUP BY substring(t_department.departid, 1, @departlen )
) as sumnum full outer join
(
select substring(t_department.departid, 1, @departlen ) as departid ,count(1) as j1num
FROM t_department,t_departmentindex,t_employee
where t_department.updepartid like @departid
     AND t_employee.userid=t_departmentindex.userid
     AND t_departmentindex.departid= t_department.departid
     AND t_employee.employeetype NOT IN ('3','4','5')  
     AND t_departmentindex.state='1'
     AND t_employee.employeestate='0'
     AND t_department.state='0'  
     AND datediff(year, birthday, getdate()) < @age0
GROUP BY substring(t_department.departid, 1, @departlen )
) as j1
on sumnum.departid = j1.departid
) as f1
full outer join
(
select substring(t_department.departid, 1, @departlen ) as departid ,count(1) as j2num
FROM t_department,t_departmentindex,t_employee
where t_department.updepartid like @departid
     AND t_employee.userid=t_departmentindex.userid
     AND t_departmentindex.departid= t_department.departid
     AND t_employee.employeetype NOT IN ('3','4','5')  
     AND t_departmentindex.state='1'
     AND t_employee.employeestate='0'
     AND t_department.state='0'  
     AND datediff(year, birthday, getdate()) >= @age0
     AND datediff(year, birthday, getdate()) < @age1
GROUP BY substring(t_department.departid, 1, @departlen )
) as j2
on f1.departid = j2.departid
) as f2 full outer join
(
select substring(t_department.departid, 1, @departlen ) as departid ,count(1) as j3num
FROM  t_department,t_departmentindex,t_employee
where t_department.updepartid like @departid
     AND t_employee.userid=t_departmentindex.userid
     AND t_departmentindex.departid= t_department.departid
     AND t_employee.employeetype NOT IN ('3','4','5')  
     AND t_departmentindex.state='1'
     AND t_employee.employeestate='0'
     AND t_department.state='0'  
     AND datediff(year, birthday, getdate()) >= @age1
     AND datediff(year, birthday, getdate()) < @age2
GROUP BY substring(t_department.departid, 1, @departlen )
) as j3
on j3.departid = f2.departid
) AS f3 full outer join
(
select substring(t_department.departid, 1, @departlen ) as departid ,count(1) as j4num
FROM t_department,t_departmentindex,t_employee
where t_department.updepartid like @departid
     AND t_employee.userid=t_departmentindex.userid
     AND t_departmentindex.departid= t_department.departid
     AND t_employee.employeetype NOT IN ('3','4','5')  
     AND t_departmentindex.state='1'
     AND t_employee.employeestate='0'
     AND t_department.state='0'  
     AND datediff(year, birthday, getdate()) >= @age2
     AND datediff(year, birthday, getdate()) < @age3    
GROUP BY substring(t_department.departid, 1, @departlen )
)  as j4
  on j4.departid = f3.departid  
) AS f4 full outer join
(
select substring(t_department.departid, 1, @departlen ) as departid ,count(1) as j5num
FROM t_department,t_departmentindex,t_employee
where t_department.updepartid like @departid
     AND t_employee.userid=t_departmentindex.userid
     AND t_departmentindex.departid= t_department.departid
     AND t_employee.employeetype NOT IN ('3','4','5')  
     AND t_departmentindex.state='1'
     AND t_employee.employeestate='0'
     AND t_department.state='0'  
     AND datediff(year, birthday, getdate()) >= @age3    
GROUP BY substring(t_department.departid, 1, @departlen )
) as j5
on j5.departid = f4.departid
)AS f5,t_department
where f5.DEPARTID = t_department.DEPARTID