SQL时间跟踪查询

Question

数据：

EmpNumber,     TimeStamp,          AreaName  
10632,  2009-11-23 16:40:33.000,    OUT_1  
10632,  2009-11-23 16:39:03.000,    IN_1  
10632,  2009-11-23 16:38:56.000,    IN_1  
10632,  2009-11-23 15:31:51.000,    OUT_1  
10632,  2009-11-23 15:31:48.000,    IN_1  
10632,  2009-11-23 15:31:43.000,    IN_1  
10632,  2009-11-23 15:31:14.000,    OUT_1  
10632,  2009-11-23 15:31:08.000,    IN_1  
10632,  2009-11-23 15:29:18.000,    OUT_1  
10632,  2009-11-23 15:28:29.000,    IN_1  
10632,  2009-11-23 15:27:35.000,    OUT_1  
10632,  2009-11-23 15:26:35.000,    IN_1  
10632,  2009-11-23 15:22:55.000,    IN_1 

这是我当前正在使用的查询。

SELECT [EmpNumber], [TimeStamp], [AreaName], 
    DATEDIFF(second, [TimeStamp], (SELECT TOP 1 [TimeStamp] 
                                   FROM [EventTable] EV2 
                                   WHERE EV2.[TimeStamp] > EV1.[TimeStamp] 
                                   AND AreaName = 'OUT_1' 
                                   AND EV2.[EmpNumber] = EV1.[EmpNumber])
            )/60.00 DurationMins 
FROM [EventTable] EV1 
WHERE AreaName = 'IN_1' 
ORDER BY [TimeStamp] DESC

问题出在多个 IN_1 条目上。我只想跟踪第一个 IN_1 条目和后续 OUT_1 条目之间的时间差，并忽略其间的 IN_1 条目。当然，您可以有 100 个 IN_1，但仅跟踪从第一个 IN_1 到下一个 OUT_1 的时间。

更复杂的是，可能还有 IN_1、IN_2、IN_3、OUT_1、OUT_2 code>、OUT_3，您可以输入 IN_1 并离开 OUT_3，它会像 IN_1 一样工作，OUT_1。

Answer 1

已解决

declare @test table (
    ID int,
    empnumber int,
    timestamp datetime,
    areaname varchar(20)
    ) 

INSERT INTO @test VALUES (1, 10632,  '2009-11-23 16:40:33.000', 'OUT_1' ) 
INSERT INTO @test VALUES (2, 10632,  '2009-11-23 16:39:03.000', 'IN_1'  )
INSERT INTO @test VALUES (3, 10632,  '2009-11-23 16:38:56.000', 'IN_1'  )
INSERT INTO @test VALUES (4, 10632,  '2009-11-23 15:31:51.000', 'OUT_1' )
INSERT INTO @test VALUES (5, 10632,  '2009-11-23 15:31:48.000', 'IN_1'  )
INSERT INTO @test VALUES (6, 10632,  '2009-11-23 15:31:43.000', 'IN_1'  )
INSERT INTO @test VALUES (7, 10632,  '2009-11-23 15:31:14.000', 'OUT_1' )
INSERT INTO @test VALUES (8, 10632,  '2009-11-23 15:31:08.000', 'IN_1'  )
INSERT INTO @test VALUES (9, 10632,  '2009-11-23 15:29:18.000', 'OUT_1' )
INSERT INTO @test VALUES (10, 10632,  '2009-11-23 15:28:29.000',    'IN_1'  )
INSERT INTO @test VALUES (11, 10632,  '2009-11-23 15:27:35.000',    'OUT_1' )
INSERT INTO @test VALUES (12, 10632,  '2009-11-23 15:26:35.000',    'IN_1' )
INSERT INTO @test VALUES (13, 10632,  '2009-11-23 15:22:55.000',    'IN_1' )



select g.empnumber, min(g.[timestamp]) as starttime, g.[timeout] as endtime, DATEDIFF(second,min(g.[timestamp]),g.[timeout])/60 as mins
FROM
(
select empnumber, [timestamp], (
 SELECT TOP 1 s.[timestamp] FROM @test s
   WHERE  s.areaname like 'OUT%' AND s.[timestamp] > base.[timestamp]
   ORDER BY s.[timestamp] ASC) as [timeout] 
from @test base
where base.areaname like 'IN%'
) g
GROUP BY g.empnumber, g.[timeout]

