CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
MontyPython
MontyPython

Reputation: 2994

How to extract minimum and maximum time from a table in Oracle?

In a table that contains the Employee ID of every employee, the login time and the logout time for each employee, I need to extract the minimum from login time and maximum from logout time for each day. The problem is that every day an employee can login and logout multiple times. So, we have sample data that looks something like this,

 NAME     EMPID         LOGIN TIME         LOGOUT TIME 
user1       37      16-JAN-12 03.07.37  16-JAN-12 03.07.44
user5       21      16-JAN-12 02.00.36  16-JAN-12 04.45.34
user3       12      16-JAN-12 05.35.35  16-JAN-12 06.39.57
user3       40      16-JAN-12 02.54.13  16-JAN-12 07.12.16
user4       33      16-JAN-12 07.29.43  16-JAN-12 07.59.42
user1       40      16-JAN-12 07.12.39  16-JAN-12 07.59.50
user3       30      16-JAN-12 11.30.50  16-JAN-12 08.02.42
user990     31      17-JAN-12 11.46.12  17-JAN-12 01.46.13
user29      23      17-JAN-12 10.39.18  17-JAN-12 05.00.02
user20      21      17-JAN-12 04.59.37  17-JAN-12 05.00.17
user990     40      17-JAN-12 10.55.48  17-JAN-12 05.00.50
user4       23      17-JAN-12 05.00.11  17-JAN-12 05.01.08
user4       21      17-JAN-12 04.59.37  17-JAN-12 05.01.21

I tried this. It is working for only one day,

SELECT username, MIN(login_time), MAX(logout_time)
FROM Table_Name
WHERE trunc(login_time) = '19-JAN-12'
GROUP BY username;

This gives me the following result for the date I've entered. This is what I want for every date,

NAME

user1   19-JAN-12 11.00.26  19-JAN-12 08.00.53
user3   19-JAN-12 11.05.53  19-JAN-12 11.36.02
user29  19-JAN-12 09.49.32  19-JAN-12 06.48.08
user990 19-JAN-12 10.59.59  19-JAN-12 08.11.15
use23   19-JAN-12 06.40.36  19-JAN-12 08.36.07
user43  19-JAN-12 11.23.05  19-JAN-12 08.28.02
user89  19-JAN-12 02.38.54  19-JAN-12 07.28.02
user4   19-JAN-12 01.24.09  19-JAN-12 06.01.07
user7   19-JAN-12 03.29.17  19-JAN-12 08.34.02
user9   19-JAN-12 09.42.13  19-JAN-12 06.35.54

Upvotes: 1

Views: 6876

Answers (3)

Md. Kamruzzaman
Md. Kamruzzaman

Reputation: 1905

In oracle:

SELECT NAME, TO_CHAR(IN_DT,'DD-MM-YYYY') as DT, TO_CHAR(MIN(IN_DT),'HH24:MI:SS')  as LOGIN, TO_CHAR(MAX(OUT_DT),'HH24:MI:SS') as LOGOUT FROM USER_LOG GROUP BY NAME, TO_CHAR(IN_DT,'DD-MM-YYYY');

enter image description here

Upvotes: 0

Hart CO
Hart CO

Reputation: 34784

It could be as simple as:

SELECT TRUNC(LOGIN_TIME]),NAME, EMPID, MIN(LOGIN_TIME), MAX(LOGOUT_TIME)
FROM Table_Name
GROUP BY TRUNC(LOGIN_TIME),NAME, EMPID
ORDER BY EMPID, TRUNC(LOGIN_TIME)

That will return one line per employee/day, earliest login and latest logout for that employee/day.

Upvotes: 0

wolφi
wolφi

Reputation: 8361

SELECT    username, TRUNC(login_time), min(login_time), max(logout_time)
  FROM table_name
 GROUP BY username, TRUNC(login_time)
 ORDER BY username, TRUNC(login_time);

Upvotes: 7

Related Questions