34,593
社区成员
发帖
与我相关
我的任务
分享
-- 修正一下:所有的“离职时间”字段,加上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.部门;
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.部门;
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.部门;
--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)