统计人数---在线等啊

Question

我有一个职位表（cmsmv）
员工编号  部门 入职时间  离职时间(离职空值即没有请辞)
  123      00  20050203  20070101
  124      00  20071231  20080504
  125      00  20070131  20070805
  126      00  20070205  
  127      01  20080304  20090101

我要得到的查询结果是
  部门   年份  1月份  2月份  3月份  ...  年总人数
   00    2007    0      2      2    ...      2    
   00    2008    2      2      3    ...      3    
   00    2009    1      1      1    ...      1    
   01    2007    0      2      2    ...      2    
。。
根据入职时间和离职时间统计每个月和每一年的在职员工人数
条件：入职时间如果为20071231则不计入07年，离职时间为20080101则不计入08年数据
我现在要统计07---09年之间的数据。
在线等啊我应该如何写条件啊？？头大。泪奔

Answer 1

select datepart(yyyy,入职时间),datepart(mm,入职时间),count(员工编号) from TB
where  datepart(yyyy,入职时间) between '2007' and '2009'
and 离职时间<>''
group by datepart(yyyy,离职时间)，datepart(mm,入职时间)不知所谓。。先这样写写。。

Answer 2

select a.年,a.月,a.员工数-b.员工数
(select datepart(yyyy,入职时间) 年,datepart(mm,入职时间)月,count(员工编号)员工数 from TB
where datepart(yyyy,入职时间) between '2007' and '2009'
and 离职时间<>''
group by datepart(yyyy,入职时间)，datepart(mm,入职时间))a,(select datepart(yyyy,入职时间),datepart(mm,入职时间),count(员工编号) from tb where left(入职时间,4)='1231' and 离职时间<>''
group by datepart(yyyy,入职时间) between '2007' and '2009')b 
where a.datepart(yyyy,入职时间)=b.datepart(yyyy,入职时间) and a.datepart(mm,入职时间)=b.datepart(mm,入职时间)

Answer 3

--07年
SELECT LEFT(部门,3) AS '部门',(case LEFT(入职时间,4) WHEN '2007' THEN '2007' ELSE '2007' END) AS '07年',COUNT(MV001) AS '人数' FROM CMSMV
WHERE 入职时间< '20071231' AND (离职时间 > '20070101' OR 离职时间='' ) AND 离职时间<>入职时间
GROUP BY LEFT(入职时间,4),LEFT(部门,3)
ORDER BY LEFT(入职时间,4),LEFT(部门,3)
--08年
SELECT LEFT(部门,3) AS '部门',(case LEFT(入职时间,4) WHEN '2008' THEN '2008' ELSE '2008' END) AS '08年',COUNT(MV001) AS '人数' FROM CMSMV
WHERE 入职时间< '20081231' AND (离职时间 > '20080101' OR 离职时间='' ) AND 离职时间<>入职时间
GROUP BY LEFT(入职时间,4),LEFT(部门,3)
ORDER BY LEFT(入职时间,4),LEFT(部门,3)
--09年
SELECT LEFT(部门,3) AS '部门',(case LEFT(入职时间,4) WHEN '2009' THEN '2009' ELSE '2009' END) AS '09年',COUNT(MV001) AS '人数' FROM CMSMV
WHERE 入职时间< '20091231' AND (离职时间 > '20090101' OR 离职时间='' ) AND 离职时间<>入职时间
GROUP BY LEFT(入职时间,4),LEFT(部门,3)
ORDER BY LEFT(入职时间,4),LEFT(部门,3)

Answer 4

CREATE TABLE cmsmv(
员工编号 INT,
部门 varchar(10),
入职时间 datetime,
离职时间 datetime
);
select * from cmsmv;insert into cmsmv(员工编号,部门,入职时间,离职时间) values(123,'00','20050203','20070101');
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(124,'00','20071231','20080504');
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(125,'00','20070131','20070805');
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(126,'00','20070205',null);
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(127,'01','20080304','20090101');select * from cmsmv;SELECT c1.部门, t1.years as '年份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-01'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-01'
                 then 1 else 0 end ) AS '1月份',       
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-02'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-02'
                 then 1 else 0 end ) AS '2月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-03'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-03'
                 then 1 else 0 end ) AS '3月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-04'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-04'
                 then 1 else 0 end ) AS '4月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-05'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-05'
                 then 1 else 0 end ) AS '5月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-06'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-06'
                 then 1 else 0 end ) AS '6月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-07'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-07'
                 then 1 else 0 end ) AS '7月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-08'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-08'
                 then 1 else 0 end ) AS '8月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-09'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-09'
                 then 1 else 0 end ) AS '9月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-10'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-10'
                 then 1 else 0 end ) AS '10月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-11'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-11'
                 then 1 else 0 end ) AS '11月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-12'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-12'
                 then 1 else 0 end ) AS '12月份'
