hi,
e.g.
Code:
sum of converted time to integer
6.3834
2.6167
= 9.0001
supposed to be it should be 9.00
if i convert time to integer it always has .0001 difference for 4 decimal places and .01 for 2 decimal places, there is always a .01, but in my time format it is exact 9:00
please kindly check my script
Code:
select
`date`,
last_name,
tl,
`client`,
service,
sum(coalesce(adjustment,0)) as 'Total Adjustment',
sum(coalesce(`total break`,0)) as 'Total Break',
sum(`over time`) as 'Over time',
`total man hours` - `over time` as 'reg hrs',
sum(`total man hours`) as 'Total Man Hours',
`time start`,
`time finished`,
`data process`
from(
SELECT
key_entry_log.`date`,
key_entry_log.last_name,
`user`.tl,
batch_log.`client`,
batch_log.service,
key_entry_log.adjustment_time as 'Adjustment',
key_entry_log.break_hours as 'Total Break',
sum(
time_to_sec(
if(
key_entry_log.time_start and key_entry_log.time_end <= '16:00', 0,
if(
key_entry_log.time_start <= '16:00' and key_entry_log.time_end >= '16:00',
subtime(
key_entry_log.time_end,'16:00'
),
if(
key_entry_log.time_start and key_entry_log.time_end > '16:00',
subtime(
key_entry_log.time_end, key_entry_log.time_start
),
0)
)
)
)
)/3600
as 'Over Time',
sum(
time_to_sec(
SUBTIME(
key_entry_log.time_end,key_entry_log.time_start
)
)
)/3600
as 'Total Man Hours',
min(
key_entry_log.time_start
) as 'Time Start',
max(
key_entry_log.time_end
) as 'Time Finished',
"key_entry_log" as 'Data Process'
FROM
key_entry_log
left join `user` on (`user`.last_name = key_entry_log.last_name)
inner join batch_log on (batch_log.job_number = key_entry_log.job_no)
where batch_log.total_batches is not null && key_entry_log.last_name = 'corporal' && month(key_entry_log.`date`) = 7
group by
key_entry_log.`date`,
key_entry_log.last_name,
batch_log.`client`) as q
group by
q.`date`,
q.last_name,
q.`client`
order by
q.`date` desc,
q.last_name
dBforums
>
convert time to decimal format
08-15-08, 03:51
Linear Mode
