OraFAQ Forum: SQL & PL/SQL » Calc elapsed time within a group of data

Home » SQL & PL/SQL » SQL & PL/SQL » Calc elapsed time within a group of data (10.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calc elapsed time within a group of data [message #609901]

Thu, 13 March 2014 13:29

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Within each "parent" record, I'd like to see the elapsed time between each subsequent "start_date" times
(30515 -> 30518) seq: 1 and 5
(31111 -> 31111) seq: 6 and 14

ID	ID2	TIME
31111	parent	00 Hours 00 Minutes 18 Seconds
30518	parent	00 Hours 00 Minutes 09 Seconds

WITH data AS
 (SELECT '30518' id1, NULL id2, SYSDATE start_date, 1 seq
    FROM dual
  UNION ALL
  SELECT NULL id1, 'a' id2, SYSDATE + 3 / 24 / 60 / 60, 2
    FROM dual
  UNION ALL
  SELECT NULL id1, NULL id2, SYSDATE + 5 / 24 / 60 / 60, 3
    FROM dual
  UNION ALL
  SELECT NULL id1, 'a' id2, SYSDATE + 7 / 24 / 60 / 60, 4
    FROM dual
  UNION ALL
  SELECT '30518' id1, NULL id2, SYSDATE + 9 / 24 / 60 / 60, 5
    FROM dual
  UNION ALL
  SELECT '31111', NULL, SYSDATE + 11 / 24 / 60 / 60, 6
    FROM dual
  UNION ALL
  SELECT NULL id1, 'b' id2, SYSDATE + 13 / 24 / 60 / 60, 7
    FROM dual
  UNION ALL
  SELECT NULL id1, NULL id2, SYSDATE + 15 / 24 / 60 / 60, 8
    FROM dual
  UNION ALL
  SELECT NULL id1, NULL id2, SYSDATE + 17 / 24 / 60 / 60, 9
    FROM dual
  UNION ALL
  SELECT NULL id1, 'b' id2, SYSDATE + 20 / 24 / 60 / 60, 10
    FROM dual
  UNION ALL
  SELECT NULL id1, 'c' id2, SYSDATE + 22 / 24 / 60 / 60, 11
    FROM dual
  UNION ALL
  SELECT NULL id1, NULL id2, SYSDATE + 23 / 24 / 60 / 60, 12
    FROM dual
  UNION ALL
  SELECT NULL id1, 'c' id2, SYSDATE + 25 / 24 / 60 / 60, 13
    FROM dual
  UNION ALL
  SELECT '31111' id1, NULL id2, SYSDATE + 29 / 24 / 60 / 60, 14 FROM dual
  
  )

SELECT id1 id,
       id2,
       to_char(trunc(SYSDATE) + (max_time - min_time), 'HH24 "Hours" MI "Minutes" SS "Seconds"') TIME
  FROM (SELECT id1,
               'parent' id2,
               MIN(start_date) Min_Time,
               MAX(start_date) Max_Time,
               MAX(start_date) - MIN(start_date) Max_minus_Min
          FROM data
         WHERE id1 IS NOT NULL
         GROUP BY id1, id2)

BUT

within each "parent" record, i'd like to see the elapsed time between each subsequent
"start_date" times

(a -> a) seq: 2 and 4
(b -> b) seq: 7 and 10
(c -> c) seq: 11 and 13

ID	ID2	TIME
30518	parent	00 Hours 00 Minutes 09 Seconds
30518	a	00 Hours 00 Minutes 04 Seconds
31111	parent	00 Hours 00 Minutes 18 Seconds
31111	b	00 Hours 00 Minutes 07 Seconds
31111	c	00 Hours 00 Minutes 03 Seconds

Do I need to somehow group each set of "parent" data first then group each "id2" columns within them to get the min/max times??

Thanks

Report message to a moderator

Re: Calc elapsed time within a group of data [message #609902 is a reply to message #609901]

Thu, 13 March 2014 13:34

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

oops, sorry, I should have put the "select" clause with the "with" statement...

Report message to a moderator

Re: Calc elapsed time within a group of data [message #609903 is a reply to message #609901]

Thu, 13 March 2014 14:00

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

It is not exactly clear what you need, but this can be a starting point. Use analytics + group by:

WITH data AS (
              SELECT '30518' id1, NULL id2, SYSDATE start_date, 1 seq FROM dual UNION ALL
              SELECT NULL id1, 'a' id2, SYSDATE + 3 / 24 / 60 / 60, 2 FROM dual UNION ALL
              SELECT NULL id1, NULL id2, SYSDATE + 5 / 24 / 60 / 60, 3 FROM dual UNION ALL
              SELECT NULL id1, 'a' id2, SYSDATE + 7 / 24 / 60 / 60, 4 FROM dual UNION ALL
              SELECT '30518' id1, NULL id2, SYSDATE + 9 / 24 / 60 / 60, 5 FROM dual UNION ALL
              SELECT '31111', NULL, SYSDATE + 11 / 24 / 60 / 60, 6 FROM dual UNION ALL
              SELECT NULL id1, 'b' id2, SYSDATE + 13 / 24 / 60 / 60, 7 FROM dual UNION ALL
              SELECT NULL id1, NULL id2, SYSDATE + 15 / 24 / 60 / 60, 8 FROM dual UNION ALL
              SELECT NULL id1, NULL id2, SYSDATE + 17 / 24 / 60 / 60, 9 FROM dual UNION ALL
              SELECT NULL id1, 'b' id2, SYSDATE + 20 / 24 / 60 / 60, 10 FROM dual UNION ALL
              SELECT NULL id1, 'c' id2, SYSDATE + 22 / 24 / 60 / 60, 11 FROM dual UNION ALL
              SELECT NULL id1, NULL id2, SYSDATE + 23 / 24 / 60 / 60, 12 FROM dual UNION ALL
              SELECT NULL id1, 'c' id2, SYSDATE + 25 / 24 / 60 / 60, 13 FROM dual UNION ALL
              SELECT '31111' id1, NULL id2, SYSDATE + 29 / 24 / 60 / 60, 14 FROM dual
             ),
        t AS (
              select  nvl2(id2,last_value(id1 ignore nulls) over(order by seq),id1) id1,
                      nvl(id2,'parent') id2,
                      start_date,
                      seq
                from  data
                where nvl(id1,id2) is not null
             )
select  id1,
        id2,
        to_char(
                date '1-1-1' + (max(start_date) - min(start_date)),
                'HH24" Hours "MI" Minutes "SS" Seconds"'
               ) time
  from  t
  group by id1,
           id2
  order by id1,
           case id2
             when 'parent' then 1
           end,
           id2
/

ID1   ID2    TIME
----- ------ ------------------------------
30518 parent 00 Hours 00 Minutes 09 Seconds
30518 a      00 Hours 00 Minutes 04 Seconds
31111 parent 00 Hours 00 Minutes 18 Seconds
31111 b      00 Hours 00 Minutes 07 Seconds
31111 c      00 Hours 00 Minutes 03 Seconds

SQL>

SY.
P.S. The above code assumes time duration is less than 24 hours.

[Updated on: Thu, 13 March 2014 14:00]

Report message to a moderator

Re: Calc elapsed time within a group of data [message #609905 is a reply to message #609903]

Thu, 13 March 2014 15:14

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

As usual, my specs are usually lacking exact details, but yes this is pretty much what I was after.

Thanks, Solomon

Report message to a moderator

Previous Topic:	rewrite sql query
Next Topic:	how to call package using persistant package variable

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 03:50:40 CDT 2024