Home » SQL & PL/SQL » SQL & PL/SQL » elapsed time for a id (Oracle 12.1)
elapsed time for a id [message #687673] Wed, 03 May 2023 03:35 Go to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

I have a table as below with the sample input data.

create table t_sr (id number, step varchar2(20), start_time timestamp, end_time timestamp); 
insert into t_sr values (1, 'started', to_timestamp('05/01/2023 2:24:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/01/2023 2:26:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (1, 'InProgress', to_timestamp('05/01/2023 2:28:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/01/2023 2:30:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (1, 'Getting data', to_timestamp('05/01/2023 3:42:22.34','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/01/2023 3:48:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (1, 'End', to_timestamp('05/01/2023 4:24:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/01/2023 4:26:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
--
insert into t_sr values (10, 'started', to_timestamp('05/02/2023 2:24:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/02/2023 3:26:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (10, 'InProgress', to_timestamp('05/02/2023 3:28:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/02/2023 3:30:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (10, 'Getting data', to_timestamp('05/02/2023 3:42:22.34','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/02/2023 4:48:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (10, 'End', to_timestamp('05/02/2023 4:50:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/02/2023 5:48:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
--
insert into t_sr values (20, 'started', to_timestamp('05/03/2023 2:24:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/03/2023 2:26:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
--
insert into t_sr values (21, 'started', to_timestamp('05/04/2023 2:24:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/4/2023 2:28:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (21, 'InProgress', to_timestamp('05/04/2023 2:29:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/4/2023 2:30:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (21, 'Getting data', to_timestamp('05/04/2023 3:41:22.34','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/4/2023 3:46:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (21, 'End', to_timestamp('05/04/2023 4:24:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/4/2023 4:25:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
The output looks like as shown in the attachement.

I would like to get following 2 columns displayed as below.

ID, Total_time_elapsed
where total_time_elapsed is a calculated column need to be in seconds where the value is subtraction of end_time(where STEP='End')  and start_time(where STEP='started') for a given id when records are ordered by start_time
At the same time, records which are not having both "started" and "End" as step value need to be ignored, for e.g. id=20 need to be ignored as it does not have STEP='end'


Thank you in advance.

Regards,

Report message to a moderator

Re: elapsed time for a id [message #687674 is a reply to message #687673] Wed, 03 May 2023 03:59 Go to previous messageGo to next message
John Watson
Messages: 8872
Registered: January 2010
Location: Global Village
Senior Member
What SQL have you tried so far? I have a solution based on GROUP BY ID with MIN and MAX for the START_TIME and END_TIME columns.

Report message to a moderator

Re: elapsed time for a id [message #687675 is a reply to message #687674] Wed, 03 May 2023 05:19 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi Jon,

I understand what you are trying to say, the challenge is, max may not work as there could be other records sharing the same ID with different values of start_dtm, end_dtm

for e.g. below is possible.

insert into t_sr values (1, 'started', to_timestamp('05/01/2023 2:24:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/01/2023 2:26:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (1, 'InProgress', to_timestamp('05/01/2023 2:28:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/01/2023 2:30:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (1, 'Getting data', to_timestamp('05/01/2023 3:42:22.34','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/01/2023 3:48:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (1, 'End', to_timestamp('05/01/2023 4:24:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/01/2023 4:26:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));

insert into t_sr values (1, 'started', to_timestamp('05/02/2023 2:24:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/02/2023 2:26:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (1, 'InProgress', to_timestamp('05/02/2023 2:28:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/02/2023 2:30:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (1, 'Getting data', to_timestamp('05/02/2023 3:42:22.34','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/02/2023 3:48:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
insert into t_sr values (1, 'End', to_timestamp('05/02/2023 4:24:22.1234','mm/dd/yyyy hh24:mi:ss.FF'),to_timestamp('05/02/2023 4:26:22.1234','mm/dd/yyyy hh24:mi:ss.FF'));
Also, we have to exclude those records which are not having END record for e.g ID=20 in the above data.

Regards,

Report message to a moderator

Re: elapsed time for a id [message #687677 is a reply to message #687675] Wed, 03 May 2023 08:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3246
Registered: January 2010
Location: Connecticut, USA
Senior Member
Data:

SQL> SELECT  *
  2    FROM  T_SR
  3    ORDER BY ID,
  4             START_TIME
  5  /

        ID STEP                 START_TIME                     END_TIME
---------- -------------------- ------------------------------ ------------------------------
         1 started              01-MAY-2023  02:24:22.123400   01-MAY-2023  02:26:22.123400
         1 InProgress           01-MAY-2023  02:28:22.123400   01-MAY-2023  02:30:22.123400
         1 Getting data         01-MAY-2023  03:42:22.340000   01-MAY-2023  03:48:22.123400
         1 End                  01-MAY-2023  04:24:22.123400   01-MAY-2023  04:26:22.123400
         1 started              02-MAY-2023  02:24:22.123400   02-MAY-2023  02:26:22.123400
         1 InProgress           02-MAY-2023  02:28:22.123400   02-MAY-2023  02:30:22.123400
         1 Getting data         02-MAY-2023  03:42:22.340000   02-MAY-2023  03:48:22.123400
         1 End                  02-MAY-2023  04:24:22.123400   02-MAY-2023  04:26:22.123400
        10 started              02-MAY-2023  02:24:22.123400   02-MAY-2023  03:26:22.123400
        10 InProgress           02-MAY-2023  03:28:22.123400   02-MAY-2023  03:30:22.123400
        10 Getting data         02-MAY-2023  03:42:22.340000   02-MAY-2023  04:48:22.123400
        10 End                  02-MAY-2023  04:50:22.123400   02-MAY-2023  05:48:22.123400
        20 started              03-MAY-2023  02:24:22.123400   03-MAY-2023  02:26:22.123400
        21 started              04-MAY-2023  02:24:22.123400   04-MAY-2023  02:28:22.123400
        21 InProgress           04-MAY-2023  02:29:22.123400   04-MAY-2023  02:30:22.123400
        21 Getting data         04-MAY-2023  03:41:22.340000   04-MAY-2023  03:46:22.123400
        21 End                  04-MAY-2023  04:24:22.123400   04-MAY-2023  04:25:22.123400

17 rows selected.

SQL>
Match recognize solution, assuming started/End don't overlap:

SELECT  M.ID,
        EXTRACT(SECOND FROM M.DURATION) +
        EXTRACT(MINUTE FROM M.DURATION) * 60 +
        EXTRACT(HOUR FROM M.DURATION) * 3600 +
        EXTRACT(HOUR FROM M.DURATION) * 86400 TOTAL_TIME_ELAPSED
  FROM  T_SR
  MATCH_RECOGNIZE(
                  PARTITION BY ID
                  ORDER BY START_TIME
                  MEASURES
                    E.END_TIME - FIRST(S.START_TIME) DURATION
                  PATTERN(S A* E)
                  DEFINE S AS STEP = 'started',
                         A AS STEP NOT IN ('started','End'),
                         E AS STEP = 'End'
                 ) M
/

        ID TOTAL_TIME_ELAPSED
---------- ------------------
         1             180120
         1             180120
        10             271440
        21             180060

SQL>
SY.

[Updated on: Wed, 03 May 2023 08:45]

Report message to a moderator

Re: elapsed time for a id [message #687678 is a reply to message #687675] Wed, 03 May 2023 11:20 Go to previous messageGo to next message
John Watson
Messages: 8872
Registered: January 2010
Location: Global Village
Senior Member
I was assuming that ID,STEP made a primary key. If they don't, what is the PK? Remember that Ted Codd said that all tables must have a PK (he also said that tables have rows. Not records.)

You need some way to distinguish row sets for the same ID. Think of the case when the start and end overlap. If you cannot find some way to do that, a transaction identifier perhaps, then I would say that your relational analysis is flawed.

Report message to a moderator

Re: elapsed time for a id [message #687690 is a reply to message #687677] Tue, 09 May 2023 02:58 Go to previous message
pointers
Messages: 451
Registered: May 2008
Senior Member
Sorry for my late response.
Thanks a ton Solomon Yakobson.
Your response not only helped me to solve a problem but also to learn something new. You are amazing.

Regards,
Pointers

Report message to a moderator

Previous Topic: Join purchases to customers
Next Topic: reading all values of json table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Sep 27 17:34:05 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.