Solved

# Rollover Minutes Calculation

Posted on 2012-08-30
535 Views
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.

0
Question by:Seaghost

LVL 73

Expert Comment

please sample data and expected output
0

Author Comment

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

LVL 73

Expert Comment

does your query return 13 and 84?

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

Author Comment

Yes, my query does return the specified results.
0

LVL 73

Accepted Solution

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

LVL 51

Expert Comment

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

LVL 73

Expert Comment

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 Comment

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

Author Closing Comment

The Solution worked just as I hoped that it would. Great service.
0

## Featured Post

### Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup