我可以在没有游标的SQL函数中这样做吗？ [英] Can I do this in SQL function without a cursor?
问题描述
我正在使用时间表数据库。简单来说，TimesheetEntries表有四列
I'm working on a timesheet database. In simple terms, the TimesheetEntries table has four columns
ID int (identity, 1, 1)
StaffID int
ClockedIn datetime
ClockedOut datetime
我被要求写一份报告按日期范围显示工作人员出勤。用户输入日期，并且报告输出所有参加的工作人员的计时进出时间及其在现场的持续时间。
I've been asked to write a report to show staff attendance by date range. The user puts in a date, and the report outputs the clocking in and out times of all attending staff members together with their duration on-site.
然而，这是工作人员有时候会离开网站很短的时间，而报告要求忽略这些（当他们离开网站不到2小时）。
However, and this is where it gets tricky, staff members sometimes clock out to leave the site for short periods, and the report is required to ignore these (when they leave the site for less than 2 hours).
所以，让我们假设以下条目：
So, let's assume the following entries
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
这样做没有光标，甚至光标嵌套在光标（这是我现在在哪里！）？我们不是在这里谈论庞大的数据集，性能并不是真正的问题（它是一个报告，而不是一个生产系统），但我真的不喜欢光标，如果我可以避免它们。
Is there any way of doing this without a cursor or even a cursor nested inside a cursor (which is where I'm at right now!)? We're not talking about huge datasets here and performance isn't really an issue (it's a report, not a production system) but I really don't like cursors if I can avoid them.
感谢
爱德华
推荐答案
来自Jeremy的回答以上的数据，但在问题上有一个完全不同的方式。这使用递归CTE，我认为需要SQL Server 2005.它报告结果准确（我相信），还报告记录在时间段内的时钟数和总分钟数（可以超过120，因为限制是每个非现场时段少于两个小时）。
I used the data from Jeremy's response above but went at the problem a whole different way. This uses a recursive CTE, which I think requires SQL Server 2005. It reports the results accurately (I believe) and also reports the number of clock-ins recorded during the timeframe and the total number of minutes off (can be more than 120 because the restriction is simply that each offsite period is less than two hours).
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之间吃掉的时间量。因此，StaffID 5在钟表时段之间关闭了129分钟，但没有余额时间，因此MinutesOff对于这两行都为0。
In case it isn't clear, the MinutesOff is only the 'allowance' time, or the amount of time 'eaten' between the ClockedIn and EffectiveClockout shown in the same row. So, StaffID 5 took 129 minutes off between clocked time periods, but no allowance time, so MinutesOff is 0 for both rows.
这篇关于我可以在没有游标的SQL函数中这样做吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
