counting how many minutes a timestamp is older then 8 in the morning 1

[Larshg]

Hi

I have a job that runs every day, and logs in a table when it is done. And at the end of the month, I need to figure out is the job is living up to the requriments
Whitch are:
the job has to be finished by 8 in the morning, but since this is not always posible, I need to messure how many minutes it is later then 8, meaning the only the days where the job finnishes after 8 are relevent, and it is the number of minutes after 8 that are relevent.

ex
end_time
04-06-20 08:50 --should count as 50 minutes
04-07-20 07:40 --not relevent
04-08-20 10:00 --should count as 120 minutes

the sql should return 170 minutes

And it should only messure it in weekdays. -wthe entery that is made in the weekends is also not relevent.

Thanks
Larshg

 

[PHV]

And what have you so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Larshg]

Hi

The way I doing it now is to select the time stamps that I want.

select ETL_PROGRAM.program_name, to_char(ETL_PROGRAM_log.program_end, 'yyyy-mm--dd-dy hh24:mi')
from ETL_PROGRAM_log, ETL_PROGRAM
where ETL_PROGRAM_log.program_id = ETL_PROGRAM.program_id
and ETL_PROGRAM.program_name = 'END_CYCLE'
and to_char(ETL_PROGRAM_log.program_end, 'YYYYMM')='200408'
and to_char(ETL_PROGRAM_log.program_end, 'DY') != 'SAT'
and to_char(ETL_PROGRAM_log.program_end, 'DY') != 'SUN'


But this means that I have to manualy take away the ones that finnished before 08:00

And then finaly count the number of minutes that they are after 8

I've made a small unix ksh script that does this, but it still involves som manual labor, witch is what I like to do with out.

/Larshg

 

[fredericofonseca]

AND to_char(ETL_PROGRAM_log.program_end, 'HH:MM') > '08:00'

will return only the ones finhised after 08:00.


to_char(timestamp, 'HH24')*60 + to_char(timestamp, 'MI') - 480 as minutes
will return the number of minuts after 08:00


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com