Home » SQL & PL/SQL » SQL & PL/SQL » help required in writing a query
help required in writing a query [message #247242] Mon, 25 June 2007 03:50 Go to next message
ssunda6
Messages: 28
Registered: June 2007
Junior Member
Hi All,

I have a table LOG_TABLE
{
JOB_NM1 NOT NULL VARCHAR(80)
JOB_NM2 NOT NULL VARCHAR(80)
JOB_START_DT NOT NULL DATE
LOG_RUN_DT NOT NULL DATE
....
and some extra columns
}

JOB_NM1,JOB_NM2 are two parts of job name. Both together is the full job name.
Job_start_dt is the time the job started.(Though this is DATE,(it is being stored as timestamp(date and time))
LOG_RUN_DT is the time of execution of each line in the job(This is also timestamp)

For one execution of job, the JOB_START_DT value will remain same for all records but LOG_RUN_DT varies. For next execution of job , the JOB_START_DT will differ obviously

My requirement is:
For a particular day(i.e., JOB_START_DT) find out which job took the maximum time of execution.

So, first I have filtered out records based on job_start_dt and then grouped the records on job_nm1,job_nm2. Then Found out the execution time of each job(max(LOG_RUN_DT)-min(LOG_RUN_DT)) and then have to find out the maximum time of execution.

I wrote this query.

SELECT
max(LOG_RUN_DT)-min(LOG_RUN_DT), job_nm1, job_nm2
FROM
LOG_TABLE
GROUP BY
job_nm1,job_nm2,JOB_START_DT
HAVING
JOB_START_DT like '25-MAY-07'

This is giving the time of execution of each job on a date. But i have to find out the job name which took the maximum amount of time.

Inputs on how to do this would b of great help.

Regards,
ssunda.
Re: help required in writing a query [message #247245 is a reply to message #247242] Mon, 25 June 2007 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, the following is wrong:
Quote:
JOB_START_DT like '25-MAY-07'

You compare a DATE with a STRING, this is WRONG and it will never work in my environment.

Now you can do something like that:
select *
from ( select job_nm1, job_nm2, max(LOG_RUN_DT)-min(LOG_RUN_DT) max_duration
       FROM LOG_TABLE
       WHERE JOB_START_DT between 
               to_date('25/05/2007','DD/MM/YYYY') and
               to_date('26/05/2007','DD/MM/YYYY')-1/86400
       GROUP BY job_nm1,job_nm2,JOB_START_DT
       ORDER BY max_duration desc
     )
where rownum=1
/

Regards
Michel
Re: help required in writing a query [message #247250 is a reply to message #247245] Mon, 25 June 2007 04:54 Go to previous message
ssunda6
Messages: 28
Registered: June 2007
Junior Member
Hi Michel,

JOB_START_DT column datatype is DATE. We are storing timestamp also in that column (like "25-MAY-2007 10:20:30"). But since it is actually defined as DATE, when queried , the display format is (25-MAY-2007) only. As I wanted to fetch only date for comparision, I used

JOB_START_DT like '25-MAY-2007'. This is working fine on my environment.

But as mentioned by you this is not the right procedure. The query you gave is perfectly working for my requirement.

Thank you very much.

Regards,
ssunda.
Previous Topic: dbms_job submit - problem with date
Next Topic: Usage of triggers
Goto Forum:
  


Current Time: Mon Dec 05 21:31:19 CST 2016

Total time taken to generate the page: 0.05963 seconds