Requête SQL de suivi du temps

Question

Données :

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 

Voici la requête que j'utilise actuellement.

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

Le problème se situe au niveau des multiples IN_1 entrées. Je voudrais seulement suivre la différence de temps entre la première entrée et la deuxième entrée. IN_1 Entrée, et ce qui suit OUT_1 Entrée et ignorer la IN_1 entre les deux. Bien sûr, vous pourriez avoir 100 IN_1 mais le temps n'est comptabilisé qu'à partir du premier IN_1 à la prochaine OUT_1 .

Pour compliquer encore les choses, il pourrait y avoir un IN_1 , IN_2 , IN_3 , OUT_1 , OUT_2 , OUT_3 et vous pourriez entrer IN_1 et partir OUT_3 et il fonctionnerait tel quel IN_1 , OUT_1 .

Answer 1

Nick, les différentes portes ne posent pas de problème au lieu d'utiliser = 'IN_1' y = 'OUT_1' utiliser like 'IN%' y like 'OUT%'

Answer 2

Résolu

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]

Donne les résultats suivants :

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

Cela fonctionnera pour tous les types si IN_ et OUT_.

Answer 3

Les CTEs travailleront sur SQL server 2005, 2008. L'insertion des données de test est spécifique à 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