Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to calculate the time since job started or restarted

Posted on 2014-03-12
15
Medium Priority
?
311 Views
Last Modified: 2014-06-08
I have a database with many tables.  One of the tables maintains the history of the job status.  This tables has many columns but the columns of importance are Job Number, Status, and Status Start Time.  I need to calculate the time the job spent from start to completion.

For most jobs this is easy since I have the job request time and the job complete time in the job table.  However there are instances when a job can be marked complete but the reopened for further work.  This can potentially happen many times.

Job Status Data
The users want to see the time between start of the cycle and job complete.  For the first job completion this is simply the job complete time - job request time.  I can do this one easily.  For a job that has multiple completions I need the time calculated as Job Complete Time - Previous Job Complete Time.

  Completion time calculation
I don't know how to select the previous Job Complete Time from the data based on the completion record I have.

I cannot modify existing tables in the database but I can add a view or build a derived table in the universe the reports are based on


mlmcc
Job-Status-History-data.xlsx
0
Comment
Question by:mlmcc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +1
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923732
should the 2nd job completion time not be the difference from row 5 - row 6?
because during time row 4 - row 5, nothing was done? just to consider ...


apart from that, the calculation simply needs to take the row which matches, prior to the completion, either being the first one unless another completion is before it.

let me work on the script ...
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 39923769
presuming the ID numbers are indeed ascending per start_date, this would be a working query:
select h.id, h.job_number, p.status_start, h.status_end
from job_history h
join job_history p
  on p.job_number = h.job_number
 and p.id = ( select min(x.id) 
      from job_history x
     where x.job_number = h.job_number
       and x.status <> 99
       and not exists( select null 
                       from job_history c 
                      where c.job_number = h.job_number
                          and c.status = 99
                          and c.id > x.id                                     
                          and c.id < h.id
                      )
        )       
where h.status = 99
;

Open in new window

where you only have to modify the table and column names as needed
0
 
LVL 101

Author Comment

by:mlmcc
ID: 39923804
There are no useful ID numbers in the table.  I added those to help with the explanation.

I didn't think about that when I wrote the question.

mlmcc
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923855
then just replace the ID by status_start field, it should do the same job for this query
0
 
LVL 32

Expert Comment

by:awking00
ID: 39923928
Two questions. What version of Oracle are you using and is it possible for there to be more than two complete statuses for the same job number?
0
 
LVL 101

Author Comment

by:mlmcc
ID: 39923972
Not sure on the Oracle version.  Yes it is possible for there to be more than 2 complete statuses.  

mlmcc
0
 
LVL 32

Expert Comment

by:awking00
ID: 39923995
select * from v$version  ==> to get Oracle version
0
 
LVL 101

Author Comment

by:mlmcc
ID: 39924216
11g

mlmcc
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1000 total points
ID: 39924251
with cte as
(select jobno, status, statusstart, lead(statusstart) over (partition by jobno order by statusstart) nexttime  from
 (select jobno, status, statusstart
  from status_history
  where statusdesc = 'Complete'
  union
  select jobno, status, statusstart
  from status_history
  where statusdesc = 'Requested'
  order by jobno, statusstart)
)
select sh.jobno, sh.status, sh.statusstart, round(24 * (cte.nexttime - cte.statusstart),2) timesincestart
from status_history sh
left join cte
on sh.jobno = cte.jobno
and sh.statusstart = cte.nexttime
and cte.nexttime is not null
order by sh.jobno, sh.statusstart;
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39925728
awking: I think your assumption on using status "requested" as "starting point" is not correct, when I look at the same input data ...
0
 
LVL 32

Expert Comment

by:awking00
ID: 39926190
mlmcc,
Would the initial "starting point" always be where the status desc is 'Requested' or not?
0
 
LVL 101

Author Comment

by:mlmcc
ID: 39926259
It should be but there are times it is not.

I have the initial starting date in another table so if it is the first Completion NULL or 0 can be returned.  The dates I am using are stored as integers.

This SQL, based on the first comment from Guy, seems to be working but I don't get NULLs or a value when there is no previous

select  h.JOBNUMBER,  h.BEGINDATE, h.BEGINTIME, max(p.BEGINDATE) as previousstartdate,  p.BEGINTIME as previousstarttime
from JOBSTATUSHISTORY h
left outer join JOBSTATUSHISTORY p
  on p.JOBNUMBER = h.JOBNUMBER and
      (p.BEGINDATE < h.BEGINDATE OR (p.BEGINDATE = h.BEGINDATE AND p.BEGINTIME < h.BEGINTIME))   and
       p.STATUS= '99'
where h.STATUS= '99' and h.JOBNUMBER IN ( 29395148, 29397082, 29399202)
group by  h.JOBNUMBER,  h.BEGINDATE, h.BEGINTIME,  p.BEGINTIME

Open in new window


The 3 jobnumbers have 2 completion records.  I get 2 records for each but when I try to convert the dates for display I get an error that indicates it is a bad value - either out of range or 0.  When I just display the values they appear to be good values or nothing is displayed.  If I format the number to show something for an undefined (NULL) value then I still get nothing displayed.

Code for the conversion is

CASE WHEN NVL("Status_History_Previous".PREVIOUSSTARTDATE,0) = 0  THEN NULL ELSE EFSCRID.TO_GREGORIAN("Status_History_Previous".PREVIOUSSTARTDATE)  END

Open in new window


mlmcc
0
 
LVL 32

Expert Comment

by:awking00
ID: 39926351
Can you provide some sample data that covers all of the possible criteria and what you expect as output?
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 40121186
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question