complex sql grouping and sorting

Question

I'm having a problem in grouping and sorting data in our daily time record to automatically produce a report. The table structure of the DTR as follows:

LogDate     LogTime     EmployeeName            LogType
2012-09-14  10:48:04    SALITA, LYNYRD ANTONIO  LOGOUT
2012-09-14  09:39:29    SALITA, LYNYRD ANTONIO  LOGOUT
2012-09-14  09:39:19    SALITA, LYNYRD ANTONIO  LOGIN
2012-09-14  09:35:25    SALITA, LYNYRD ANTONIO  LOGOUT
2012-09-14  09:35:13    SALITA, LYNYRD ANTONIO  LOGIN
2012-09-14  08:10:00    SALITA, LYNYRD ANTONIO  LOGIN
2012-09-13  17:00:00    SALITA, LYNYRD ANTONIO  LOGOUT
2012-09-13  08:05:00    SALITA, LYNYRD ANTONIO  LOGIN
2012-09-12  17:05:00    SALITA, LYNYRD ANTONIO  LOGOUT
2012-09-12  08:05:00    SALITA, LYNYRD ANTONIO  LOGIN
2012-07-10  17:00:00    MAG-ISA, MAYBELLE       LOGOUT
2012-07-10  17:00:00    BELO, RIO               LOGOUT
2012-07-10  17:00:00    CANSINO, PAUL           LOGOUT
2012-07-10  17:00:00    SALITA, LYNYRD ANTONIO  LOGOUT
2012-07-10  17:00:00    AURENO, LEAH            LOGOUT
2012-07-10  17:00:00    GARCIA, ALVIN           LOGOUT
2012-07-10  17:00:00    TARINE, KAREN           LOGOUT
2012-07-10  17:00:00    REYES, ANDREA           LOGOUT
2012-07-10  17:00:00    NAVARRO, KRISTINA       LOGOUT
2012-07-10  10:30:00    MAG-ISA, MAYBELLE       LOGIN
2012-07-10  08:00:00    SALITA, LYNYRD ANTONIO  LOGIN
2012-07-10  08:00:00    CANSINO, PAUL           LOGIN
2012-07-10  08:00:00    BELO, RIO               LOGIN
2012-07-10  07:40:00    AURENO, LEAH            LOGIN
2012-07-10  07:30:00    GARCIA, ALVIN           LOGIN
2012-07-10  07:25:00    TARINE, KAREN           LOGIN
2012-07-10  07:10:00    NAVARRO, KRISTINA       LOGIN
2012-07-10  07:10:00    REYES, ANDREA           LOGIN

with this sql:

SELECT 
    DATE_FORMAT(LogDate, '%d/%c/%Y') AS LogDate, 
    EmployeeName,
    (GROUP_Concat(CASE LogType WHEN 'LOGIN' THEN LogTime END)) AS LOGIN,
    (GROUP_Concat(CASE LogType WHEN 'LOGOUT' THEN LogTime END)) AS LOGOUT
FROM myTable
GROUP BY LogDate, EmployeeName
ORDER BY LogDate desc;

I am able to produce this result

LogDate     EmployeeName            Login                       Logout
2012-09-14  SALITA, LYNYRD ANTONIO  08:10:00,09:35:13,09:39:19  09:35:25,09:39:29,10:48:04
2012-09-13  SALITA, LYNYRD ANTONIO  08:05:00                    17:00:00
2012-09-12  SALITA, LYNYRD ANTONIO  08:05:00                    17:05:00
2012-07-10  REYES, ANDREA           07:10:00                    17:00:00
2012-07-10  NAVARRO, KRISTINA       07:10:00                    17:00:00
2012-07-10  TARINE, KAREN           07:25:00                    17:00:00
2012-07-10  GARCIA, ALVIN           07:30:00                    17:00:00
2012-07-10  AURENO, LEAH            07:40:00                    17:00:00
2012-07-10  CANSINO, PAUL           08:00:00                    17:00:00
2012-07-10  SALITA, LYNYRD ANTONIO  08:00:00                    17:00:00
2012-07-10  BELO, RIO               08:00:00                    17:00:00
2012-07-10  MAG-ISA, MAYBELLE       10:30:00                    17:00:00

Based from one of the answers this is the code:

SELECT DATE_FORMAT(t1.LogDate, '%d/%c/%Y') AS LogDate, t1.EmployeeName
     , t1.LogTime AS Login
     , ( SELECT MIN(t2.LogTime) FROM myTable t2
          WHERE t2.LogType = 'LOGOUT'
            AND t2.LogDate = t1.LogDate
            AND t2.EmployeeName = t1.EmployeeName
            AND t2.LogTime > t1.LogTime ) AS Logout
  FROM myTable t1
 WHERE t1.LogType = 'LOGIN'

