我正在一个时间表数据库。 简单来说，该TimesheetEntries表有四列
ID int (identity, 1, 1)
StaffID int
ClockedIn datetime
ClockedOut datetime
我已经要求写一份报告，显示按日期范围员工的考勤。 用户将在一个日期，报告输出时钟进出所有与会人员的时间与他们的现场时间在一起。
然而，这是它得到棘手，工作人员有时时钟输出离开现场短时间，并要求报告忽略这些（当他们离开不到2小时的部位）。
所以，让我们假设以下条目
ID StaffID ClockedIn ClockedOut
1 4 0900 1200
2 4 1330 1730
3 5 0900 1200
4 5 1409 1730
5 4 1830 1930
该报告的输出应该是
StaffID ClockedIn ClockedOut
4 0900 1930
5 0900 1200
5 1409 1730
是否有这样做没有游标的情况，甚至嵌套光标内的光标任何方式（这是我在我现在！）？ 我们现在谈论的不是巨大的数据集在这里，性能是不是一个真正的问题（这是一个报告，而不是一个生产系统），但我真的不喜欢光标如果我能避免。
谢谢
爱德华
Answer 1:
我从上面Jeremy的响应中使用的数据，但在去的问题完全不同的方式。 这使用递归CTE，我认为需要SQL Server 2005。它准确地报告结果（我相信），并报告时限和分钟的总数关闭期间记录时钟插件的数量（可超过120，因为限制仅仅是每个异地时间小于两小时）。
declare @TimeSheetEntries table
(
ID int identity not null primary key,
StaffID int not null,
ClockedIn datetime not null,
ClockedOut datetime not null
);
insert into @TimeSheetEntries
(
StaffID,
ClockedIn,
ClockedOut
)
select
4,
'2012-01-01 09:00:00',
'2012-01-01 12:00:00'
union all select
4,
'2012-01-01 13:30:00',
'2012-01-01 17:30:00'
union all select
5,
'2012-01-01 09:00:00',
'2012-01-01 12:00:00'
union all select
5,
'2012-01-01 14:09:00',
'2012-01-01 17:30:00'
union all select
4,
'2012-01-01 18:30:00',
'2012-01-01 19:30:00';
WITH ClockData AS
(
SELECT ID, StaffID, ClockedIn, ClockedOut AS EffectiveClockout, 1 AS NumClockIns, 0 AS MinutesOff
FROM @TimeSheetEntries ts
WHERE NOT EXISTS (SELECT ID FROM @TimeSheetEntries tsWhere WHERE tsWhere.ClockedOut BETWEEN DATEADD(hour, -2, ts.ClockedIn) AND ts.ClockedIn)
UNION ALL
SELECT cd.ID, cd.StaffID, cd.ClockedIn, ts.ClockedOut AS EffectiveClockout, cd.NumClockIns + 1 AS NumClockIns, cd.MinutesOff + DateDiff(minute, cd.EffectiveClockout, ts.ClockedIn) AS MinutesOff
FROM @TimeSheetEntries ts
INNER JOIN ClockData cd
ON ts.StaffID = cd.StaffID
AND ts.ClockedIn BETWEEN cd.EffectiveClockout AND dateadd(hour, 2, cd.EffectiveClockout)
)
SELECT *
FROM ClockData cd
WHERE NumClockIns = (SELECT MAX(NumClockIns) FROM ClockData WHERE ID = cd.ID)
这将返回：
ID StaffID ClockedIn EffectiveClockout NumClockIns MinutesOff
3 5 2012-01-01 09:00:00.000 2012-01-01 12:00:00.000 1 0
4 5 2012-01-01 14:09:00.000 2012-01-01 17:30:00.000 1 0
1 4 2012-01-01 09:00:00.000 2012-01-01 19:30:00.000 3 150
UPDATE
在情况下，它是不明确的，是MinutesOff只有'津贴的时间或时间“吃”的ClockedIn和EffectiveClockout之间的相同行中示出的量。 因此，5 STAFFID了129分钟关闭计时时间段之间，但没有考虑时间，所以MinutesOff为0，两行。
Answer 2:
我敢肯定有更简单的方法来做到这一点，但我可以用一对夫妇的热膨胀系数的把它关闭：
declare @TimeSheetEntries table
(
ID int identity not null primary key,
StaffID int not null,
ClockedIn datetime not null,
ClockedOut datetime not null
);
insert into @TimeSheetEntries
(
StaffID,
ClockedIn,
ClockedOut
)
select
4,
'2012-01-01 09:00:00',
'2012-01-01 12:00:00'
union all select
4,
'2012-01-01 13:30:00',
'2012-01-01 17:30:00'
union all select
5,
'2012-01-01 09:00:00',
'2012-01-01 12:00:00'
union all select
5,
'2012-01-01 14:09:00',
'2012-01-01 17:30:00'
union all select
4,
'2012-01-01 18:30:00',
'2012-01-01 19:30:00'
;
with MultiCheckins as (
select distinct
StaffID,
cast(cast(cast(ClockedIn as float) as int) as datetime) as TimeSheetDate,
rank() over (
partition by StaffID,
cast(cast(cast(ClockedIn as float) as int) as datetime)
order by ClockedIn
) as ordinal,
ClockedIn,
ClockedOut
from @TimeSheetEntries
), Organized as
(
select
row_number() over (
order by
mc.StaffID,
mc.TimeSheetDate,
mc.ClockedIn,
mc.ClockedOut
) as RowID,
mc.StaffID,
mc.TimeSheetDate,
case
when datediff(hour, coalesce(mc3.ClockedOut, mc.ClockedIn), mc.ClockedIn) >= 2
then mc.ClockedIn
else coalesce(mc3.ClockedIn, mc.ClockedIn)
end as ClockedIn,
case
when datediff(hour, mc.ClockedOut, coalesce(mc2.ClockedIn, mc.ClockedOut)) < 2
then coalesce(mc2.ClockedOut, mc.ClockedOut)
else mc.ClockedOut
end as ClockedOut
from
MultiCheckins as mc
left outer join
MultiCheckIns as mc3
on mc3.StaffID = mc.StaffID
and mc3.TimeSheetDate = mc.TimeSheetDate
and mc3.ordinal = mc.ordinal - 1
left outer join
MultiCheckIns as mc2
on mc2.StaffID = mc.StaffID
and mc2.TimeSheetDate = mc.TimeSheetDate
and mc2.ordinal = mc.ordinal + 1
)
select distinct
o.StaffID,
o.ClockedIn,
o.ClockedOut
from Organized as o
where
not exists (
select null from Organized as o2
where o2.RowID <> o.RowID
and o2.StaffID = o.StaffID
and
(
o.ClockedIn between o2.ClockedIn and o2.ClockedOut
and o.ClockedOut between o2.ClockedIn and o2.ClockedOut
)
)
Answer 3:
选项1：也许它插入到一个临时表，然后使用LEFT JOIN建立一个结果表（如果他们只能在白天两次时钟，这将，如果你有3个结果时，将无法正常工作）
select *
from timesheet ts
left join timesheet tss on tss.id = ts.id
在这之后你可以得到的最小值和最大值，甚至有一个更强大的报告。
选项2：
create #TimeTable Table (UserID int, InTime int, OutTime int)
insert into #TimeTable (UserID) select distinct StaffID
Update #TimeTable set InTime = (select Min(InTime) from #TimeTable where StaffID = s.StaffID) from #TimeTAble s
Update #TimeTable set OutTime = (Select Max(OutTime) from #TimeTable where StaffID = s.StaffID) from #TimeTable s
鉴于MROE时间我会合并这两大快速查询，但其中3位工作不担心性能。
Answer 4:
迭代基于集合的方法：
-- Sample data.
declare @TimesheetEntries as Table ( Id Int Identity, StaffId Int, ClockIn DateTime, ClockOut DateTime )
insert into @TimesheetEntries ( StaffId, ClockIn, ClockOut ) values
( 4, '2012-05-03 09:00', '2012-05-03 12:00' ),
( 4, '2012-05-03 13:30', '2012-05-03 17:30' ), -- This falls within 2 hours of the next two rows.
( 4, '2012-05-03 17:35', '2012-05-03 18:00' ),
( 4, '2012-05-03 19:00', '2012-05-03 19:30' ),
( 4, '2012-05-03 19:45', '2012-05-03 20:00' ),
( 5, '2012-05-03 09:00', '2012-05-03 12:00' ),
( 5, '2012-05-03 14:09', '2012-05-03 17:30' ),
( 6, '2012-05-03 09:00', '2012-05-03 12:00' ),
( 6, '2012-05-03 13:00', '2012-05-03 17:00' )
select Id, StaffId, ClockIn, ClockOut from @TimesheetEntries
-- Find all of the periods that need to be coalesced and start the process.
declare @Bar as Table ( Id Int Identity, StaffId Int, ClockIn DateTime, ClockOut DateTime )
insert into @Bar
select TSl.StaffId, TSl.ClockIn, TSr.ClockOut
from @TimesheetEntries as TSl inner join
-- The same staff member and the end of the left period is within two hours of the start of the right period.
@TimesheetEntries as TSr on TSr.StaffId = TSl.StaffId and DateDiff( ss, TSl.ClockOut, TSr.ClockIn ) between 0 and 7200
-- Continue coalescing periods until we run out of work.
declare @Changed as Bit = 1
while @Changed = 1
begin
set @Changed = 0
-- Coalesce periods.
update Bl
-- Take the later ClockOut time from the two rows.
set ClockOut = case when Br.ClockOut >= Bl.ClockOut then Br.ClockOut else Bl.ClockOut end
from @Bar as Bl inner join
@Bar as Br on Br.StaffId = Bl.StaffId and
-- The left row started before the right and either the right period is completely contained in the left or the right period starts within two hours of the end of the left.
Bl.ClockIn < Br.ClockIn and ( Br.ClockOut <= Bl.ClockOut or DateDiff( ss, Bl.ClockOut, Br.ClockIn ) < 7200 )
if @@RowCount > 0
set @Changed = 1
-- Delete rows where one period is completely contained in another.
delete Br
from @Bar as Bl inner join
@Bar as Br on Br.StaffId = Bl.StaffId and
( ( Bl.ClockIn < Br.ClockIn and Br.ClockOut <= Bl.ClockOut ) or ( Bl.ClockIn <= Br.ClockIn and Br.ClockOut < Bl.ClockOut ) )
if @@RowCount > 0
set @Changed = 1
end
-- Return all of the coalesced periods ...
select StaffId, ClockIn, ClockOut, 'Coalesced Periods' as [Type]
from @Bar
union all
-- ... and all of the independent periods.
select StaffId, ClockIn, ClockOut, 'Independent Period'
from @TimesheetEntries as TS
where not exists ( select 42 from @Bar where StaffId = TS.StaffId and ClockIn <= TS.ClockIn and TS.ClockOut <= ClockOut )
order by ClockIn, StaffId
我敢肯定，有一些优化，应该进行。
Answer 5:
我想你可以只用左做到这一点很容易地加入到它自身和一次性比赛。 下面是不是完全实现，但更多的概念证明的：
create table #TimeSheetEntries
(
ID int identity not null primary key,
StaffID int not null,
ClockedIn datetime not null,
ClockedOut datetime not null
);
insert into #TimeSheetEntries
(
StaffID,
ClockedIn,
ClockedOut
)
select
4,
'2012-01-01 09:00:00',
'2012-01-01 12:00:00'
union all select
4,
'2012-01-01 13:30:00',
'2012-01-01 17:30:00'
union all select
5,
'2012-01-01 09:00:00',
'2012-01-01 12:00:00'
union all select
5,
'2012-01-01 14:09:00',
'2012-01-01 17:30:00'
union all select
4,
'2012-01-01 18:30:00',
'2012-01-01 19:30:00'
union all select 4, '2012-01-01 18:30:00', '2012-01-01 19:30:00';
select * from #timesheetentries tse1
left outer join #timesheetentries tse2 on tse1.staffid = tse2.staffid
and tse2.id =
(
select MAX(ID)
from #timesheetentries ts_max
where ts_max.id < tse1.id and tse1.staffid = ts_max.staffid
)
outer apply
(
select DATEDIFF(minute, tse2.clockedout, tse1.clockedin) as BreakTime
) as breakCheck
where BreakTime > 120 or BreakTime < 0 or tse2.id is null
order by tse1.StaffID, tse1.ClockedIn
GO
drop table #timesheetentries
GO
这里的想法是，你有你原来的时间表表tse1 ，然后你做了left join到同一个时间表表，别名为tse2和匹配行时staffID是相同的， tse2.ID是最高的ID值仍不比tse1.ID 。 这显然是拙劣的形式-你可能想使用ROW_NUMBER()这个ID进行比较，通过分区和有序StaffID和你ClockedIn / ClockedOut值，因为时间可能已经输入按时间顺序排列的。
此时，从连接表的行现在包含从目前的时间表项，以及其前一个时间数据。 这意味着我们可以进行比较ClockedIn / ClockedOut连续时间输入值...使用DATEDIFF()我们可以发现的时间长度，用户一直是他们以前的客场之间Clockedout和最近ClockedIn值。 我用了一个OUTER APPLY这只是因为它使代码更干净，但你也许可以将它组装成一个子查询。
一旦我们做一个DATEDIFF()是微不足道的发现，其中一个人的情况下， BreakTime不超过120分钟的屏障，并删除这些时间表项，留在你以后的报告将只使用员工的时间表的显著行。
文章来源: Can I do this in SQL function without a cursor?
