SQL & PL/SQL

select DISTINCT(ii.equse_gkey), eu4.eq_nbr, reef.on_dr, reef.onpower, reef.offpower, reef.Plugged_Days, reef.Plugged_Hours
from invoice_items ii, equipment_uses eu4,
( SELECT onpower.dr on_dr, to_char(onpower.performed,'DD-MON-RRRR HH24:MI:SS') onpower,
            to_char(offpower.performed,'DD-MON-RRRR HH24:MI:SS') offpower,
            round(offpower.performed - onpower.performed) Plugged_Days,
            round(((offpower.performed - onpower.performed) * 24 )) Plugged_Hours
       FROM
         (
          SELECT tserv_id,
                 CASE
                   WHEN tserv_id = 'IN TIME' AND dr = 1 THEN
                     performed
                   WHEN tserv_id = 'ONPOWER' AND dr > 1 THEN
                     performed
                 END performed, dr
          FROM
        (
          SELECT tserv_id, performed,
                 dense_rank() over (order by performed) dr
          FROM (
                SELECT se.tserv_id, se.performed
                FROM   service_events se
                WHERE se.equse_gkey=ii.equse_gkey
                  AND se.tserv_id='ONPOWER'
               )

         UNION
         SELECT tserv_id, in_time,
                 dense_rank() over (order by in_time) dr
         FROM (
                SELECT 'IN TIME' tserv_id, in_time
                FROM   equipment_uses eu
                WHERE eu.gkey=ii.equse_gkey
                  AND eu.in_time IS NOT NULL
              )
         )
         WHERE CASE
                  WHEN tserv_id = 'IN TIME' and dr = 1 THEN
                    performed
                  WHEN tserv_id = 'ONPOWER' and dr > 1 THEN
                    performed
                  END IS NOT NULL
         /**/
         ) onpower,
         (
          SELECT tserv_id, performed,
                 dense_rank() over (order by performed) dr
          FROM (
                SELECT se.tserv_id, se.performed
                FROM   service_events se
                WHERE se.equse_gkey=ii.equse_gkey
                  AND se.tserv_id='OFFPOWER'

                UNION
                SELECT 'OFFPOWER' tserv_id, eu.out_time
                FROM   equipment_uses eu
                WHERE eu.gkey=ii.equse_gkey
                  AND eu.out_time IS NOT NULL
               )
          ) offpower
     WHERE onpower.dr = offpower.dr
     ) reef
where ii.equse_gkey=eu4.gkey
  and ii.equse_gkey is not null
  and ii.invoice_id=2609630
order by eu4.eq_nbr