SQL-奇数/偶数列与另一列之间的差异 [英] SQL - Difference between odd/even columns into another column

问题描述

我有一个包含以下数据的表。

I have a table which consists of the following data.

 Employee      TimeRegister1           TimeRegister2           TimeRegister3           TimeRegister4           
    77    2014-04-01 11:51:43.000 2014-04-01 14:03:52.000 2014-04-01 14:17:01.000 2014-04-01 16:01:12.000
    77    2014-04-03 09:48:33.000 2014-04-03 12:13:43.000 2014-04-03 12:22:27.000 2014-04-03 14:03:43.000
    181   2014-04-02 08:24:34.000 2014-04-02 13:01:10.000 2014-04-02 14:30:31.000 2014-04-02 18:04:04.000

我需要在另一列上写下根据每位员工/天的每对列之间的差异（奇数减去偶数）得出的总数。

What I need is to write, on another column, the total calculated from the differences between each pair of columns (odd minus even) for each employee/day.

在上面的示例中，对于 Employee 77和2014-04-01 ，它应在另一列上写出 TimeRegister 2-TimeRegister 1 ， TimeRegister 4-TimeRegister 3 。

In the example above, for Employee 77 and 2014-04-01, it should write on another column the sum of the differences between TimeRegister 2 - TimeRegister 1, TimeRegister 4 - TimeRegister 3.

类似这个应该输出（秒无关紧要，只需要小时/分钟）：

Something like this should be be output (seconds are irrelevant, just need hours/minutes):

 Employee      TimeRegister1           TimeRegister2           TimeRegister3           TimeRegister4              CALCULATEDCOL        
    77      2014-04-01 11:51:43.000 2014-04-01 14:03:52.000 2014-04-01 14:17:01.000 2014-04-01 16:01:12.000    2014-04-01 03:56:00.000

除了 TimeRegister 列之外到30（我只显示了4列，但可能会更多），因此我需要对每个奇偶对进行顺序计算，直到用完所有列。

Besides the TimeRegister Columns can be up to 30 (I just showed 4 columns but could be more) so I need a sequenced calculation for each odd/even pair until it runs out of columns.

任何帮助如何我在SQL中可以实现这一点将不胜感激。谢谢。

Any help how I can achieve this in SQL will be greatly appreciated. Thanks.

推荐答案

假设所提供的表是正确的（即不是30列），则可以通过 DATEDIFF 设置DateTime，将它们加在一起，然后将它们加回到其中一个时间的基准日期（假设所有DateTimes都在同一天）。您可以通过选择分钟作为差异/添加分辨率来消除秒数。为了处理丢失的数据，您在每个 In /中需要 ISNULL（）或 COALESCE Out 配对。

Assuming that the table presented is correct (i.e. not 30 columns), you can do this by DATEDIFFing the DateTimes, add the differences together, and then adding these back to the base date of one of the times (assuming all DateTimes are on the same day). You can eliminate the seconds by choosing minutes as your diff / add resolution. And to handle missing data, you would require ISNULL() or COALESCE on each In / Out pairing.

SELECT Employee, TimeRegister1, TimeRegister2, TimeRegister3, TimeRegister4, 
       DATEADD(mi, ISNULL(DATEDIFF(mi, TimeRegister1, TimeRegister2), 0) 
                 + ISNULL(DATEDIFF(mi, TimeRegister3, TimeRegister4), 0),
               CAST(CAST(TimeRegister1 AS DATE) AS DATETIME)) 
       as CALCULATEDCOL        
FROM TimeRegister;

根据评论，最好将 CALCULATEDCOL分开分为单独的日期和时间部分，因为按现状显示， CALCULATEDCOL 根本不代表时间点。

As per the comment, it may be better to split the CALCULATEDCOL into separate date and time components, since as it stands, CALCULATEDCOL doesn't represent a point in time at all.

SqlFiddle此处

如果实际上最多有30列，则需要对每对 In / Out 数据重复计算（并希望员工不要这样做）。每天离开建筑物超过15次！）。

If you truly have up to 30 columns, you would need to repeat the calculation for each pair of In/Out data (and hope that employees don't leave the building more than 15 times / day!).

编辑

要从原始表中执行此操作时钟输入/输出数据，假设方向是可行的（否则该数据几乎是无用的），并丢弃员工未在同一天连续输入和输出的任何数据，怎么办：

Edit
To do this from a raw table of clock in / out data, assuming that the direction is obtainable (otherwise the data is next to useless), and discarding any data where the employee hasn't clocked in and out consecutively, on the same day, how about:

WITH cte AS 
(
   SELECT Employee, 
          TimeRegister,
          Direction,
          CAST(TimeRegister AS DATE) AS TheDate,
          ROW_NUMBER() OVER (PARTITION BY Employee, CAST(TimeRegister AS DATE) 
                             ORDER BY TimeRegister ASC) AS Rnk
   FROM TimeRegister
)
SELECT 
   cur.Employee,
   DATEADD(mi, SUM(DATEDIFF(mi, cur.TimeRegister, nxt.TimeRegister)), 
           CAST(cur.TheDate AS DATETIME)) AS CALCULATEDCOL
   FROM cte cur
      INNER JOIN cte nxt
      ON cur.Employee = nxt.Employee 
         AND cur.TheDate = nxt.TheDate AND cur.Rnk + 1 = nxt.Rnk
   WHERE cur.Direction = 'I' AND nxt.Direction = 'O'
   GROUP BY cur.Employee, cur.TheDate;

SqlFiddle在这里

这篇关于SQL-奇数/偶数列与另一列之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！