?
Solved

Rollover Minutes Calculation

Posted on 2012-08-30
9
Medium Priority
?
541 Views
Last Modified: 2012-08-30
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
0
Comment
Question by:Seaghost
  • 4
  • 4
9 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38350405
please sample data and expected output
0
 

Author Comment

by:Seaghost
ID: 38350508
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 74

Expert Comment

by:sdstuber
ID: 38350586
does your query return 13 and 84?


if not, please provide check_in/check_out values for T_KIOSK_CHECKIN
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Seaghost
ID: 38350600
Yes, my query does return the specified results.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 38350649
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 61

Expert Comment

by:HainKurt
ID: 38350666
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38350743
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

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

Author Closing Comment

by:Seaghost
ID: 38352285
The Solution worked just as I hoped that it would. Great service.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question