SQL & PL/SQL

Hi Users,

I have requirement to write a sql query to calculate and convert the device outage into PERCENTAGE from the timedifference between 2 date columns.

1) I have 2 date columns in the table


SQL> create table datepercent
2 ( firstoccurrence date,
3 lastoccurrence date);

SQL> insert into datepercent values (to_date('23-MAR-2011 10:30:00','DD-MON-YYYY HH:MI:SS'), to_date('23-MAR-2011 11:00:00','DD-MON-YYYY HH:MI:SS'));

1 row created.

SQL> insert into datepercent values (sysdate,sysdate-1/48); <-- ( Time difference with 30 Minutes)

1 row created.


2) The above table has 2 records with time difference between "FIRSTOCCURRENCE" and "LASTOCCURRENCE" is 30 Minutes.

SQL> select to_char(firstoccurrence,'DD-MON-YYYY HH:MI:SS AM'),to_char(lastoccurrence,'DD-MON-YYYY HH:MI:SS AM') from datepercent;

TO_CHAR(FIRSTOCCURRENCE TO_CHAR(LASTOCCURRENCE,
----------------------- -----------------------------------------------------------------------
23-MAR-2011 10:30:00 AM 23-MAR-2011 11:00:00 AM
23-MAR-2011 11:53:44 PM 23-MAR-2011 11:23:44 PM

3) Run the query to select and substract the lastoccurrence and firstoccurrence for the last one hour. I have written the query below

SQL> select to_char(firstoccurrence,'DD-MON-YYYY HH:MI:SS AM'),to_char(lastoccurrence,'DD-MON-YYYY HH:MI:SS AM') from datepercent where firstoccurrence > sysdate-(1/24) and lastoccurrence <= sysdate;

TO_CHAR(FIRSTOCCURRENCE TO_CHAR(LASTOCCURRENCE,
----------------------- -----------------------
23-MAR-2011 11:53:44 PM 23-MAR-2011 11:23:44 PM


SQL> select firstoccurrence - lastoccurrence from datepercent where firstoccurrence > sysdate-(1/24) and lastoccurrence <= sysdate;

FIRSTOCCURRENCE-LASTOCCURRENCE
---------------------------------------------------------------
.020833333


From the output above , I can see that in the last one hour there is a record where the outage 30 Minutes.

My customer wants to see that 30 Minutes downtime value in the ONE HOUR window as 50%. Suppose if the downtime difference is 15 Minutes then the output of the sql query should be 75%.

How can I accomplish this with a sql query.

Also kindly let me know whether the output that is shown above (.0208333333) is "Seconds" or what does it indicate.

Thanks.
Regards,
Ravi.