Home » SQL & PL/SQL » SQL & PL/SQL » Query to find out weekly average of job execution time (Oracle 10.2.0.4 on Sun Solaris )
Query to find out weekly average of job execution time [message #365290] Thu, 11 December 2008 05:38 Go to next message
gkodakalla
Messages: 49
Registered: March 2005
Member
I have an existing table which has the following details:



CREATE TABLE JOB_DETAILS (JOB_START_DATE DATE,
JOB_END_DATE DATE,
JOB_START_TIME NUMBER,
JOB_END_TIME NUMBER,
HOURS_OF_EXECUTION NUMBER,
DETAILS VARCHAR2(20));

INSERT INTO JOB_DETAILS VALUES ('01-FEB-2007','01-FEB-2007',10.04,20.09,10.04,'WEEKDAY');
INSERT INTO JOB_DETAILS VALUES ('07-FEB-2007','07-FEB-2007',00.00,00.00,00.00,'WEEKEND');
commit;

Job_Start_Date Job_End_Date Job_Start_Time Job_End_Time Hours_Of_Execution DETAILS
1/2/2007 1/2/2007 10:04 20:09 10:04
1/7/2007 1/7/2007 0:00 0:00 0:00 Weekend


Our jobs wont run on week ends and on holidays where we see "Hours_of_execution" as 0.
Week means Monday to Friday for me.
(1) I want a query which gives me a weekly,monthly average of how many hours the job ran.
(2) I want to find out the week, when the average execution of the job is > 10 hours.


can anyone help in framing this query to get the details? Thanks

Re: Query to find out weekly average of job execution time [message #365296 is a reply to message #365290] Thu, 11 December 2008 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your output shows 6 columns in header and 5 in data. It is unreadable.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Post what you already tried:
1) EXTRACT or TO_CHAR on date and GROUP BY
2) Add HAVING to get only those >10 hours

Regards
Michel
Re: Query to find out weekly average of job execution time [message #365650 is a reply to message #365290] Thu, 11 December 2008 18:58 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Shouldn't it be easier if your start and end dates are already in the 'MM/DD/YYYY HH24:MI:SS' format? Its alot easier to read and compute the number or processing hours/minutes that way..

select sysdate from dual

select sysdate from dual

12/12/2008 8:40:14 AM

-----

select (sysdate - to_date('12/11/2008 8:40','MM/DD/YYYY HH24:MI')) NUM_OF_DAY from dual

NUM_OF_DAY
1.00038194444444



Well still if the table already exists that way.. then all it takes is some arithmetic and month to week to day to hours to minutes etc. conversion, barring weekends..

Regards,
Wilbert

[Updated on: Thu, 11 December 2008 19:03]

Report message to a moderator

Re: Query to find out weekly average of job execution time [message #365653 is a reply to message #365290] Thu, 11 December 2008 19:12 Go to previous message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Moderators, sorry for the follow up..

This would be helpful as a reference.. http://www.orafaq.com/forum/t/129489/131810/

[Updated on: Thu, 11 December 2008 19:13]

Report message to a moderator

Previous Topic: Continuing the process after an EXCEPTION
Next Topic: External Tables for a fixed width file
Goto Forum:
  


Current Time: Sat Dec 03 18:09:50 CST 2016

Total time taken to generate the page: 0.10637 seconds