-
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
-
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.
-
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
-
Originally Posted by manojis
... 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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|