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