and it produced this result:

LogDate     EmployeeName            Login       Logout
2012-09-14  SALITA, LYNYRD ANTONIO  08:10:00    09:35:25
2012-09-14  SALITA, LYNYRD ANTONIO  09:35:13    09:35:25
2012-09-14  SALITA, LYNYRD ANTONIO  09:39:19    09:39:29
2012-09-13  SALITA, LYNYRD ANTONIO  08:05:00    17:00:00
2012-09-12  SALITA, LYNYRD ANTONIO  08:05:00    17:05:00
2012-07-10  REYES, ANDREA           07:10:00    17:00:00
2012-07-10  NAVARRO, KRISTINA       07:10:00    17:00:00
2012-07-10  TARINE, KAREN           07:25:00    17:00:00
2012-07-10  GARCIA, ALVIN           07:30:00    17:00:00
2012-07-10  AURENO, LEAH            07:40:00    17:00:00
2012-07-10  CANSINO, PAUL           08:00:00    17:00:00
2012-07-10  SALITA, LYNYRD ANTONIO  08:00:00    17:00:00
2012-07-10  BELO, RIO               08:00:00    17:00:00
2012-07-10  MAG-ISA, MAYBELLE       10:30:00    17:00:00

Is there any a way to make the result sort like this?

LogDate     EmployeeName            Login       Logout
2012-09-14  SALITA, LYNYRD ANTONIO  08:10:00    NULL
2012-09-14  SALITA, LYNYRD ANTONIO  09:35:13    09:35:25
2012-09-14  SALITA, LYNYRD ANTONIO  09:39:19    09:39:29
2012-09-14  SALITA, LYNYRD ANTONIO  NULL        10:48:04
2012-09-13  SALITA, LYNYRD ANTONIO  08:05:00    17:00:00
2012-09-12  SALITA, LYNYRD ANTONIO  08:05:00    17:05:00
2012-07-10  REYES, ANDREA           07:10:00    17:00:00
2012-07-10  NAVARRO, KRISTINA       07:10:00    17:00:00
2012-07-10  TARINE, KAREN           07:25:00    17:00:00
2012-07-10  GARCIA, ALVIN           07:30:00    17:00:00
2012-07-10  AURENO, LEAH            07:40:00    17:00:00
2012-07-10  CANSINO, PAUL           08:00:00    17:00:00
2012-07-10  SALITA, LYNYRD ANTONIO  08:00:00    17:00:00
2012-07-10  BELO, RIO               08:00:00    17:00:00
2012-07-10  MAG-ISA, MAYBELLE       10:30:00    17:00:00

David Faber · Accepted Answer · 2012-09-14 02:32:14Z

4

Something like this:

SELECT DATE_FORMAT(t1.LogDate, '%d/%c/%Y') AS LogDate, t1.EmployeeName
     , t1.LogTime AS Login
     , ( SELECT MIN(t2.LogTime) FROM myTable t2
          WHERE t2.LogType = 'LOGOUT'
            AND t2.LogDate = t1.LogDate
            AND t2.EmployeeName = t1.EmployeeName
            AND t2.LogTime > t1.LogTime ) AS Logout
  FROM myTable t1
 WHERE t1.LogType = 'LOGIN'

No GROUP BY is really necessary since you're not actually grouping anything.

answered Sep 14, 2012 at 2:32

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kirill Leontev Over a year ago

AND t2.LogDate = t1.LogDate is probably redundant, as one might log out after midnight )

David Faber Over a year ago

Yes, as long as LogTime is a DATETIME I think you are right.

Lynyrd Salita Over a year ago

wow this one did work also! but is there a way that the unpaired logtimes will display null instead of the next logout for the unpaired login or the previous login for the unpaire logout?

David Faber Over a year ago

Can you define what you mean by an unpaired logtime? Is it possible for a user to log in, not log out, but then log in again? Or will there always be a matching logout event except when a user is actually logged in?

Lynyrd Salita Over a year ago

i just add another logout so that makes employee salita to login, then login and logout, then logout again. this produced 3 records for salita but i dont know where the system get the login for the unpaired logout, it is not the previous login. this is the result of your code with a new entry for the database which is the logout of employee salita: <pre> 2012-09-14 SALITA, LYNYRD ANTONIO 08:10:00 09:35:25 2012-09-14 SALITA, LYNYRD ANTONIO 09:35:13 09:35:25 2012-09-14 SALITA, LYNYRD ANTONIO 09:39:19 09:39:29 <pre>

|

Collectives™ on Stack Overflow

complex sql grouping and sorting

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related