Wednesday 15 June 2011

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


Employee ID, log time for each employee and logout time for each employee in which the table requires me Remove the maximum of the minimum and logout time from the login time for the day. The problem is that every day an employee can login and log out several times a day. Therefore, we have sample data that looks like this, 

  NAME EMPID log time LOGOUT time user 1 37 16-Jan-12 03.07.37 16-Jan-12 03.07.44 Users 5 21 16-Jan-12 02.00.36 16-Jan-12 04.45.34 Users 3 12 16-Jan-12 05.35.35 16-Jan-12 06.39.57 Users 3 40 16-Jan-12 02.54.13 16 -Jan-12 07.12.16 Users 4 33 16-Jan-12 07.2 9.43 16-Jan-12 07.5 9.42 Users 1 40 16-Jan-12 07.12.39 16-Jan-12 07.59.50 Users 3 30 16-Jan- 11 11.30.50 16-Jan-12 08.02.42 Users 990 31 17-Jan-12 11.46.12 17-Jan-12 01.46.13 Users 29 923 17-Jan-12 10.39.18 17-Jan-12 05.00. 02 users 20 21 17 -Jan-12 04.5 9 .31 17-Jan-12 05.00.17 User 990 40 17-Jan-12 10.55.48 17-Jan-12 05.00.50 Users 24 23 17-Jan-12 05.00.11 17-Jan- 12 05.01.08 Users 4 21 17-Jan-12 04.5 9.37 17-Jan-12 05.01.21   
 I tried this. This is only working for one day,  
  SELECT username, MIN (login_time), MAX (logout_time) from table_name WHEER trunc (login_time) = '19 -JAN-12 'user Group by name;   
 This gives me the following results for the date I entered. I want it for every date,  
  NAME user1 19-JAN-12 11.00.26 19-Jan-12 08.00.53 Users 3 19-Jan-12 11.05.53 19-JN - 12 11.36.02 Users 29 19-Jan-12 09.4 9.32 19-Jan-12 06.48.08 User 9 9-Jan-12 10.5 9. 9 19-Jan-12 08.11.15 Usage 23 19-Jan-12 06.40.36 19-Jan-12 08.36.07 Users 43 19-Jan-12 11.23.05 19-Jan-12 08.28.02 User-89 19-Jan-12 02.38.54 19-Jan-12 07.28.02 User-4 1-9-Jan 01.24.0 9 -19-Jan-06 06.01.07 User 7 19-Jan-12 03.29.17 19 -Jan-12 08.34.02 Users 9 19-Jan-12 09.42.13 19-Jan-12 06.35.54    
 
  user name, TRUNC (login_time), min (login_time), max (logout_time) from table_name group;    

 








Posted by



at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)