给出以下结果：

empnumber starttime               endtime                 mins
10632     2009-11-23 15:22:55.000 2009-11-23 15:27:35.000 4
10632     2009-11-23 15:28:29.000 2009-11-23 15:29:18.000 0
10632     2009-11-23 15:31:08.000 2009-11-23 15:31:14.000 0
10632     2009-11-23 15:31:43.000 2009-11-23 15:31:51.000 0
10632     2009-11-23 16:38:56.000 2009-11-23 16:40:33.000 1

如果 IN_ 和 OUT_ 这将适用于所有类型

Answer 2

Nick，不同的门不是问题，而不是使用 = 'IN_1' 和 = 'OUT_1' 使用 like 'IN%' 和 <代码>如“OUT%”

Answer 3

CTE 将在 SQL Server 2005、2008 上运行。测试数据插入特定于 2008 年。

DECLARE @EventTable TABLE
    ( 
     EmpNumber int
    ,[TimeStamp] datetime
    ,AreaName varchar(5)
    )

INSERT  INTO @EventTable
        ( EmpNumber, [TimeStamp], AreaName )
VALUES
        ( 10632, '2009-11-23 16:40:33.000', 'OUT_1' )
,       ( 10632, '2009-11-23 16:39:03.000', 'IN_1' )  
,       ( 10632, '2009-11-23 16:38:56.000', 'IN_1' )  
,       ( 10632, '2009-11-23 15:31:51.000', 'OUT_1' )  
,       ( 10632, '2009-11-23 15:31:48.000', 'IN_1' )  
,       ( 10632, '2009-11-23 15:31:43.000', 'IN_1' )  
,       ( 10632, '2009-11-23 15:31:14.000', 'OUT_1' )  
,       ( 10632, '2009-11-23 15:31:08.000', 'IN_1' )  
,       ( 10632, '2009-11-23 15:29:18.000', 'OUT_1' )  
,       ( 10632, '2009-11-23 15:28:29.000', 'IN_1' )  
,       ( 10632, '2009-11-23 15:27:35.000', 'OUT_1' )  
,       ( 10632, '2009-11-23 15:26:35.000', 'IN_1' )  
,       ( 10632, '2009-11-23 15:22:55.000', 'IN_1' )  

;
WITH  cte_1 -- order by time and spilt to InTime, OutTime
        AS ( SELECT
              EmpNumber
             ,case WHEN AreaName LIKE 'IN%' THEN [TimeStamp]
                   ELSE NULL
              END AS InTime
             ,case WHEN AreaName LIKE 'OUT%' THEN [TimeStamp]
                   ELSE NULL
              END AS OutTime
             ,AreaName
             ,row_number() OVER ( ORDER BY [TimeStamp] ASC ) AS rn
             FROM
              @EventTable
           ),
      cte_2 -- mark those that repeat
        AS ( SELECT
              t.EmpNumber
             ,t.InTime
             ,t.OutTime
             ,t.AreaName
             ,t.rn
             ,case WHEN ( SELECT AreaName
                          FROM cte_1 AS x
                          WHERE x.rn = t.rn - 1
                        ) = t.AreaName THEN 1
                   ELSE 0
              END AS mrk
             FROM cte_1 AS t
           ),
      cte_3 --extract non repeats and group
        AS ( SELECT
              *
             ,row_number() OVER ( PARTITION BY AreaName ORDER BY rn ASC ) AS rn2
             FROM cte_2
             WHERE  mrk = 0
           )
  SELECT
    t1.EmpNumber
   ,t1.InTime
   ,t2.Outtime
   ,datediff(ss, t1.InTime, t2.OutTime) AS Duration
  FROM
    cte_3 AS t1
    JOIN cte_3 AS t2 ON t1.rn2 = t2.rn2
  WHERE
    t1.Intime IS NOT NULL
    AND t2.Outtime IS NOT NULL
  ORDER BY
    t1.rn