人员编号 部门编号 入职日期 离职日期
0001 01 2002-01-01
0002 01 2002-01-01 2003-10-09
... ... ... ...
如何能得到如下数据部门编号 年份 月份 在职人数 离职人数
01 2002 01 2 0
01 2002 02 2 0
... ... ... ... ...
01 2003 09 2 0
01 2003 10 1 1
... ... ... ... ...
01 2009 12 1 0
0001 01 2002-01-01
0002 01 2002-01-01 2003-10-09
... ... ... ...
如何能得到如下数据部门编号 年份 月份 在职人数 离职人数
01 2002 01 2 0
01 2002 02 2 0
... ... ... ... ...
01 2003 09 2 0
01 2003 10 1 1
... ... ... ... ...
01 2009 12 1 0
datepart(year,入职日期)年份,
datepart(month,入职日期)月份,
sum(case when isnull(离职日期,'')='' then 1 else 0 end )在职人数,
sum(case when 离职日期!='' then 1 else 0 end )离职人数
from tb
group by
部门编号,
datepart(year,入职日期)年份,
datepart(month,入职日期)月份
datepart(year,)
datepart(month,)
sum(chinese wholesalers
ugg when isnull()='' then 1 else 0 end )
sum(case when then 1 else 0 end )
from tb
group by datepart(year,)
datepart(month,)
--> author:Ken Wong
--> Date: 2009-12-21 14:05:54
/*===============================================*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([人员编号] varchar(4),[部门编号] varchar(2),[入职日期] varchar(10),[离职日期] varchar(10))
insert [tb]
select '0001','01','2002-01-01',null union all
select '0002','01','2002-01-01','2003-10-09'select r.部门编号,
year([date]) as 年份,
right('0'+ltrim(month([date])),2) as 月份,
(select count(1) from [tb] where 部门编号=r.部门编号
and left(入职日期,7)<=convert(varchar(7),t.[date],120)
and (离职日期 is null or left(离职日期,7) > convert(varchar(7),t.[date],120))) as 在职人数,
(select count(1) from [tb] where 部门编号=r.部门编号
and left(离职日期,7)=convert(varchar(7),t.[date],120)) as 离职人数
from
(select dateadd(month,number,(select min(入职日期) from tb)) as [date]
from master..spt_values
where type = 'P'
and year(dateadd(month,number,(select min(入职日期) from tb)))<=year(getdate())
) t,(select distinct 部门编号 from tb) r---------------------------
01 2002 01 2 0
01 2002 02 2 0
01 2002 03 2 0
01 2002 04 2 0
01 2002 05 2 0
01 2002 06 2 0
01 2002 07 2 0
01 2002 08 2 0
01 2002 09 2 0
01 2002 10 2 0
01 2002 11 2 0
01 2002 12 2 0
01 2003 01 2 0
01 2003 02 2 0
01 2003 03 2 0
01 2003 04 2 0
01 2003 05 2 0
01 2003 06 2 0
01 2003 07 2 0
01 2003 08 2 0
01 2003 09 2 0
01 2003 10 1 1
01 2003 11 1 0
01 2003 12 1 0
01 2004 01 1 0
01 2004 02 1 0
01 2004 03 1 0
01 2004 04 1 0
01 2004 05 1 0
01 2004 06 1 0
01 2004 07 1 0
01 2004 08 1 0
01 2004 09 1 0
01 2004 10 1 0
01 2004 11 1 0
01 2004 12 1 0
01 2005 01 1 0
01 2005 02 1 0
01 2005 03 1 0
01 2005 04 1 0
01 2005 05 1 0
01 2005 06 1 0
01 2005 07 1 0
01 2005 08 1 0
01 2005 09 1 0
01 2005 10 1 0
01 2005 11 1 0
01 2005 12 1 0
01 2006 01 1 0
01 2006 02 1 0
01 2006 03 1 0
01 2006 04 1 0
01 2006 05 1 0
01 2006 06 1 0
01 2006 07 1 0
01 2006 08 1 0
01 2006 09 1 0
01 2006 10 1 0
01 2006 11 1 0
01 2006 12 1 0
01 2007 01 1 0
01 2007 02 1 0
01 2007 03 1 0
01 2007 04 1 0
01 2007 05 1 0
01 2007 06 1 0
01 2007 07 1 0
01 2007 08 1 0
01 2007 09 1 0
01 2007 10 1 0
01 2007 11 1 0
01 2007 12 1 0
01 2008 01 1 0
01 2008 02 1 0
01 2008 03 1 0
01 2008 04 1 0
01 2008 05 1 0
01 2008 06 1 0
01 2008 07 1 0
01 2008 08 1 0
01 2008 09 1 0
01 2008 10 1 0
01 2008 11 1 0
01 2008 12 1 0
01 2009 01 1 0
01 2009 02 1 0
01 2009 03 1 0
01 2009 04 1 0
01 2009 05 1 0
01 2009 06 1 0
01 2009 07 1 0
01 2009 08 1 0
01 2009 09 1 0
01 2009 10 1 0
01 2009 11 1 0
01 2009 12 1 0