Home » SQL & PL/SQL » SQL & PL/SQL » Summing of time (Oracle DB 10g Release 10.2.0.3.0 - Prod, Linux)
Summing of time [message #443040] Fri, 12 February 2010 04:03 Go to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Hi Friends,

I have a sceario like below,

SELECT login_id, c_date, login_time, loout_time,
       TO_CHAR (TRUNC (LOGOUT) + (LOGOUT - login), 'HH24:MI:SS') time_diff
  FROM time_details
 WHERE loginid = '1406'



LOGIN_ID   C_DATE      LOGIN_TIME        LOOUT_TIME        TIME_DIF
---------- ----------- ----------------- ----------------- --------
1406       07-JAN-2010 20100107 11:01:02 20100107 14:01:24 03:01:22
1406       07-JAN-2010 20100107 15:01:13 20100107 20:01:28 05:03:15



I need the output as sum of the difference of the login and logout time for that particular login_id.

Regards,
Marlon.
Re: Summing of time [message #443043 is a reply to message #443040] Fri, 12 February 2010 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please ALWAYS post a test case:
drop table t;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';
create table t (id integer, login date, logout date);
insert into t values (1406, '20100107 11:01:02', '20100107 14:01:24');
insert into t values (1406, '20100107 15:01:13', '20100107 20:01:28');
commit;

SQL> select * from t;
        ID LOGIN             LOGOUT
---------- ----------------- -----------------
      1406 20100107 11:01:02 20100107 14:01:24
      1406 20100107 15:01:13 20100107 20:01:28

2 rows selected.

SQL> select id, 
  2         numtodsinterval(sum(logout-login),'DAY') logon_time
  3  from t
  4  group by id
  5  order by id
  6  /
        ID LOGON_TIME
---------- -----------------------------------------------------
      1406 +000000000 08:00:37.000000000

1 row selected.

Regards
Michel
Re: Summing of time [message #443048 is a reply to message #443040] Fri, 12 February 2010 04:31 Go to previous messageGo to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
Thanks Michel.

Please suggest me how can I display both LOGIN and LOGOUT time
with that output and bringing the LOGON time as a SUBTOTAL
in the third row for each ID.

Regards,
Marlon.
Re: Summing of time [message #443050 is a reply to message #443048] Fri, 12 February 2010 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you post a working Test case: create table and insert statements along with the result you want with these data then we will be able to work with your table and data.

Regards
Michel
Re: Summing of time [message #443157 is a reply to message #443048] Sat, 13 February 2010 01:25 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Are you looking for this kind of solutions ?

http://forums.oracle.com/forums/thread.jspa?threadID=1018576&tstart=1

sriram Smile
Previous Topic: oracle tree calculation
Next Topic: can we do it with one update statement for updating the same column with different conditions
Goto Forum:
  


Current Time: Sat Dec 03 14:11:07 CST 2016

Total time taken to generate the page: 0.07874 seconds