各位大虾:
如果我知道了人员的 【任职日期】 和【离职日期】 然后判断 某一月份的在职人数呢? 比如 1月份人数 30 二月份人数 33人 。。 做一个趋势列表 比如:已知列表
任职日期 离职日期
1998-04-01
2006-12-29
2005-01-25
2005-03-31
2008-07-01
2005-08-01
2004-08-09
2011-01-05
2005-05-25
2005-09-23
2005-05-23
2005-05-13
2008-02-25
2008-11-10
2008-07-28
2008-12-17
2009-02-23
2009-04-02
2009-04-15
2009-07-23
2010-12-06
2009-08-31
2009-09-14
2009-12-08
2010-01-15
2010-01-28
2010-02-20
2010-05-04
2008-07-08 2011-01-12
2005-04-25
2010-07-08 2011-03-31
2010-11-02
2010-08-24
2011-01-01
2005-09-19
2005-07-01
2009-11-10
2010-08-24
2003-04-30
2005-04-11
2009-07-01
2011-02-01
2010-12-20
2010-03-19 2011-01-19
2010-11-18
2008-05-19 2010-11-15
2006-04-06
2009-03-25
2009-09-02
2010-03-08
2010-07-28
1998-03-18
2011-04-01
2011-01-05
2011-01-11
2011-01-04
2010-12-01 2011-01-03
2011-03-14
2011-03-08
2011-03-15 要求效果
年度 月度 人数
2010 01 3
2010 02 6
2010 05 3
2010 06 6
2010 07 3
2010 09 3
2010 11 12
2011 01 9
2011 03 6
望高手能解答!
如果我知道了人员的 【任职日期】 和【离职日期】 然后判断 某一月份的在职人数呢? 比如 1月份人数 30 二月份人数 33人 。。 做一个趋势列表 比如:已知列表
任职日期 离职日期
1998-04-01
2006-12-29
2005-01-25
2005-03-31
2008-07-01
2005-08-01
2004-08-09
2011-01-05
2005-05-25
2005-09-23
2005-05-23
2005-05-13
2008-02-25
2008-11-10
2008-07-28
2008-12-17
2009-02-23
2009-04-02
2009-04-15
2009-07-23
2010-12-06
2009-08-31
2009-09-14
2009-12-08
2010-01-15
2010-01-28
2010-02-20
2010-05-04
2008-07-08 2011-01-12
2005-04-25
2010-07-08 2011-03-31
2010-11-02
2010-08-24
2011-01-01
2005-09-19
2005-07-01
2009-11-10
2010-08-24
2003-04-30
2005-04-11
2009-07-01
2011-02-01
2010-12-20
2010-03-19 2011-01-19
2010-11-18
2008-05-19 2010-11-15
2006-04-06
2009-03-25
2009-09-02
2010-03-08
2010-07-28
1998-03-18
2011-04-01
2011-01-05
2011-01-11
2011-01-04
2010-12-01 2011-01-03
2011-03-14
2011-03-08
2011-03-15 要求效果
年度 月度 人数
2010 01 3
2010 02 6
2010 05 3
2010 06 6
2010 07 3
2010 09 3
2010 11 12
2011 01 9
2011 03 6
望高手能解答!
from tb group by year(任职日期),month(任职日期)
declare @yf varchar(2)
set @nf='2010'
set @yf='01'
select @yf,count(*) from tb where datename(yy, 任职日期)+datename(mm, 任职日期)<=@nf+@yf
and 离职日期 is null or datename(yy, 离职日期)+datename(mm, 离职日期)>@nf+@yf
insert into tb select '1998-04-01',null union all
select '2006-12-29',null union all
select '2005-01-25',null union all
select '2005-03-31',null union all
select '2008-07-01',null union all
select '2005-08-01',null union all
select '2004-08-09',null union all
select '2011-01-05',null union all
select '2005-05-25',null union all
select '2005-09-23',null union all
select '2005-05-23',null union all
select '2005-05-13',null union all
select '2008-02-25',null union all
select '2008-11-10',null union all
select '2008-07-28',null union all
select '2008-12-17',null union all
select '2009-02-23',null union all
select '2009-04-02',null union all
select '2009-04-15',null union all
select '2009-07-23',null union all
select '2010-12-06',null union all
select '2009-08-31',null union all
select '2009-09-14',null union all
select '2009-12-08',null union all
select '2010-01-15',null union all
select '2010-01-28',null union all
select '2010-02-20',null union all
select '2010-05-04',null union all
select '2008-07-08','2011-01-12' union all
select '2005-04-25',null union all
select '2010-07-08','2011-03-31' union all
select '2010-11-02',null union all
select '2010-08-24',null union all
select '2011-01-01',null union all
select '2005-09-19',null union all
select '2005-07-01',null union all
select '2009-11-10',null union all
select '2010-08-24',null union all
select '2003-04-30',null union all
select '2005-04-11',null union all
select '2009-07-01',null union all
select '2011-02-01',null union all
select '2010-12-20',null union all
select '2010-03-19','2011-01-19' union all
select '2010-11-18',null union all
select '2008-05-19','2010-11-15' union all
select '2006-04-06',null union all
select '2009-03-25',null union all
select '2009-09-02',null union all
select '2010-03-08',null union all
select '2010-07-28',null union all
select '1998-03-18',null union all
select '2011-04-01',null union all
select '2011-01-05',null union all
select '2011-01-11',null union all
select '2011-01-04',null union all
select '2010-12-01','2011-01-03' union all
select '2011-03-14',null union all
select '2011-03-08',null union all
select '2011-03-15',null
go
;with c1 as(
select dateadd(m,number,'2010-01-01')dt from master..spt_values where type='p' and number<16
),c2 as(
select a.dt,count(*)rz from c1 a left join tb b on a.dt>=b.任职日期 group by a.dt
),c3 as(
select a.dt,count(*)lz from c1 a left join tb b on a.dt>=b.离职日期 group by a.dt
)select year(c2.dt)年,month(c2.dt)月,c2.rz-c3.lz 人数 from c2 inner join c3 on c2.dt=c3.dt
go
drop table tb
/*
年 月 人数
----------- ----------- -----------
2010 1 34
2010 2 36
2010 3 37
2010 4 39
2010 5 39
2010 6 40
2010 7 40
2010 8 42
2010 9 44
2010 10 44
2010 11 44
2010 12 47
2011 1 50
2011 2 52
2011 3 52
2011 4 55(16 行受影响)
*/
我X,人家辛辛苦苦写测试数据和结果,你TMD的来一句要oracle,彻底无语