Re: Miscalculations

Glen Harvy ( glen@aquarius.com.au )
Thu, 27 Aug 1998 10:18:13 +1000

At 13:59 26/08/98 -0400, you wrote:

I use iHTML extensively and this is how I do the SQL query..

<excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><excerpt><bigger><<ISQL
DBNAME=Emerald SQL="SELECT sum(AcctSessionTime),
sum(AcctInputOctets),sum(AcctOutputOctets) FROM CALLS

WHERE ((Calls.CallDate)>':FromDate')

AND ((Calls.UserName)=':Login') ">

<<ISQLFETCH>

These are the totals of the three main items since :FromDate ....

<<PRE>Total hours on-line = <<iMATH A=:1 B=3600 o=/ PRECISION=2> hours.

Total kilobytes sent by you = <<iMATH A=:2 B=1000 o=/> kilobytes.

Total kilobytes received by you = <<iMATH A=:3 B=1000 o=/>
kilobytes.<</ISQL><</PRE>

</bigger></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt></excerpt>

>Ok, I've been playing with queries with the database for determining
the

>time spent online by a given user. The calculation I am using is below
:

>

> sum(Calls.AcctSessionTime) / 3600 AS Hours,

> (sum(Calls.AcctSessionTime) / 60) - ((sum(Calls.AcctSessionTime) /
3600) *

>60) AS Mins,

>

>I don't see anything apparently wrong with that assuming the
AcctSessionTime

>value is in fact stored as the number of seconds online. But, in
Emerald,

>the calculation is different by as high as 14 hours in certain
scenarios.

>(Seems to increase exponentially in comparison to the number of hours
spent

>online)

>In my testing scenario, the following numbers apply: (These are
actual

>values from the database)

> Calls.AcctSessionTime = 105433

>Following through the calculations: (SQL Auto rounds to 0 decimal
places)

> 105433/3600 = 29

> Total Hours

> (105433/60) - [(105433/3600) * 60]

> (SQL Rounds) - [(SQL Rounds)*60]

> 1757 - 29*60 = 17

>

>So we end off with 29 hours and 17 minutes. As best as I can see,
this

>calculation is fairly accurate aside from the rounding issues of the
SQL

>calculations.

>However, if I head into Emerald and use the Time On function for the
same

>service, it lists as 27 hours and 8 minutes. There are no restrictions

>anywhere on which calls to include or disclude in the SQL statement, and
the

>Emerald restrictions are set to the future sometime and before the start
of

>the earth! (Well, not exactly but you get the idea, everything is
included).

>Is there a miscalculation somewhere on my end? Does Emerald do
something

>differently? Or is there something that's just plain wrong??

>2 hours and 9 minutes is quite a bit of time difference between the
two

>calculations. Can anyone verify this? Or tell me where I've gone
wrong??

>

>- Chris

>

>

>

Glen.

--------------------------------------------------------------------

AQUARIUS Communications for all your Internet<<>Fidonet needs

<<>Full ISP services<<>FrontDoor Commercial<<>TransX Internet/FTSC Mailer

voice(02)9977-3788<<>fax(02)9977-3844<<>bbs(02)9977-2855<<>3:714/930

http://www.aquarius.com.au <<> mailto:sales@aquarius.com.au