SQL Server在不同行之间查找日期差异

Question

我正在尝试构建一个查询来分析我们的时间跟踪系统中的数据。用户每次打卡输入或输出时，都会生成一行记录打卡时间。因此，如果您在9:00打卡上班，在5:00打卡下班，那么就有两行记录了相应的日期戳。我需要一个查询，它将迭代在基本上workingpunch_ts (时间戳列)之间的日期差小时的行。

每一行都有一个标识符，表示打孔是打孔还是打孔(inout_id，1表示输入，2表示输出)。

例如，如果你有

ID  | workingpunch_ts         | inout_id
----------------------------------------------
123 | 2011-02-16 09:00:00.000 | 1
124 | 2011-02-16 17:00:00.000 | 2

这将产生一个8小时。现在，我只需要对表中的每一对行重复该过程。

对如何实现这一点有什么想法？

Answer 1

在几个小时内，当然

select empid, cast(datediff(d,0,workingpunch_ts) as datetime),
    SUM(case when inout_id = 2 then 1 else -1 end *
    datediff(MI, datediff(d,0,workingpunch_ts), workingpunch_ts))/60.0 as Hours
from clock
where workingpunch_ts between '20110201' and '20110228 23:59:59.999'
group by empid, datediff(d,0,workingpunch_ts)

只要输入和输出是配对的，你就可以添加所有的输出并删除所有的输入，例如

 - IN  (9)
 + OUT (12)
 - IN  (13:15)
 + OUT (17)

主代码在第二行和第三行

datediff-datediff计算出每个workingpunch_ts从午夜开始的分钟数，如果是打卡，则使用CASE inout_id语句将其设为负值。

其他的是为现实生活场景添加的，其中您需要在日期范围内按员工和日期分组。

Answer 2

如果人们在同一天多次打卡进出，此查询将给您带来问题：

表模式：

CREATE TABLE [dbo].[TimePunch](
    [TimeCardID] [int] IDENTITY(1,1) NOT NULL,
    [PunchTime] [datetime] NOT NULL,
    [InOrOut] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [DayofPunch] [datetime] NOT NULL,
 CONSTRAINT [PK_TimePunch] PRIMARY KEY CLUSTERED 
(
    [TimeCardID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]

查询：

select 
    tIn.UserID,
    tIn.DayOfPunch,
    DateDiff(Hour, tIn.PunchTime, tOut.PunchTime) as HoursWorked
FROM
    TimePunch tIn,
    TimePunch tOut
WHERE
    tIn.InOrOut = 1
AND tOut.InOrOut = 2
AND tIn.UserID = tOut.UserID
AND tIn.DayofPunch = tOut.DayOfPunch