Rollover Minutes Calculation

I need assistance with a problem that consists of calculating the total hours, total minutes, and any minutes that exceed 60 minutes, should be carried over and added to the hours column and displaying for the total minutes the remaining. Currently, I have written code to account of the summation of hours and minutes, however; the rollover minutes to append or add to the hours column, has imposed a bit of a challenge. I have provided my code as a snapshot of what I am trying to accomplish.

SELECT
TRIM(LEADING '-' FROM SUM((EXTRACT(HOUR FROM CHECK_OUT) - EXTRACT(HOUR FROM CHECK_IN))  )) AS TOTAL_HRS,MOD(SUM(EXTRACT(MINUTE FROM (CHECK_OUT - CHECK_IN)) ),60) AS TOTAL_MINUTES
FROM T_KIOSK_CHECKIN

We are currently running Oracle 10g and using PL SQL has the programming lang. I hope this explains everything that  I am trying to accomplish.

Who is Participating?

Commented:
just wrap your query in an inline view and adjust the hours

SELECT total_hrs + FLOOR(total_minutes / 60) total_hrs, MOD(total_minutes, 60) total_minutes
FROM (SELECT TRIM(LEADING '-' FROM SUM(
(  EXTRACT(HOUR FROM check_out)
- EXTRACT(HOUR FROM check_in))))
AS total_hrs,
SUM(EXTRACT(MINUTE FROM (check_out - check_in))) AS total_minutes
FROM t_kiosk_checkin)
0

Commented:
please sample data and expected output
0

Author Commented:
The example should reflect the following:
Name      Tot_Hrs          Tot_Min
John Doe     8                   14
John Doe     3                   28
John Doe     2                   42
--------              ---------
13                   84

Rollover Minutes Should reflect a Summary As:

Name         Tot_Hrs     Tot_Min
John Doe        14                 24
0

Commented:
does your query return 13 and 84?

if not, please provide check_in/check_out values for T_KIOSK_CHECKIN
0

Author Commented:
Yes, my query does return the specified results.
0

Sr. System AnalystCommented:
try something like this

with t as (
select Name,  cast(check_in as timestamp) check_in,  cast(check_out as timestamp) check_out from (
select 'John Doe' Name, sysdate-3.35 check_in, sysdate-3.12 check_out from dual
union select 'John Doe', sysdate-2.50, sysdate-2.06 from dual
union select 'John Doe', sysdate-1.94, sysdate-1.63 from dual
)
)
SELECT
--check_in, check_out, check_out-check_in,
--EXTRACT(HOUR FROM check_out-check_in) AS TOTAL_HRS,
--MOD((EXTRACT(MINUTE FROM (CHECK_OUT - CHECK_IN)) ),60) AS TOTAL_MINUTES
SUM(EXTRACT(HOUR FROM check_out-check_in)) + trunc(SUM(EXTRACT(MINUTE FROM (CHECK_OUT - CHECK_IN))/60)) AS TOTAL_HRS,
MOD(SUM(EXTRACT(MINUTE FROM (CHECK_OUT - CHECK_IN))),60) AS TOTAL_MINUTES
FROM T

will give you (23, 30)

commented area will give you

5      31
10      33
7      26
0

Commented:
if you've created an interval aggregate you can simply sum the intervals and then extract from the totaled interval

SELECT EXTRACT(HOUR FROM total) total_hours,
EXTRACT(MINUTE FROM total) total_minutes
FROM (SELECT dsintervalagg(check_out - check_in) total FROM t_kiosk_checkin);

an example of creating dsintervalagg can be found here

http://www.experts-exchange.com/Database/Oracle/A_9391-How-to-Create-User-Defined-Aggregates-in-Oracle.html
0

Author Commented:
Thank You Both. Let me give this a try and I will report back to you of my findings.
0

Author Commented:
The Solution worked just as I hoped that it would. Great service.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.