计算TimeStamps之间长度的MSSQL时间

Question

我正试图从一个时间钟的数据库中计算工作时数。我想出的这个查询有几个问题，我想不出来。

1)如果一个用户没有在一天内下班，它认为该用户一直工作到时钟进来。如果用户忘记时钟，则应将时钟从查询中丢弃。

2)查询速度慢。有什么更好的方法来加快速度吗？

WITH AUXILIERY_TBL AS (
SELECT [First Name],Checktime,ROW_NUMBER() OVER (ORDER BY CheckTime ASC) AS Ordr
From Clock_Data 
LEFT JOIN Employees ON Employees.[ID Number] = Clock_Data.UserId 
    WHERE Employees.[First Name]='Hogen' AND Year(CheckTime) > YEAR(GetDate()) - 6 
    )
SELECT * FROM(
        SELECT 
         A.Ordr Number,a.[First Name],A.Checktime ct ,B.Checktime ct2,(DATEDIFF(MINUTE ,A.Checktime ,B.Checktime)/60) AS Hours
        FROM AUXILIERY_TBL AS A
        LEFT JOIN AUXILIERY_TBL AS B
               ON (A.Ordr=(B.Ordr-1))
   )c
WHERE c.Number % 2 <> 0

原始数据如下所示：

HOGEN                   2013-10-28 09:30:00
HOGEN                   2013-10-28 13:30:00
HOGEN                   2013-10-28 14:00:00
HOGEN                   2013-10-28 18:00:00
HOGEN                   2013-10-29 09:31:00
HOGEN                   2013-10-29 14:17:00
HOGEN                   2013-10-29 18:00:00
HOGEN                   2013-10-30 09:59:00
HOGEN                   2013-10-30 14:06:00
HOGEN                   2013-10-30 14:37:00
HOGEN                   2013-10-30 18:10:00

c.f.SqlFiddle

Answer 1

您实际上需要的是类似于LAG或LEAD解析函数的SQLServer 2012+，它根据指定的分区和顺序从相同的select结果N行向上或向下获取列。

在我的解决方案中，我循环遍历Clock_Data表，假设时间是签出时间，然后模拟一个带有相关子查询的LAG函数，以便根据每个签出时间获得员工签入时间。当然，并不是所有的时间都是签出的，所以我在子查询中添加了一个HAVING条件，以确定在同一天签出之前，选中的签入时间是否是一个奇怪的行。如果是，则返回时间，如果不返回null，则可以在之后跳过这些行。

请参阅SQL，请尽量不要使用带有空格字符的列名.至少，使用下划线字符：

select 
  ep.[First Name],
  z.CheckIn,
  z.CheckTime as CheckOut,
  DATEDIFF(MINUTE, z.CheckIn, z.CheckTime)/60 AS Hours
from
(
  select 
    cd.*, 
    (
      -- simulating a LAG(CheckTime, 1, null) with this subquery
      select max(CheckTime)
      from Clock_Data
      where 1=1
        -- select only times from the same employee
        and UserID = cd.UserID
        -- select only times before the checkout time (cd.CheckTime)
        and CheckTime <  cd.CheckTime
        -- select only times from the same year/month/day
        and CheckTime >= convert(date, cd.CheckTime)
      group by UserID
      -- as we're selecting the max(CheckTime) before a check out time
      -- in the same day, we're selecting the immediatelly previous row/time
      -- but this subquery must select an odd number of rows
      -- to ensure the max(CheckTime) is a check in time
      -- since the check in always occurs first, before a check out
      -- if it's an even number of rows, null is returned
      having count(*) % 2 = 1
    ) as checkIn
  from Clock_Data cd
) z
inner join Employees ep on 1=1
  and ep.[ID Number] = z.UserID
  -- and ep.[First Name] = 'Hogen'
where 1=1
  and z.checkIn is not null