> I have 3 pairs of time in/out (24 our clock) that I need to calculate
> the decimal equivalent of the total time worked for that day.
> example: time in/out 08:00 10:00, 10:15 12:45, 13:45 16:50
> if I use a simple select of time_out time_ in I get 2:00,
> 2:30, 3:10 respectfully, or 7:35 total hours.
> That is 7.58 in decimal.
> Now how do I change the total hours of 7:35 to a decimal of 7.58 in a
> single SQL statement? Or will I have to write a proc that does this?
You will have to obtain or write a procedure to do it; there isn't any
way to coerce an interval into a decimal without using a stored procedure.
create procedure ivhm_decimal(i interval hour(7) to minute)
define im interval minute(9) to minute;
define str char(10);
define d decimal;
let im = i;
let str = im;
let d = str / 60.0;
Untested. You have to go through a string. You might return ROUND(d,
2) to get 2 decimal places of hours. I'm not sure about the first
assignment (let im = i); you might be able to alter the type in the
signature to interval minute(9) to minute and still invoke it with:
ivhm_decimal(t_out1 - t_in1) +
ivhm_decimal(t_out2 - t_in2) +
ivhm_decimal(t_out3 - t_in3)
Or equivalents. Also poke around the IIUG archives - there's code
there to do the job in ESQL/C (iv_hours for your case). It could be
adapted to work in SPL instead of ESQL/C.
Jonathan Leffler #include <disclaimer.h>
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/