CODEWITHSUNDEEP
sqlsql-server
Panda1122
Panda1122

Reputation: 121

Separating out related records in same table in sql

I have a table. With records 

**UserLoginAuditID  EmployeeID  LoginAuditCodeID    LoginDate**
538544                      1795    96                    2013-12-19 12:26:21.187
538512                      1795    137                   2013-12-19 11:02:20.527
538458                      1795    96                    2013-12-19 09:34:50.703
538249                      1795    96                    2013-12-18 13:34:41.923
538177                      1795    96                    2013-12-18 10:32:22.087
537944                      1795    96                    2013-12-17 13:08:47.860
537889                      1795    96                    2013-12-17 10:39:07.610
536560                      1795    96                    2013-12-11 14:29:54.703
536485                      1795    137                   2013-12-11 11:21:12.737
536401                      1795    96                    2013-12-11 08:34:46.183
534430                      1795    96                    2013-12-05 11:11:39.013
533942                      1795    137                   2013-12-04 12:18:03.497

LoginAuditCodeID 96 means Login and 137 means logout.

I want corresponding separate list of login and logouts. Problem is multiple logins are present. We can consider login record which is just before logout, leaving lonely login records.

Please help me.

I want a list of corresponding log in and log out records. For ex. In my data given above,

  538512                  1795    137                   2013-12-19 11:02:20.527
  538458                  1795    96                    2013-12-19 09:34:50.703
  536485                  1795    137                   2013-12-11 11:21:12.737
  536401                  1795    96                    2013-12-11 08:34:46.183

Upvotes: 0

Views: 69

Answers (2)

Daniel B
Daniel B

Reputation: 807

You can get the corresponding login/logouts as follows:

select Login, Logout from
(
    select UserLoginAuditID as Logout,
     (select UserLoginAuditID from

        (select UserLoginAuditID, row_number() over (order by L2.LoginDate desc) rn
         from logtable L2 
         where LoginAuditCodeID = 96 
         and L2.LoginDate < L.LoginDate 
         and L2.EmployeeID = L.EmployeeID) Y where rn = 1
      ) Login

    from logtable L where LoginAuditCodeID = 137
) X where not login is null

Upvotes: 1

gvee
gvee

Reputation: 17171

I think this will give you what you want (assuming SQL Server as your DBMS):

; WITH actions AS (
  SELECT UserLoginAuditID
       , EmployeeID
       , LoginAuditCodeID
       , LoginDate
       , Row_Number() OVER (PARTITION BY EmployeeID ORDER BY LoginDate) As seq
  FROM   your_table
  WHERE  LoginAuditCodeID IN (96, 137)
)
SELECT logins.*
FROM   actions As logins
 INNER
  JOIN actions As logouts
    ON logouts.EmployeeID = logins.EmployeeID
   AND logouts.seq + 1 = logins.seq
   AND logins.LoginAuditCodeID  = 96
   AND logouts.LoginAuditCodeID = 137

Let me know how you get on.

Upvotes: 0

Related Questions