CODEWITHSUNDEEP
mysqlsqlsortinggrouping
Lynyrd Salita
Lynyrd Salita

Reputation: 39

complex sql grouping and sorting

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

Upvotes: 3

Views: 149

Answers (1)

David Faber
David Faber

Reputation: 12485

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.

Upvotes: 4

Related Questions