还是利用SQL统计时间时怎么去除＂周末和假日＂的时间（非工作日时间）的问题？

[ksy616]

表一


file_uid empno deptcode timeofbegin timeofend days
.... .... .... .... .... ....
.... .... .... .... .... ....
.... .... .... .... .... ....
1001 c06001 cx100 2006-09-28 2006-10-18 20
1002 c06002 cx101 2006-09-30 2006-10-09 9
1002 c06003 cx102 2006-10-08 2006-10-18 10
1002 c06003 CX102 2006-10-19 2006-10-28 9
1002 c06004 cx103 2006-09-29 2006-10-20 21
1003 c06005 cx104 2006-10-10 2006-10-16 6
1003 c06005 cx104 2006-10-18 2006-10-26 8
1004 c06006 cx105 2006-10-09 2006-10-09 0
.... .... .... .... .... ....
.... .... .... .... .... ....
.... .... .... .... .... ....


***注意点：（第三条记录和第四条记录，第六条记录和第七条记录）是同一个人，
同一份文件，不同时间段的天数



表二 Calendar.dbo

DateOfThis EventType_FK WorkingDay_Flag
... ... ...
... ... ...
... ... ...
... ... ...
2006-09-28 85 1
2006-09-29 85 1
2006-09-30 82 1
2006-10-01 82 0
2006-10-02 82 0
2006-10-03 82 0
2006-10-04 82 0
2006-10-05 82 0
2006-10-06 82 0
2006-10-07 82 0
2006-10-08 85 1
2006-10-09 85 1
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　
2006-10-23 82 1　　　
2006-10-24 82 1　
2006-10-25 82 1　　　
2006-10-26 82 1　
2006-10-27 82 1　　　
2006-10-28 82 0
2006-10-29 82 0　　　
2006-10-30 82 1　
... ... ...
... ... ...
... ... ...
... ... ...
其中 2006-10-01 到 2006-10-07 为放假日期，
WorkingDay_Flag 为0 表示 周末和假期



结果应该为下表：

file_uid empno deptcode timeofbegin timeofend days 实际天数
.... .... .... .... .... .... ....
.... .... .... .... .... .... ....
.... .... .... .... .... .... ....
1001 c06001 cx100 2006-09-28 2006-10-18 20 13
1002 c06002 cx101 2006-09-30 2006-10-09 9 2
1002 c06003 cx102 2006-10-08 2006-10-18 10 8
1002 c06003 CX102 2006-10-19 2006-10-28 9 6
1002 c06004 cx103 2006-09-29 2006-10-20 21 12
1003 c06005 cx104 2006-10-10 2006-10-16 6 4
1003 c06005 cx104 2006-10-18 2006-10-26 8 6
1004 c06006 cx105 2006-10-09 2006-10-09 0 0
.... .... .... .... .... ....
.... .... .... .... .... ....
.... .... .... .... .... ....


　　加了一些数据，出现问题呢？大家帮个忙啊

[ksy616]

Select
A.file_uid,
A.empno,
A.deptcode,
A.timeofbegin,
A.timeofend,
Count(*) - 1 As days
From
表一 A
Inner Join
表二 B
On DateOfThis Between A.timeofbegin And A.timeofend And WorkingDay_Flag = 1
Group By A.file_uid, A.empno, A.deptcode, A.timeofbegin, A.timeofend

用这个方法好象把结果的第八条记录省掉了，，但我需要这条记录

[paoluo]

Create Table 表一
(file_uid Char(4),
empno Varchar(10),
deptcode Varchar(10),
timeofbegin Varchar(10),
timeofend Varchar(10),
days Int)
Insert 表一 Select '1001', 'c06001', 'cx100', '2006-09-28', '2006-10-18', 20
Union All Select '1002', 'c06002', 'cx101', '2006-09-30', '2006-10-09', 9
Union All Select '1002', 'c06003', 'cx102', '2006-10-08', '2006-10-18', 10
Union All Select '1002', 'c06003', 'cx102', '2006-10-19', '2006-10-28', 9
Union All Select '1002', 'c06004', 'cx103', '2006-09-29', '2006-10-20', 21
Union All Select '1003', 'c06005', 'cx104', '2006-10-10', '2006-10-16', 6
Union All Select '1003', 'c06005', 'cx104', '2006-10-18', '2006-10-26', 8
Union All Select '1004', 'c06006', 'cx105', '2006-10-09', '2006-10-09', 0