FROM cmsmv c1 join (select 2007 as years union all select 2008 union all select 2009 ) t1 
  on t1.years >= year(isnull(c1.入职时间,getdate()))
 and t1.years <= year(isnull(c1.离职时间,getdate()))
group by c1.部门, t1.years
order by t1.years, c1.部门;

Answer 5

CREATE TABLE cmsmv(
员工编号 INT,
部门 varchar(10),
入职时间 datetime,
离职时间 datetime
);
select * from cmsmv;insert into cmsmv(员工编号,部门,入职时间,离职时间) values(123,'00','20050203','20070101');
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(124,'00','20071231','20080504');
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(125,'00','20070131','20070805');
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(126,'00','20070205',null);
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(127,'01','20080304','20090101');select * from cmsmv;SELECT c1.部门, t1.years as '年份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-01'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-01'
 and day(isnull(c1.离职时间,(case when day(getdate())=1 then getdate()+1 else getdate() end)))<>1 -- 考虑你的特殊情况
                 then 1 else 0 end ) AS '1月份',       
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-02'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-02'
                 then 1 else 0 end ) AS '2月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-03'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-03'
                 then 1 else 0 end ) AS '3月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-04'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-04'
                 then 1 else 0 end ) AS '4月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-05'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-05'
                 then 1 else 0 end ) AS '5月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-06'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-06'
                 then 1 else 0 end ) AS '6月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-07'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-07'
                 then 1 else 0 end ) AS '7月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-08'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-08'
                 then 1 else 0 end ) AS '8月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-09'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-09'
                 then 1 else 0 end ) AS '9月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-10'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-10'
                 then 1 else 0 end ) AS '10月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-11'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-11'
                 then 1 else 0 end ) AS '11月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-12'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-12'
 and day(isnull(c1.离职时间,(case when day(getdate())=31 then getdate()+1 else getdate() end)))<>31 -- 考虑你的特殊情况
                 then 1 else 0 end ) AS '12月份'
FROM cmsmv c1 join (select 2007 as years union all select 2008 union all select 2009 ) t1 
  on t1.years >= year(isnull(c1.入职时间,getdate()))
 and t1.years <= year(isnull(c1.离职时间,getdate()))
group by c1.部门, t1.years
order by t1.years, c1.部门;

Answer 6

-- 修正一下：所有的“离职时间”字段，加上isnull()函数CREATE TABLE cmsmv(
员工编号 INT,
部门 varchar(10),
入职时间 datetime,
离职时间 datetime
);
select * from cmsmv;insert into cmsmv(员工编号,部门,入职时间,离职时间) values(123,'00','20050203','20070101');
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(124,'00','20071231','20080504');
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(125,'00','20070131','20070805');
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(126,'00','20070205',null);
insert into cmsmv(员工编号,部门,入职时间,离职时间) values(127,'01','20080304','20090101');select * from cmsmv;SELECT c1.部门, t1.years as '年份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-01'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-01'
 and day(isnull(c1.离职时间,(case when day(getdate())=1 then getdate()+1 else getdate() end)))<>1 -- 考虑你的特殊情况
                 then 1 else 0 end ) AS '1月份',       
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-02'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-02'
                 then 1 else 0 end ) AS '2月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-03'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-03'
                 then 1 else 0 end ) AS '3月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-04'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-04'
                 then 1 else 0 end ) AS '4月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-05'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-05'
                 then 1 else 0 end ) AS '5月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-06'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-06'
                 then 1 else 0 end ) AS '6月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-07'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-07'
                 then 1 else 0 end ) AS '7月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-08'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-08'
                 then 1 else 0 end ) AS '8月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-09'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-09'
                 then 1 else 0 end ) AS '9月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-10'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-10'
                 then 1 else 0 end ) AS '10月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-11'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-11'
                 then 1 else 0 end ) AS '11月份',
       sum(case when convert(varchar(7),c1.入职时间,120)<=convert(varchar(7),t1.years)+'-12'
                 and convert(varchar(7),isnull(c1.离职时间,getdate()),120)>=convert(varchar(7),t1.years)+'-12'
 and day(isnull(c1.离职时间,(case when day(getdate())=31 then getdate()+1 else getdate() end)))<>31 -- 考虑你的特殊情况
                 then 1 else 0 end ) AS '12月份'
FROM cmsmv c1 join (select 2007 as years union all select 2008 union all select 2009 ) t1 
  on t1.years >= year(c1.入职时间)
 and t1.years <= year(isnull(c1.离职时间,getdate()))
group by c1.部门, t1.years
order by t1.years, c1.部门;