22,209
社区成员
发帖
与我相关
我的任务
分享
--试下
select
年月列,
sum(case when '201106' between convert(varchar(6),JoinDate,112) and convert(varchar(6),isnull(LeaveDate,'999912'),112) then 1 else 0 end) 在职人数,
count(1)-sum(case when '201106' between convert(varchar(6),JoinDate,112) and convert(varchar(6),isnull(LeaveDate,'999912'),112) then 1 else 0 end) 离职人数
from 你的表
where 年月列='201106'
group by 年月列
select
年月列,
sum(case when '201106' between JoinDate and isnull(LeaveDate,'999912') then 1 else 0 end) 在职人数,
count(1)-sum(case when '201106' between JoinDate and isnull(LeaveDate,'999912') then 1 else 0 end) 离职人数
from 你的表
where 年月列='201106'
group by 年月列
use tempdb
go
create table test
(name char(10),
sdate datetime,
edate datetime,
Ewstatus int --0:在职;1:离职
)
insert into test values('a','2011-03-01','2011-08-31',0)
insert into test values('a','2011-09-01','2999-12-31',1)
insert into test values('b','2011-03-01','2099-12-31',0)
declare @leavedate datetime='2011-06-01'--查询时间
select name ,Ewstatus
from test
where @leavedate between sdate and edate and Ewstatus=0