表数据
ID,人员ID,聘任时间,聘任职称
1, 1, 1998-2-1,初级工
2, 1, 2000-2-1,中级工
3, 1, 2005-3-1,高级工
4, 2, 2002-2-1,初级工
5, 2, 2004-5-4,中级工 如果@sj='2000-8-9',@sj2='2006-8-2'表示要统计2000年到2006年的职称聘任情况,因此要得到下面结果
年份 , 高级工, 中级工, 初级工
2000 , 0 , 1, 0
2001, 0, 1, 0
2002, 0, 1, 1
2003, 0 1, 1
2004, 0, 2, 0
2005, 1, 1, 0
2006, 1, 1, 0
year(聘任时间) as 年份,
sum(case when 聘任职称='高级工' then 1 else 0 end) as 高级工,
sum(case when 聘任职称='中级工' then 1 else 0 end) as 中级工,
sum(case when 聘任职称='初级工' then 1 else 0 end) as 初级工
from
tb
where
聘任时间>=''2000-8-9' and 聘任时间<='2006-8-2'
group by
year(聘任时间)
year(聘任时间) as 年份,
sum(case 聘任职称 when '高级工' then 1 else 0 end) as '高级工',
sum(case 聘任职称 when '高级工' then 1 else 0 end) as '中级工',
sum(case 聘任职称 when '高级工' then 1 else 0 end) as '初级工'
from
tb
where
聘任时间 between @sj and @sj2
group by
year(聘任时间)
create table po (id int,人员id int,聘任时间 datetime,聘任职称 varchar(20))
insert into po select 1, 1, '1998-2-1','初级工'
insert into po select 2, 1, '2000-2-1','中级工'
insert into po select 3, 1, '2005-3-1','高级工'
insert into po select 4, 2, '2002-2-1','初级工'
insert into po select 5, 2, '2004-5-4','中级工'
go--创建存储过程
create proc wsp
@sj datetime,
@sj1 datetime
as
declare @t table (sj varchar(10))
insert into @t select datename(yy,dateadd(yy,number,@sj)) from master..spt_values where type='p'
and dateadd(yy,number,@sj) between @sj and dateadd(yy,1,@sj1)
select * into #temp from (select sj,a.* from @t left join po a on datepart(yy,聘任时间)<=sj where datepart(yy,聘任时间)<=datepart(yy,@sj1))b
select 年份=sj,
[高级工]=sum(case 聘任职称 when '高级工' then 1 else 0 end),
[中级工]=sum(case 聘任职称 when '中级工' then 1 else 0 end),
[初级工]=sum(case 聘任职称 when '初级工' then 1 else 0 end)
from
(select * from #temp a
where 聘任时间=(select top 1 聘任时间 from #temp where 人员id=a.人员id and sj=a.sj order by 聘任时间 desc))a
group by sj
goexec wsp '2000-8-9','2006-8-2'
go--结果:
年份 高级工 中级工 初级工
---------- ----------- ----------- -----------
2000 0 1 0
2001 0 1 0
2002 0 1 1
2003 0 1 1
2004 0 2 0
2005 1 1 0
2006 1 1 0
set @sj='2000-8-9'
set @sj2='2006-8-2'
select
year(聘任时间) as 年份,
sum(case 聘任职称 when '高级工' then 1 else 0 end) as '高级工',
sum(case 聘任职称 when '高级工' then 1 else 0 end) as '中级工',
sum(case 聘任职称 when '高级工' then 1 else 0 end) as '初级工'
from
tb
where
聘任时间 between @sj and @sj2
group by
year(聘任时间)
insert into po select 1, 1, '1998-2-1','初级工'
insert into po select 2, 1, '2000-2-1','中级工'
insert into po select 3, 1, '2005-3-1','高级工'
insert into po select 4, 2, '2002-2-1','初级工'
insert into po select 5, 2, '2004-5-4','中级工'
insert into po select 6, 2, '2004-5-4','中级工'
go--创建存储过程
alter proc wsp
@sj datetime,
@sj1 datetime
as
create table #t(sj varchar(10))
insert into #t select datename(yy,dateadd(yy,number,@sj)) from master..spt_values where type='p'
and dateadd(yy,number,@sj) between @sj and dateadd(yy,1,@sj1)
select 年份=sj,
[高级工]=sum(case 聘任职称 when '高级工' then 1 else 0 end),
[中级工]=sum(case 聘任职称 when '中级工' then 1 else 0 end),
[初级工]=sum(case 聘任职称 when '初级工' then 1 else 0 end)
from
#t left join po on #t.sj = convert(varchar(4) , 聘任时间 , 112)
group by sj
goexec wsp '2000-8-9','2006-8-2'
=========================================================
(7 行受影响)
年份 高级工 中级工 初级工
---------- ----------- ----------- -----------
2000 0 1 0
2001 0 0 0
2002 0 0 1
2003 0 0 0
2004 0 2 0
2005 1 0 0
2006 0 0 0(7 行受影响)
go