Create Table 表二
(DateOfThis Varchar(10),
EventType_FK Int,
WorkingDay_Flag Bit)
Insert 表二 Select '2006-09-28', 85, 1
Union All Select '2006-09-29', 85, 1
Union All Select '2006-09-30', 82, 1
Union All Select '2006-10-01', 82, 0
Union All Select '2006-10-02', 82, 0
Union All Select '2006-10-03', 82, 0
Union All Select '2006-10-04', 82, 0
Union All Select '2006-10-05', 82, 0
Union All Select '2006-10-06', 82, 0
Union All Select '2006-10-07', 82, 0
Union All Select '2006-10-08', 85, 1
Union All Select '2006-10-09', 85, 1
Union All Select '2006-10-10', 85, 1
Union All Select '2006-10-11', 85, 1
Union All Select '2006-10-12', 85, 1
Union All Select '2006-10-13', 85, 1
Union All Select '2006-10-14', 82, 0
Union All Select '2006-10-15', 82, 0
Union All Select '2006-10-16', 85, 1
Union All Select '2006-10-17', 85, 1
Union All Select '2006-10-18', 85, 1
Union All Select '2006-10-19', 85, 1
Union All Select '2006-10-20', 85, 1
Union All Select '2006-10-21', 82, 0
Union All Select '2006-10-22', 82, 0
Union All Select '2006-10-23', 82, 1
Union All Select '2006-10-24', 82, 1
Union All Select '2006-10-25', 82, 1
Union All Select '2006-10-26', 82, 1
Union All Select '2006-10-27', 82, 1
Union All Select '2006-10-28', 82, 0
Union All Select '2006-10-29', 82, 0
Union All Select '2006-10-30', 82, 1
GO
Select
A.file_uid,
A.empno,
A.deptcode,
A.timeofbegin,
A.timeofend,
days,
Count(*) - 1 As 实际天数
From
表一 A
Inner Join
表二 B
On DateOfThis Between A.timeofbegin And A.timeofend And WorkingDay_Flag = 1
Group By A.file_uid, A.empno, A.deptcode, A.timeofbegin, A.timeofend, A.days
GO
Drop Table 表一, 表二
--Result
/*
file_uid empno deptcode timeofbegin timeofend days 实际天数
1001 c06001 cx100 2006-09-28 2006-10-18 20 11
1002 c06002 cx101 2006-09-30 2006-10-09 9 2
1002 c06003 cx102 2006-10-08 2006-10-18 10 8
1002 c06003 cx102 2006-10-19 2006-10-28 9 6
1002 c06004 cx103 2006-09-29 2006-10-20 21 12
1003 c06005 cx104 2006-10-10 2006-10-16 6 4
1003 c06005 cx104 2006-10-18 2006-10-26 8 6
1004 c06006 cx105 2006-10-09 2006-10-09 0 0
*/

[paoluo]

沒有問題啊，我的語句沒有丟掉最後一條紀錄.

另外，第一條的實際天數只有11天。

[ksy616]

我的是不显示的啊，就少了第八条．．

[paoluo]

你直接復制我那段代碼試試看

[ksy616]

他是把 days 为0 的数据全部去掉了

[paoluo]

不可能啊，我沒有加這個限制的。

你是不是在這個代碼的基礎上加了什麼代碼？最好能貼出你的實際代碼，下午我再來看看。

[ksy616]

估计我没把那些特殊情况的数据写出来，，我用的实际数据和上面给的好象有点出入？我可以把数据发给你，，你试试看？可以吗？

[ksy616]

我的油箱　　[e-mail]

[ksy616]

SELECT
UID, TID, EmpNo, EmpName, DeptCode, TimeOfBegin, TimeOfEnd, DayOfSignOff,
COUNT(*)-1 AS 实际天数
FROM
DCC_I_DeptSignDetail A
INNER JOIN
dbo.HR_I_Calendar B
ON DateOfThis >= TimeOfBegin AND DateOfThis <= TimeOfEnd AND WorkingDayFlag = 1
GROUP BY UID, TID, EmpNo, EmpName, DeptCode, TimeOfBegin, TimeOfEnd, DayOfSignOff

这是我写的，因为实际数据和上面我举的例子数据有点出入，方法是一样的

[naturalth]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER function [dbo].[f_workday]
-- select [dbo].[f_workday]('2007-02-01 00:00:00.000','2007-02-01 01:00:00.000',54)
( @dt_begin datetime, ---起始时间
@dt_end datetime , --结束时间
@comptype int ----0不区分工作日,1区分工作日
) returns int
as
begin

declare @workday int,@i int,@bz bit,@dt datetime

if @comptype=53--区分
begin
declare @ldt_begin datetime, @ldt_end datetime
set @ldt_begin= @dt_begin;
set @ldt_end=@dt_end;

set @dt_begin=convert(varchar(10),@dt_begin,120)
set @dt_end=convert(varchar(10),@dt_end,120)

set @dt_begin = dateadd(day, case when datepart(weekday, @dt_begin) % 7 <= 1 then 2 - datepart(weekday, @dt_begin) % 7 else 0 end, @dt_begin)
set @dt_end = dateadd(day, case when datepart(weekday, @dt_end) % 7 <= 1 then -1 - datepart(weekday, @dt_end) % 7 else 0 end, @dt_end)
set @workday = datediff(hour,@dt_begin,@dt_end) / 24 - datediff(week,@dt_begin,@dt_end) * 2
set @workday=(@workday)*24-datepart(hh,@ldt_begin)+datepart(hh,@ldt_end)
if @dt_begin<='1989-12-31' or @dt_end<='1989-12-31'
set @workday=0

-- IF @ldt_begin=@ldt_end
-- SET @workday=0;
end
else

begin--不区分工作日

set @workday=datediff(day,@dt_begin,@dt_end)*24- datepart(hh,@dt_begin)+datepart(hh,@dt_end)

set @bz=0
end
return(case when @bz=1 then -@workday else @workday end)
end



根据网上一例子做了些小修改的.你可以参考下.