SQL Server Query Question

Question

Running SQL Server 2008, and I am definitely a new SQL user.

I have a table that has 4 columns:

EmpNum, User, Action, Updatetime

A user logs into, and out of a system, it is registered in the database. For example, if user1 logs into the system, then out 5 minutes later, a simple query (select * from update) would look like:

EmpNum   User    Action   Updatetime
1        User1   I        2010-01-01 23:00:00:000
1        User1   O        2010-01-01 23:05:00:000

I'm trying to query the Empnum, User, Action, I(in time), O(out time), and the total time.

Answer 1

Select T1.EmpNum, T1.User, T1.UpdateTime As TimeIn
 , (Select Min(T2.UpdateTime)
      From Table As T2
      Where T2.EmpNum = T1.EmpNum
      And T2.User = T1.User
      And T2.Action = 'O'
 And T2.UpdateTime > T1.UpdateTime) As TimeOut
From Table As T1
Where Action = 'I'

EDIT In your comments you asked for TimeWorked. Since you are using SQL Server 2008, you can use an Outer Apply to calculate the out-time for each in-time row. Then it is a simple matter of using DateDiff.

Select T1.EmpNum, T1.User
    , T1.UpdateTime As TimeIn
    , TimeOut.UpdateTime As TimeOut
    , DateDiff(mi, T1.UpdateTime, TimeOut.UpdateTime) As TimeWorkedInMinutes
From Table As T1
    Outer Apply (
                Select Min(T2.UpdateTime) As UpdateTime
                From Table As T2
                Where T2.EmpNum = T1.EmpNum
                    And T2.User = T1.User
                    And T2.Action = 'O'
                    And T2.UpdateTime > T1.UpdateTime
                ) As TimeOut
Where Action = 'I'

Answer 2

You could try something like

DECLARE @Table TABLE(
        EmpNum Int,
        [User] VARCHAR(10),
        Action VARCHAR(1),
        Updatetime DATETIME
)

INSERT INTO @Table SELECT 1,'User1','I','2010-01-01 23:00:00:000' 
INSERT INTO @Table SELECT 1,'User1','O','2010-01-01 23:05:00:000'
INSERT INTO @Table SELECT 1,'User1','I','2010-01-01 23:10:00:000' 
INSERT INTO @Table SELECT 1,'User1','O','2010-01-01 23:25:00:000'

SELECT  *,
        DATEDIFF(mi, InTime, OutTime) Period
FROM    (
            SELECT  EmpNum,
                    [User],
                    UpdateTime InTime,
                    (   SELECT  TOP 1 
                                Updatetime 
                        FROM    @Table 
                        WHERE   EmpNum = t.EmpNum 
                        AND     Action = 'O' 
                        AND     Updatetime > t.Updatetime 
                        ORDER BY Updatetime
                    ) OutTime
            FROM    @Table t
            WHERE   Action = 'I'
        ) sub

Output

EmpNum      User       InTime                  OutTime                 Period
----------- ---------- ----------------------- ----------------------- -----------
1           User1      2010-01-01 23:00:00.000 2010-01-01 23:05:00.000 5
1           User1      2010-01-01 23:10:00.000 2010-01-01 23:25:00.000 15