Re: Time arithmetic in Oracle & WebDB

In article <8hplpt$22v$1_at_nnrp1.deja.com>,   dxmonger_at_yahoo.com wrote:
> Folks
>
> I've been scratching my head over something that I'm certain has been
> solved several times before. I have a table that has a column
> of "times", that is, the real number of minutes that something
 happened
> per day. For example, if I was a runner and tracking the number of
> minutes I ran in a month, this table would look like:
>
> Day Minutes ran
> ---- -----------
> 1 45
> 2 98
> 3 37
> 4 102
>
> and so on. Now, I need to generate a report which displays the time in
> HH24:MI format. I thought, no problem, in the SQL statement for the
> WEBDB report just use this:
>
> select STATDATE "Day", TO_CHAR(TRUNC(RUNTIME/60),'09')||':'||TO_CHAR
 (MOD
> (RUNTIME,60),'09') "Minutes ran"
>
> which does work. However, I also need to generate column totals. I
 have
> quite a few columns of this sort of info.
>
> So, my questions are:
> a) should I try to convert time? That is, I have successfully tried:
> to_char(to_date('01-JAN-1970','DD-MON-YYYY')+(RUNTIME/
> (60*24) ),'HH24:MI') "Minutes ran"
> But WebDB's total result for this column is 0. I *really* need
> accurate times in the totals. Do I need to 'Add advanced PL/SQL code'?
> What I'd really like to show is a total format that's a number of
 hours
> that can be larger than 24 ":" MM. For example, someone might run
> 122:14 hours in a month.
>
> b) should I abandon the convert to time approach and go back to
> straight math, then write some crazy stored procedure to convert
> everything back?
>
> c) am I nutz for attempting such a thing in WebDB?
>
> Many thanks
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Well, this would be hard in any report tool since the thing you want to sum, a "HH:MI" variable, isn't really summable. What we need to do is have the database compute the sum for us and we apply a format to the details and the sum.

In 8.1.5 -- We can do this -- using the ROLLUP functions (there is a cube function as well).

Consider this example:

ops$tkyte_at_8i> create table t ( day int, minutes number ); Table created.

ops$tkyte_at_8i> insert into t values ( 1, 45 );
ops$tkyte_at_8i> insert into t values ( 2, 98.31 );
ops$tkyte_at_8i> insert into t values ( 3, 37.92 );
ops$tkyte_at_8i> insert into t values ( 4, 102 );

ops$tkyte_at_8i> select nvl( to_char(day), 'TOTAL' ), sum(minutes)   2 from t
  3 group by rollup(day)
  4 /

NVL(TO_CHAR(DAY),'TOTAL')                SUM(MINUTES)
---------------------------------------- ------------
1                                                  45
2                                               98.31
3                                               37.92
4                                                 102
TOTAL                                          283.23

So, the database can generate our totals and subtotals for us

Now, applying your formatting:

ops$tkyte_at_8i> select nvl( to_char(day), 'TOTAL' ),   2
TO_CHAR(TRUNC(sum(minutes)/60),'fm09')||':'||TO_CHAR(MOD   3 (sum(minutes),60),'fm09') "Minutes ran"   4 from t
  5 group by rollup(day)
  6 /

NVL(TO_CHAR(DAY),'TOTAL')                Minutes
---------------------------------------- -------
1                                        00:45
2                                        01:38
3                                        00:38
4                                        01:42
TOTAL                                    04:43

And that is the query you'll build your webdb report on...

Answer to B) = NO
Answer to C) = NO

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.