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.

Thank You in Advance
SeaghostAsked:
Who is Participating?
 
sdstuberConnect With a Mentor 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
 
sdstuberCommented:
please sample data and expected output
0
 
SeaghostAuthor 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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
sdstuberCommented:
does your query return 13 and 84?


if not, please provide check_in/check_out values for T_KIOSK_CHECKIN
0
 
SeaghostAuthor Commented:
Yes, my query does return the specified results.
0
 
HainKurtSr. 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

Open in new window


will give you (23, 30)

commented area will give you

5      31
10      33
7      26
0
 
sdstuberCommented:
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
 
SeaghostAuthor Commented:
Thank You Both. Let me give this a try and I will report back to you of my findings.
0
 
SeaghostAuthor 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.

All Courses

From novice to tech pro — start learning today.