CODEWITHSUNDEEP
sqloracle-databasefunctionsum
szaman
szaman

Reputation: 6756

how to calculate sum of hours?

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.

Upvotes: 2

Views: 3096

Answers (2)

Doug Porter
Doug Porter

Reputation: 7887

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

Upvotes: 3

AverageAdam
AverageAdam

Reputation: 337

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_does_one_get_the_time_difference_between_two_date_columns

Upvotes: 2

Related Questions