Complex SQL sorting and grouping

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:

Log Date    Log Time    Employee Name           Log Type
9/13/2012   17:00:00    SALITA, LYNYRD ANTONIO  LOGOUT
9/13/2012   8:05:00     SALITA, LYNYRD ANTONIO  LOGIN
9/12/2012   17:05:00    SALITA, LYNYRD ANTONIO  LOGOUT
9/12/2012   8:05:00     SALITA, LYNYRD ANTONIO  LOGIN
7/10/2012   17:00:00    GARCIA, ALVIN           LOGOUT
7/10/2012   17:00:00    AURENO, LEAH            LOGOUT
7/10/2012   17:00:00    SALITA, LYNYRD ANTONIO  LOGOUT
7/10/2012   17:00:00    CANSINO, PAUL           LOGOUT
7/10/2012   17:00:00    BELO, RIO               LOGOUT
7/10/2012   17:00:00    MAG-ISA, MAYBELLE       LOGOUT
7/10/2012   17:00:00    TARINE, KAREN           LOGOUT
7/10/2012   17:00:00    REYES, ANDREA           LOGOUT
7/10/2012   17:00:00    NAVARRO, KRISTINA       LOGOUT
7/10/2012   10:30:00    MAG-ISA, MAYBELLE       LOGIN
7/10/2012   8:00:00     SALITA, LYNYRD ANTONIO  LOGIN
7/10/2012   8:00:00     CANSINO, PAUL           LOGIN
7/10/2012   8:00:00     BELO, RIO               LOGIN
7/10/2012   7:40:00     AURENO, LEAH            LOGIN
7/10/2012   7:30:00     GARCIA, ALVIN           LOGIN
7/10/2012   7:25:00     TARINE, KAREN           LOGIN
7/10/2012   7:10:00     NAVARRO, KRISTINA       LOGIN
7/10/2012   7:10:00     REYES, ANDREA           LOGIN

I want it to be processed as a query with an output like this:

Log Date    Employee Name           LOGIN       LOGOUT
9/13/2012   SALITA, LYNYRD ANTONIO  8:05:00     17:00:00
9/12/2012   SALITA, LYNYRD ANTONIO  8:05:00     17:05:00
7/10/2012   GARCIA, ALVIN           7:30:00     17:00:00
7/10/2012   AURENO, LEAH            7:40:00     17:00:00
7/10/2012   SALITA, LYNYRD ANTONIO  8:00:00     17:00:00
7/10/2012   CANSINO, PAUL           8:00:00     17:00:00
7/10/2012   BELO, RIO               8:00:00     17:00:00
7/10/2012   MAG-ISA, MAYBELLE       10:30:00    17:00:00
7/10/2012   TARINE, KAREN           7:25:00     17:00:00
7/10/2012   REYES, ANDREA           7:10:00     17:00:00
7/10/2012   NAVARRO, KRISTINA       7:10:00     17:00:00

How do I achieve this?

Wich RDBMS: MS SQL Server, MySQL, Postgre, Oracle? And also, what have you tried? Post a working query, no matter its erroneous, maybe you are just one step close to the solution — Yaroslav
– Yaroslav, Commented Sep 13, 2012 at 7:57

Himanshu · Accepted Answer · 2012-09-13 09:02:26Z

2

This is what you are trying to get:

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;

See this SQLFiddle

Description of the query:

Use CASE Statement to convert row to column (for Login and Logout)

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

But this will result you like in this SQLFiddle.

So you need to use GROUP_CONCAT (MySQL) to join LogTime with NULL and remove LogTime from GROUP BY clause.

edited Sep 13, 2012 at 9:02

answered Sep 13, 2012 at 8:22

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

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

4 Comments

Lynyrd Salita Over a year ago

thnx this solved the problem. I just need to calibrate the variables! thnx again!

Lynyrd Salita Over a year ago

is there a way to show only the first login and the last login?

Himanshu Over a year ago

@LynyrdSalita Can't understand your sentence show only the first login and the last login. Can you show your desired output?

Lynyrd Salita Over a year ago

ow, im sorry for the typo, that meant to be last logout

levi · Accepted Answer · 2012-09-13 08:05:44Z

0

Select  Log Date,   Employee Name,       LOGIN       LOGOUT 
    case(Log Type)
    when 'LOGIN' then Log Time AS LOGIN
    Else 
    Log Time AS LOGOUT
    End
From
YOUR_TABLE
order by Log Date desc

answered Sep 13, 2012 at 8:05

levi

3,5407 gold badges52 silver badges86 bronze badges

Collectives™ on Stack Overflow

Complex SQL sorting and grouping

2 Answers 2

See this SQLFiddle

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related