每两行的DATEDIFF的总和，以分钟为单位

I am trying to run a query against a 3rd party employee time tracking database. As far as I can tell, they do not keep a running total on an employees time throughout the day. What I have are a series of rows containing a user ID and a time stamp. If I order the rows by the time stamp, I effectively get their history of punches. If I assume that punch 1 is in, punch 2 is out, punch 3 is in, etc etc, is there an effective method of finding the DATEDIFF in minutes from every other row and then SUM them up to get the total time for the day for that employee?

我正在尝试针对第三方员工时间跟踪数据库运行查询。据我所知,他们不会在一天的员工时间内保持总计。我所拥有的是一系列包含用户ID和时间戳的行。如果我按时间戳订购行,我会有效地获得他们的拳击历史。如果我假设冲头1在,冲头2出来,冲头3在等等,是否有一种有效的方法可以在几分钟内从每隔一行找到DATEDIFF,然后将它们相加以获得当天的总时间那个员工?

    badge_no punch_timestamp
    11209   1/31/14 7:58 AM
    11209   1/31/14 9:57 AM
    11209   1/31/14 10:00 AM
    11209   1/31/14 10:07 AM

Not 2 minutes after I posted I came across this post: SQL Server find datediff between different rows, sum

我发布后不到2分钟,我遇到了这篇文章:SQL Server在不同的行之间找到datediff,sum

I will give that a try first.

我先试试。

2 个解决方案

#1

Using @DaveZych sample data I have managed to calculated the same results as him, using the SQL statement below:

使用@DaveZych样本数据我已经设法使用下面的SQL语句计算出与他相同的结果:

;WITH DataSource ([StartOrEnd], [badge_no], [punch_timestamp]) AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY [badge_no] ORDER BY [punch_timestamp]) +
           ROW_NUMBER() OVER (PARTITION BY [badge_no] ORDER BY [punch_timestamp])  % 2
          ,[badge_no]
          ,[punch_timestamp]
    FROM #Time
),
TimesPerBadge_No ([badge_no], [StartOrEnd], [Minutes]) AS
(
    SELECT  [badge_no]
           ,[StartOrEnd] 
           ,DATEDIFF(MINUTE, MIN([punch_timestamp]), MAX([punch_timestamp]))
    FROM DataSource
    GROUP BY [badge_no]
            ,[StartOrEnd] 
)
SELECT [badge_no]
      ,SUM([Minutes])
FROM TimesPerBadge_No
GROUP BY [badge_no]

---

Here can see the values of each CTE:

这里可以看到每个CTE的值:

First, we ned to group each start and end date:

首先,我们需要对每个开始和结束日期进行分组:

 SELECT ROW_NUMBER() OVER (PARTITION BY [badge_no] ORDER BY [punch_timestamp]) +
           ROW_NUMBER() OVER (PARTITION BY [badge_no] ORDER BY [punch_timestamp])  % 2
          ,[badge_no]
          ,[punch_timestamp]
    FROM #Time

Now, we can calculate the minutes difference in each group:

现在,我们可以计算每组的分钟差异:

SELECT  [badge_no]
        ,[StartOrEnd] 
        ,DATEDIFF(MINUTE, MIN([punch_timestamp]), MAX([punch_timestamp]))
FROM DataSource
GROUP BY [badge_no]
        ,[StartOrEnd] 

and finally sumarize the minutes for each badge_no:

并最终sumarize每个badge_no的分钟:

SELECT [badge_no]
      ,SUM([Minutes])
FROM TimesPerBadge_No
GROUP BY [badge_no]

#2

Here's a relatively simple, naive way of doing it. Assuming that, like you said, each "odd" row is a stamp in and each "even" row is a stamp out, you can grab the odd and even rows separately and calculate each working block. Note that the DateDiff I used is in minutes (mi) but you can change that to hours/seconds/whatever: http://technet.microsoft.com/en-us/library/ms189794.aspx

这是一种相对简单,天真的方式。假设,就像你说的那样,每个“奇数”行都是一个标记,每个“偶数”行都是一个标记,你可以分别抓住奇数行和偶数行并计算每个工作块。请注意,我使用的DateDiff是以分钟(mi)为单位,但您可以将其更改为小时/秒/无论如何:http://technet.microsoft.com/en-us/library/ms189794.aspx

;WITH StartTime AS
(
SELECT
    badge_no,
    punch_timestamp,
    myrow
FROM
(
    SELECT
        badge_no,
        punch_timestamp,
        ROW_NUMBER() OVER (Partition BY badge_no ORDER BY punch_timestamp ASC) as myrow
    FROM #Time
) [t1]
WHERE myrow % 2 = 1 --odd rows
)
,EndTime AS
(
SELECT
    badge_no,
    punch_timestamp,
    myrow - 1 as 'myrow' --Subtract 1 to match up with the odd rows
FROM
(
    SELECT
        badge_no,
        punch_timestamp,
        ROW_NUMBER() OVER (Partition BY badge_no ORDER BY punch_timestamp ASC) as myrow
    FROM #Time
) [t1]
WHERE myrow % 2 = 0 --even rows
)

SELECT
    badge_no,
    SUM(diff) as 'MinutesWorked'
FROM 
(
SELECT
    EndTime.badge_no,
    DATEDIFF(mi, 
             (SELECT TOP 1 
                  punch_timestamp 
              FROM StartTime 
              WHERE StartTime.badge_no = EndTime.badge_no 
                  AND StartTime.myrow = EndTime.myrow), 
             EndTime.punch_timestamp) as 'diff'
FROM EndTime
) [t1]
GROUP BY badge_no

Here is the test data I used:

这是我使用的测试数据:

CREATE TABLE #Time
(
    badge_no nvarchar(10),
    punch_timestamp datetime
)

INSERT INTO #Time VALUES ('100', '2013-01-02 12:01 PM')
INSERT INTO #Time VALUES ('100', '2013-01-02 1:38 PM')
INSERT INTO #Time VALUES ('100', '2013-01-02 2:29 PM')
INSERT INTO #Time VALUES ('100', '2013-01-03 3:01 PM')
INSERT INTO #Time VALUES ('100', '2013-01-03 4:20 PM')
INSERT INTO #Time VALUES ('100', '2013-01-04 12:01 PM')
INSERT INTO #Time VALUES ('100', '2013-01-04 2:01 PM')
INSERT INTO #Time VALUES ('100', '2013-01-04 3:11 PM')
INSERT INTO #Time VALUES ('100', '2013-01-04 4:21 PM')
INSERT INTO #Time VALUES ('100', '2013-01-05 12:01 PM')
INSERT INTO #Time VALUES ('100', '2013-01-05 1:01 PM')
INSERT INTO #Time VALUES ('200', '2013-01-04 2:11 AM')
INSERT INTO #Time VALUES ('200', '2013-01-04 4:34 PM')
INSERT INTO #Time VALUES ('200', '2013-01-05 1:01 AM')
INSERT INTO #Time VALUES ('200', '2013-01-05 4:29 AM')