利用SQL统计时间时怎么去除周末的时间（非工作日时间）,我需要的是工作时间．

[ksy616]

如下：表１
　　　　Dept_ID EmpNo Start End
A1000 C0701006 2006-10-10 2006-11-01
B2000 C0607003 2006-12-13 2006-12-29
C3000 C0501007 2007-02-02 2007-03-01
　　　
　　　　　表２
　　　　DateOfThis EventType_FK WorkingDay_Flag
......... ......... ........
2006-10-10 85 1
2006-10-11 85 1
2006-10-12 85 1
2006-10-13 85 1
＊ 2006-10-14 82 0 为星期天
＊ 2006-10-15 82 0　　　为星期天
2006-10-16 85 1
2006-10-17 85 1
2006-10-18 85 1
2006-10-19 85 1
2006-10-20 85 1
＊ 2006-10-21 82 0　　　为星期天
＊ 2006-10-22 82 0　　　为星期天
......... ......... ........

现在要求把周末的时间去掉．．该如何解决呢？？？

[leniz]

这样联立 在分组 行不行?

select EmpNo,count(*) as exp from
(
select EmpNo from
(
select *
from 表1 as t1 ,表2 as t2
) temp
where
begtime <= dateofthis and endtime >=dateofthis and workingday_flag = 1
) temp1
group by name

[ksy616]

按照你的方法做完，出了点问题

[ksy616]

怎么样可以联系你呢？我把截图发给你看看吧

[Derek_cap]

同意leniz(leniz)的做法.

[jackeyabc]

收藏先

[ksy616]

两者好象不太一样啊．．

[dawugui]

(datepart(weekday, @BegTime)+@@datefirst-1)%7 between 1 and 5

为周一至周五.

0,6为周日, 周六.

[ksy616]

我的表二中都已经把具体的星期六，天写好了，WorkingDay_Flag = 1 可以判断，
但我怎么和表一中的Begin　和 End时间结合起来呢？
DateOfThis EventType_FK WorkingDay_Flag
... ... ...
. . .
　　　　 . . .
. . .
2006-10-10 85 1
2006-10-11 85 1
2006-10-12 85 1
2006-10-13 85 1
　 2006-10-14 82 0
2006-10-15 82 0　　　
2006-10-16 85 1
2006-10-17 85 1
2006-10-18 85 1
2006-10-19 85 1
2006-10-20 85 1
2006-10-21 82 0　　　
2006-10-22 82 0　
　　　　 . . 　.
. . 　 .
. . 　.
... ... 　...

[dawugui]

你要什么结果,是减去周六周日后的天数还是......?

[ksy616]

是去掉星期六和星期天的天数，我不知道怎么做了

[yahuu]

写个函数，遍历你的记录，用乌龟给你的那个东西看他星期几，做个标记，总数-标记总数

[dawugui]

用上面的函数就可以了啊.不需要第二个表

if object_id('pubs..tb') is not null
drop table tb
go

create table tb(
Dept_ID varchar(10),
EmpNo varchar(10),
Start datetime,
tEnd datetime)

insert into tb(Dept_ID,EmpNo,Start,tEnd) values('A1000','C0701006','2006-10-10','2006-11-01')
insert into tb(Dept_ID,EmpNo,Start,tEnd) values('B2000','C0607003','2006-12-13','2006-12-29')
insert into tb(Dept_ID,EmpNo,Start,tEnd) values('C3000','C0501007','2007-02-02','2007-03-01')
go

create function fun(@BegTime datetime, @EndTime datetime)
returns int
as
begin
declare @re int
set @re=0
while @BegTime<=@EndTime
begin
select @re=@re+1 where (datepart(weekday, @BegTime)+@@datefirst-1)%7 between 1 and 5
set @BegTime=dateadd(day, 1, @BegTime)
end

return @re
end
go

select Dept_ID , EmpNo , Start,tEnd , dbo.fun(Start,tEnd) as days from tb

drop table tb

/*result
Dept_ID EmpNo Start tEnd days
---------- ---------- ----------------------- ----------------------- ----
A1000 C0701006 2006-10-10 00:00:00.000 2006-11-01 00:00:00.000 17
B2000 C0607003 2006-12-13 00:00:00.000 2006-12-29 00:00:00.000 13
C3000 C0501007 2007-02-02 00:00:00.000 2007-03-01 00:00:00.000 20

（所影响的行数为 3 行）

*/

[ksy616]

我的原始数据好象不怎么好写遍历，你有联系方式吗？我把数据发给你看看？

[ksy616]

我先试试，，谢谢你们呢

[ksy616]

可以的，那如果我要把法定假日时间也要去掉呢？该如何做呢？

[dawugui]

那就得按你的做法了.
把法定日期加到表二里面了.

[ksy616]

那该怎么解决？我刚接触ＳＱＬ　，可以帮个忙吗？

[dawugui]

将所有周六\周日\节假日放入表二中.

if object_id('pubs..tb') is not null
drop table tb
go

create table tb(
Dept_ID varchar(10),
EmpNo varchar(10),
Start datetime,
tEnd datetime)

insert into tb(Dept_ID,EmpNo,Start,tEnd) values('A1000','C0701006','2006-10-10','2006-11-01')
insert into tb(Dept_ID,EmpNo,Start,tEnd) values('B2000','C0607003','2006-12-13','2006-12-29')
insert into tb(Dept_ID,EmpNo,Start,tEnd) values('C3000','C0501007','2007-02-02','2007-03-01')
go

if object_id('pubs..tb2') is not null
drop table tb2
go

create table tb2(
DateOfThis datetime,
EventType_FK int,
WorkingDay_Flag int)

insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-10', 85, 1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-11', 85, 1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-12', 85, 1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-13', 85, 1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-14', 82, 0)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-15', 82, 0)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-16', 85, 1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-17', 85, 1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-18', 85, 1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-19', 85, 1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-20', 85, 1)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-21', 82, 0)
insert into tb2(DateOfThis,EventType_FK,WorkingDay_Flag) values('2006-10-22', 82, 0)
go

select t1.* , datediff(day,t1.Start,t1.tEnd) - t2.days as days from tb t1,
(
select a.dept_id ,
sum(case when b.DateOfThis >= a.Start and b.DateOfThis <= a.tEnd and b.WorkingDay_Flag = 0 then 1 else 0 end) as days
from tb a , tb2 b
group by a.dept_id
) t2
where t1.dept_id = t2.dept_id

--datediff(day,Start,tEnd) as days from tb

drop table tb,tb2

/*
Dept_ID EmpNo Start tEnd days
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ -----------
A1000 C0701006 2006-10-10 00:00:00.000 2006-11-01 00:00:00.000 18
B2000 C0607003 2006-12-13 00:00:00.000 2006-12-29 00:00:00.000 16
C3000 C0501007 2007-02-02 00:00:00.000 2007-03-01 00:00:00.000 27

（所影响的行数为 3 行）

*/

好象略有出入,我吃饭后看看.

[ksy616]

谢谢你了．．

[wxshzf]

mark