表一
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
.... .... .... .... .... ....
.... .... .... .... .... ....
.... .... .... .... .... ....
加了一些数据,出现问题呢?大家帮个忙啊
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
.... .... .... .... .... ....
.... .... .... .... .... ....
.... .... .... .... .... ....
加了一些数据,出现问题呢?大家帮个忙啊
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用这个方法好象把结果的第八条记录省掉了,,但我需要这条记录
(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', 0Create 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
*/
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这是我写的,因为实际数据和上面我举的例子数据有点出入,方法是一样的
set QUOTED_IDENTIFIER ON
goALTER 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 根据网上一例子做了些小修改的.你可以参考下.