SQL Time Tracking Query

Question

Data:

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

Here is the query I am currently using.

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

The problem is on the multiple IN_1 entries. I would only like to track the time difference between the first IN_1 Entry, and the Following OUT_1 Entry and Ignore the IN_1 entry in between. Of course you could have 100 IN_1 but the time is only tracked from the first IN_1 to the next OUT_1.

To complicate things further there could be an IN_1, IN_2, IN_3, OUT_1, OUT_2, OUT_3 and you could enter IN_1 and Leave OUT_3 and and it would work just as it was IN_1, OUT_1.

I'm not following the "To complicate things further" bit. Also, what's your question? — lc., Nov 24 '09 at 01:51
I'm not quite clear on what you want to do, but I expect (if you are using tsql 2005+) you have to use a recursive CTE or a loop. — Hogan, Nov 24 '09 at 04:14
This is time tracking on access control. Someone could swipe a card twice on an IN_1 reader. The problem is when i run the query above i get two different DurationMins using the First and Second swipe. So instead of reporting someone was in for 1.616 mins they are in for 1.616 and 1.5 (using the example above). So the to complicate thigns further bit, that just shows that they could come in one of 3 Entry doors and leave from any door they choose. It shouldn't be a big deal, but whatever I do needs to be able to also needs to be able to work correctly in that situation. — Nick, Nov 24 '09 at 13:34
Are you using MS SQL Server 2005+? If so I will post a CTE to do it for you. — Hogan, Nov 24 '09 at 14:22

score 1 · Answer 1 · answered Nov 24 '09 at 15:30

1

Nick, the different doors are not an issue instead of using = 'IN_1' and = 'OUT_1' use like 'IN%' and like 'OUT%'

answered Nov 24 '09 at 15:30

Hogan

69,564
10
76
117

I agree.. I you need to find intervals between :IN_1 <-> OUT_3 you simply cannot do it like it is configured now. Consider also adding an extra column to signal if the row-entry is an IN or an OUT event. This way you will only need to find the next OUT row to find out the exit time, regardless of the door used. – Radu094 Nov 24 '09 at 15:34

score 1 · Accepted Answer · answered Nov 24 '09 at 16:39

Solved

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]

Gives these results:

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

This will work for all types if IN_ and OUT_

change g.[timeout] as endtime to max(g.[timeout]) as endtime and take it out of the group by statement. — Hogan, Nov 24 '09 at 17:02
Don't have time to do it now, the idea is this, use a sub-query to get next out (as above), then a sub-query to get the next in, then find the max (in terms of time) of the outs between those and you have your duration. Looks like it might take 3 sub-queries to me. My guess is iteration will be faster. — Hogan, Nov 24 '09 at 17:27
Well I am fine with taking the 1st scan on the timeout, if they bumble and scan twice on the way out, they don't get extra time. The only thing this is to counter is somone scanning more than once on the way in. The problem i had on my query is when you would add time up it had a whole new set of time. So as long as I Get the Frist "IN" scan and the time difference of the First "OUT" scan I am good. So far with my testing this works just like it should. I will keep testing to confirm. Thanks! — Nick, Nov 24 '09 at 18:57
good! If you don't need to worry about Joel's point then the query above should be perfect. Good luck. — Hogan, Nov 24 '09 at 21:26
It is thanks! No I am going to play around and try to figure out duration from specific times. — Nick, Nov 24 '09 at 21:53

score 0 · Answer 3 · answered Nov 25 '09 at 01:40

CTEs will work on SQL server 2005, 2008. The test data insert is 2008 specific.

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

SQL Time Tracking Query

3 Answers3