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