Home » SQL & PL/SQL » SQL & PL/SQL » Processed time for a Job SQL (11G)
Processed time for a Job SQL [message #671698] Fri, 14 September 2018 01:49 Go to next message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
Hi,

I want to compute the actual process time for Job Id A and below are the data

SQL> create table test ( jid varchar2(10), start_time date, end_time date);

Table created.

SQL> insert into test values ('A',to_date('14-09-2018 09:00:00','dd-mm-yyyy hh24:mi:ss'),to_date('14-09-2018 09:10:00','dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into test values ('A',to_date('14-09-2018 09:05:00','dd-mm-yyyy hh24:mi:ss'),to_date('14-09-2018 09:40:00','dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into test values ('A',to_date('14-09-2018 09:03:00','dd-mm-yyyy hh24:mi:ss'),to_date('14-09-2018 09:30:00','dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into test values ('A',to_date('14-09-2018 09:03:00','dd-mm-yyyy hh24:mi:ss'),to_date('14-09-2018 09:30:00','dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into test values ('A',to_date('14-09-2018 09:45:00','dd-mm-yyyy hh24:mi:ss'),to_date('14-09-2018 09:50:00','dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into test values ('A',to_date('14-09-2018 09:47:00','dd-mm-yyyy hh24:mi:ss'),to_date('14-09-2018 09:48:00','dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into test values ('A',to_date('14-09-2018 09:55:00','dd-mm-yyyy hh24:mi:ss'),to_date('14-09-2018 09:55:05','dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into test values ('A',to_date('14-09-2018 09:55:00','dd-mm-yyyy hh24:mi:ss'),to_date('14-09-2018 09:56:00','dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into test values ('A',to_date('14-09-2018 09:56:00','dd-mm-yyyy hh24:mi:ss'),to_date('14-09-2018 09:59:00','dd-mm-yyyy hh24:mi:ss'));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

JID        START_TIME          END_TIME
---------- ------------------- -------------------
A          14-09-2018 09:00:00 14-09-2018 09:10:00
A          14-09-2018 09:05:00 14-09-2018 09:40:00
A          14-09-2018 09:03:00 14-09-2018 09:30:00
A          14-09-2018 09:03:00 14-09-2018 09:30:00
A          14-09-2018 09:45:00 14-09-2018 09:50:00
A          14-09-2018 09:47:00 14-09-2018 09:48:00
A          14-09-2018 09:55:00 14-09-2018 09:55:05
A          14-09-2018 09:55:00 14-09-2018 09:56:00
A          14-09-2018 09:56:00 14-09-2018 09:59:00


SQL> select * from test;

JID START_TIME END_TIME
---------- ------------------- -------------------
A 14-09-2018 09:00:00 14-09-2018 09:10:00
A 14-09-2018 09:05:00 14-09-2018 09:40:00
A 14-09-2018 09:03:00 14-09-2018 09:30:00
A 14-09-2018 09:03:00 14-09-2018 09:30:00
A 14-09-2018 09:45:00 14-09-2018 09:50:00
A 14-09-2018 09:47:00 14-09-2018 09:48:00
A 14-09-2018 09:55:00 14-09-2018 09:55:05
A 14-09-2018 09:55:00 14-09-2018 09:56:00
A 14-09-2018 09:56:00 14-09-2018 09:59:00


Here actual start and end time of JID A as below

start time - 14-09-2018 09:00:00
end time - 14-09-2018 09:40:00

start time - 14-09-2018 09:45:00
end time - 14-09-2018 09:48:00

start time - 14-09-2018 09:55:00
end time - 14-09-2018 09:59:00

Now i need 2 sql query

1. query to display below output

JID START_TIME END_TIME TIME_DIFFERENCE
--- --------------------- --------------------- --------------
A 14-09-2018 09:00:00 14-09-2018 09:40:00 00:40:00
A 14-09-2018 09:45:00 14-09-2018 09:48:00 00:03:00
A 14-09-2018 09:55:00 14-09-2018 09:59:00 00:04:00

2. Query to display

JID TIME_DIFFERENCE
--- --------------
A 00:47:00

Regards,
Ram
Re: Processed time for a Job SQL [message #671699 is a reply to message #671698] Fri, 14 September 2018 02:10 Go to previous messageGo to next message
John Watson
Messages: 7557
Registered: January 2010
Location: Global Village
Senior Member
I see no logical reason for selecting the rows you want rather than any others.
Re: Processed time for a Job SQL [message #671700 is a reply to message #671699] Fri, 14 September 2018 02:15 Go to previous messageGo to next message
s.m.ramachandran
Messages: 20
Registered: September 2012
Junior Member
I want to compute the time taken and my table has records with same.
Re: Processed time for a Job SQL [message #671701 is a reply to message #671700] Fri, 14 September 2018 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 65766
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do you REALLY think this is an explanation for "selecting the rows you want rather than any others"?

Re: Processed time for a Job SQL [message #671706 is a reply to message #671700] Fri, 14 September 2018 08:08 Go to previous messageGo to next message
EdStevens
Messages: 969
Registered: September 2013
Senior Member
s.m.ramachandran wrote on Fri, 14 September 2018 02:15
I want to compute the time taken and my table has records with same.
So please explain the logic that determines that the row you show with a start time of 09:00:00 is paired with the one that has an end time of 09:10:00 (which, btw seems to have a start time of 09:05:00) and not the row that has an end time of 09:48:00.

What is the logic that EVERY row has, in itself both a start time and and end time? What is the logic that associates (or "pairs up") any one row with another?

Re: Processed time for a Job SQL [message #671707 is a reply to message #671698] Fri, 14 September 2018 08:14 Go to previous message
Solomon Yakobson
Messages: 2788
Registered: January 2010
Location: Connecticut, USA
Senior Member
Typical start-of-group task:


with a as (
           select  t.*,
                   case
                     when lag(end_time) over(partition by jid order by start_time,end_time) >= start_time then 0
                     else 1
                   end start_of_group
             from  test t
          ),
     b as (
           select  a.*,
                   sum(start_of_group) over(partition by jid order by start_time,end_time) grp
             from  a
          )
select  jid,
        min(start_time) start_time,
        max(end_time) keep(dense_rank last order by start_time) end_time,
        numtodsinterval(max(end_time) keep(dense_rank last order by start_time) - min(start_time),'day') duration
  from  b
  group by jid,
           grp
  order by jid,
           grp
/

JID        START_TIME          END_TIME            DURATION
---------- ------------------- ------------------- -----------------------------------
A          09/14/2018 09:00:00 09/14/2018 09:40:00 +000000000 00:40:00.000000000
A          09/14/2018 09:45:00 09/14/2018 09:48:00 +000000000 00:03:00.000000000
A          09/14/2018 09:55:00 09/14/2018 09:59:00 +000000000 00:04:00.000000000

SQL> 

SY.

[Updated on: Fri, 14 September 2018 08:43]

Report message to a moderator

Previous Topic: String Comparison
Next Topic: Error in PL/SQL package function (table of records)
Goto Forum:
  


Current Time: Mon Sep 24 01:20:05 CDT 2018