DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Calculate the Outage time for each day

  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Calculate the Outage time for each day

    Hello,

    We have a table which contains the outage data as below,

    TAG CI_NAME OUTAGE_START OUTAGE_END
    IM10366 FD0004 06-jul-2010 10:00:00 10-jul-2010 10:00:00


    so from the above record, the outage is from 6th - 10th July. So for the given TAG & CI_NAME, we have to calculate the outage for day.
    So the above record should be split as below

    TAG CI_NAME O_START O_END TA_PERIOD OUT_HRS
    IM10366 FD0004 06-jul-2010 10-jul-2010 06-jul-2010 14HRS
    IM10366 FD0004 06-jul-2010 10-jul-2010 07-jul-2010 24HRS
    IM10366 FD0004 06-jul-2010 10-jul-2010 08-jul-2010 24HRS
    IM10366 FD0004 06-jul-2010 10-jul-2010 09-jul-2010 24HRS
    IM10366 FD0004 06-jul-2010 10-jul-2010 10-jul-2010 10HRS


    Thanks & Regards,
    Manoj

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Is the question "how to do it?"

    In the affirmative case the first thing that pops-up on my mind is to load base outage data into a cursor then generate all needed "splitted" rows in a loop.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2010
    Posts
    2
    Hi,

    Thank you for the response.
    I am very much new to the pl sql coding, so can you please give me a heads up or a sample code on which i can improvise and get the solution done.

    Thanks & Regards,
    Manoj

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by manojis View Post
    ... so can you please give me a heads up or a sample code on which i can improvise and get the solution done.
    Sure, here goes the pseudo-code

    create procedure MyOutageDataProc as
    - define variables as needed
    - define MyCursor as select * from OutageDataTable;
    - BEGIN
    - For each on MyCursor << MAIN LOOP
    - loop
    -- Loop here to create each ouput row << SECONDARY LOOP
    -- insert into OutputTable values(whatever business logic is asking for)
    -- end loop;
    - end loop;
    end;
    /

    Please research cursors and loops.
    Last edited by PAVB; 07-09-2010 at 07:51 AM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Code:
     with temp1
     as
     (select 'IM10366' TAG,
     'FD0004' CI_NAME,
     to_date('06-jul-2010 10:00:00','DD-Mon-YYYY HH24:MI:SS') OUTAGE_START,
     to_date('10-jul-2010 10:00:00','DD-Mon-YYYY HH24:MI:SS') OUTAGE_END
     from dual
     ) ,
     temp2
     as
     (select
     trunc(to_date('06-jul-2010 10:00:00','DD-Mon-YYYY HH24:MI:SS')+rownum) OUTAGE_END
     from dual
     CONNECT BY ROWNUM <= to_date('10-jul-2010 10:00:00','DD-Mon-YYYY HH24:MI:SS')-to_date('06-jul-2010 10:00:00','DD-Mon-YYYY HH24:MI:SS')+1
     ) ,
     temp as
     (
     select t1.tag,
     t1.ci_name,
     greatest(trunc(t1.outage_start)+rownum-1,t1.outage_start) ,
     least (t1.outage_end,t2.outage_end) ,
     (least (t1.outage_end,t2.outage_end)-greatest(trunc(t1.outage_start)+rownum-1,t1.outage_start))*24 hours
     from temp1 t1 ,temp2 t2
     )
     select * from temp
     /
    Code:
    SQL> /
    
    TAG      CI_NAME GREATEST(TRUNC(T1.OU LEAST(T1.OUTAGE_END,      HOURS
    -------- ------- -------------------- -------------------- ----------
    IM10366  FD0004  06-Jul-2010 10:00:00 07-Jul-2010 00:00:00         14
    IM10366  FD0004  07-Jul-2010 00:00:00 08-Jul-2010 00:00:00         24
    IM10366  FD0004  08-Jul-2010 00:00:00 09-Jul-2010 00:00:00         24
    IM10366  FD0004  09-Jul-2010 00:00:00 10-Jul-2010 00:00:00         24
    IM10366  FD0004  10-Jul-2010 00:00:00 10-Jul-2010 10:00:00         10
    Last edited by hrishy; 07-10-2010 at 05:20 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width