取天数的问题。。请各位SQL高手 帮帮忙！ 急急急！在线等！

Question

-- > 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,employee_id int,employee_name varchar(20),unit_id varchar(20),unit_name varchar(20),in_unit_time datetime,out_unit_time datetime)
insert into #T
select 7,183,'张三','01','人事科','2007-03-01','2007-03-29' union all
select 9,183,'张三','03','销售部','2007-03-29','2007-06-07' union all
select 10,183,'张三','07','财务科','2007-06-07','2007-10-01' union all
select 12,183,'张三','06','采购科','2007-10-01','2007-10-10' union all
select 13,183,'张三','07','财务科','2007-10-17','2008-01-03' union all
select 14,183,'张三','04','办公室','2007-10-10','2007-10-17' union all
select 15,183,'张三','09','生产部','2008-01-03',null union all
select 16,192,'李四','01','人事科','2007-03-01','2007-10-01' union all
select 17,192,'李四','04','办公室','2007-10-01',null union all
select 18,167,'王五','07','财务科','2007-10-10',nullselect *,datediff(day,in_unit_time,isnull(out_unit_time,getdate()))days from #T
/*
7 183 张三 01 人事科 2007-03-01 00:00:00.000 2007-03-29 00:00:00.000 28
9 183 张三 03 销售部 2007-03-29 00:00:00.000 2007-06-07 00:00:00.000 70
10 183 张三 07 财务科 2007-06-07 00:00:00.000 2007-10-01 00:00:00.000 116
12 183 张三 06 采购科 2007-10-01 00:00:00.000 2007-10-10 00:00:00.000 9
13 183 张三 07 财务科 2007-10-17 00:00:00.000 2008-01-03 00:00:00.000 78
14 183 张三 04 办公室 2007-10-10 00:00:00.000 2007-10-17 00:00:00.000 7
15 183 张三 09 生产部 2008-01-03 00:00:00.000 NULL 59
16 192 李四 01 人事科 2007-03-01 00:00:00.000 2007-10-01 00:00:00.000 214
17 192 李四 04 办公室 2007-10-01 00:00:00.000 NULL 153
18 167 王五 07 财务科 2007-10-10 00:00:00.000 NULL 144
*/

Answer 1

--> Sorry, 没注意要求:
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,employee_id int,employee_name varchar(20),unit_id varchar(20),unit_name varchar(20),in_unit_time datetime,out_unit_time datetime)
insert into #T
select 7,183,'张三','01','人事科','2007-03-01','2007-03-29' union all
select 9,183,'张三','03','销售部','2007-03-29','2007-06-07' union all
select 10,183,'张三','07','财务科','2007-06-07','2007-10-01' union all
select 12,183,'张三','06','采购科','2007-10-01','2007-10-10' union all
select 13,183,'张三','07','财务科','2007-10-17','2008-01-03' union all
select 14,183,'张三','04','办公室','2007-10-10','2007-10-17' union all
select 15,183,'张三','09','生产部','2008-01-03',null union all
select 16,192,'李四','01','人事科','2007-03-01','2007-10-01' union all
select 17,192,'李四','04','办公室','2007-10-01',null union all
select 18,167,'王五','07','财务科','2007-10-10',nullif object_id('tempdb.dbo.#Int') is not null drop table #Int
select top 500 id=identity(int,39140,1) into #Int from syscolumns a, syscolumns bselect employee_id,employee_name,unit_id,unit_name,ym=convert(varchar(6),cast(b.id as datetime),112),days=count(1) from #T a cross join #Int b
where in_unit_time<=b.id and isnull(out_unit_time,getdate())>b.id
group by employee_id,employee_name,unit_id,unit_name,convert(varchar(6),cast(b.id as datetime),112)
/*
employee_id employee_name        unit_id              unit_name            ym     days
----------- -------------------- -------------------- -------------------- ------ -----------
167         王五                   07                   财务科                  200710 22
167         王五                   07                   财务科                  200711 30
167         王五                   07                   财务科                  200712 31
167         王五                   07                   财务科                  200801 31
167         王五                   07                   财务科                  200802 29
167         王五                   07                   财务科                  200803 2
183         张三                   01                   人事科                  200703 28
183         张三                   03                   销售部                  200703 3
183         张三                   03                   销售部                  200704 30
183         张三                   03                   销售部                  200705 31
183         张三                   03                   销售部                  200706 6
183         张三                   04                   办公室                  200710 7
183         张三                   06                   采购科                  200710 9
183         张三                   07                   财务科                  200706 24
183         张三                   07                   财务科                  200707 31
183         张三                   07                   财务科                  200708 31
183         张三                   07                   财务科                  200709 30
183         张三                   07                   财务科                  200710 15
183         张三                   07                   财务科                  200711 30
183         张三                   07                   财务科                  200712 31
183         张三                   07                   财务科                  200801 2
183         张三                   09                   生产部                  200801 29
183         张三                   09                   生产部                  200802 29
183         张三                   09                   生产部                  200803 2
192         李四                   01                   人事科                  200703 31
192         李四                   01                   人事科                  200704 30
192         李四                   01                   人事科                  200705 31
192         李四                   01                   人事科                  200706 30
192         李四                   01                   人事科                  200707 31
192         李四                   01                   人事科                  200708 31
192         李四                   01                   人事科                  200709 30
192         李四                   04                   办公室                  200710 31
192         李四                   04                   办公室                  200711 30
192         李四                   04                   办公室                  200712 31
192         李四                   04                   办公室                  200801 31
192         李四                   04                   办公室                  200802 29
192         李四                   04                   办公室                  200803 2
*/

