how to calculate sum of hours?

Question

My tables are as follows

workers:

id integer

...

days:

id INTEGER

event_date DATE

worker_id INTEGER

and

hours:

id INTEGER

hr_start VARCHAR2(8)

hr_stop VARCHAR2(8)

day_id INTEGER

I need a function which will calculate for every user in database, sum of hours in current month. Fields hr_start and hr_stop contains time in format hh:mi:ss. I need this for oracle database.

Answer 1

SELECT w.id, SUM(DATEDIFF(h, hr_start, hr_end))
FROM workers w
INNER JOIN days d ON w.id = d.worker_id
INNER JOIN hours h ON d.id = h.day_id
GROUP BY w.id

Sorry, I only know SQL Server. You might try using the linked example below to calculate the number of hours by subtraction and rounding.

http://www.orafaq.com/faq/how%5Fdoes%5Fone%5Fget%5Fthe%5Ftime%5Fdifference%5Fbetween%5Ftwo%5Fdate%5Fcolumns

Answer 2

I created these test tables in my 10g XE database and this query should produce what you are looking for.

select worker_id,
       sum(hrs)
  from (select worker_id, 
               (to_date(hr_stop, 'hh24:mi:ss') - to_date(hr_start, 'hh24:mi:ss')) * 24 hrs
          from days d,
               hours h
         where d.id = h.day_id
           and trunc(event_date, 'mm') = trunc(sysdate, 'mm')
       )
group by worker_id