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 |
|
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 #671707 is a reply to message #671698] |
Fri, 14 September 2018 08:14 |
Solomon Yakobson
Messages: 3285 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
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:37:06 CDT 2024
|