Answer 2

没有问题了！我在看看。
好了 各位谢谢大家！我结贴了！
再次感谢“昨夜小楼” 
 不过还等好好看看你的SQL 语句。呵呵 

Answer 3

-- > 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,employee_id int,employee_name varchar(20),unit_id varchar(20),unit_name varchar(20),in_unit_time datetime,out_unit_time datetime)
insert into #T
select 7,183,'张三','01','人事科','2007-03-01','2007-03-29' union all
select 9,183,'张三','03','销售部','2007-03-29','2007-06-07' union all
select 10,183,'张三','07','财务科','2007-06-07','2007-10-01' union all
select 12,183,'张三','06','采购科','2007-10-01','2007-10-10' union all
select 13,183,'张三','07','财务科','2007-10-17','2008-01-03' union all
select 14,183,'张三','04','办公室','2007-10-10','2007-10-17' union all
select 15,183,'张三','09','生产部','2008-01-03',null union all
select 16,192,'李四','01','人事科','2007-03-01','2007-10-01' union all
select 17,192,'李四','04','办公室','2007-10-01',null union all
select 18,167,'王五','07','财务科','2007-10-10',nullif object_id('tempdb.dbo.#Int') is not null drop table #Int
select top 368 id=identity(int,39140,1),ym=cast(null as int),date=cast(null as datetime),weekday=cast(null as int) into #Int from syscolumns a, syscolumns b
/*
368和39140是这样得来的:
declare @Min datetime
select @Min = min(in_unit_time) from #T
select datediff(day, 0, @Min) --> 39140
select datediff(day, @Min, getdate()) + 1 --> 368
*/update #Int set date = id
update #Int set ym = convert(varchar(6),date,112), weekday=(@@datefirst-1+datepart(weekday,date))%7
/*
weekday: 星期一至六分别对应1至6,星期日=0
可以select出来看看:
select * from #Int
*/select a.employee_id,a.employee_name,a.unit_id,unit_name,b.ym,days=count(1) from #T a cross join #Int b
where a.in_unit_time<=b.id and isnull(a.out_unit_time,getdate())>b.id
and b.weekday between 1 and 5 --> 加上这个可以只统计工作日: 星期1至5
group by a.employee_id,a.employee_name,a.unit_id,a.unit_name,b.ym
order by 1,3,5