Billing Issues

Howard Towt ( htowt@solucian.com )
Sat, 6 Dec 1997 12:08:47 -0700

I am using Emerald software from the February, 1997 timeframe
(emerald.exe version 2.01.0011, 664,064 bytes). I expect the software
to help me bill customers on an hourly basis, but have been disappointed
in that capability.
There are several minor problems that have been discussed in this forum.
However, there are two major problems that I've not seen mentioned:

1) The significance of the time of month when charges are consolidated
--The data cuts off at midnight for the PRIOR month's bill.
--The CURRENT month's billing starts accumulating from the
moment the update is run.
--The customer therefore receives free hours, unless the ISP runs
the
consolidation at 00:00:01 on a particular day.

2) The monthly consolidated time logged by Emerald does not match the
time
from the SQL query I run to audit the Emerald calculation
--The time difference is much greater than from the effect in
(1) above.
--The database query that I use is:

*************************
DECLARE @timeon smalldatetime, @timeoff smalldatetime, @userid char(16)

Select @timeon = "11/01/97 00:00"
Select @timeoff = "12/01/97 00:00"
Select @userid = "testuser"

Select UserName, SvcDate=DatePart(dd,DateAdd(Second,(0-AcctSessionTime-
AcctDelayTime), CallDate)),Start=DateAdd(Second,
(0-AcctSessionTime-AcctDelayTime),
CallDate),Mins=(AcctSessionTime/60),
Stop=DateAdd(Second, (0-AcctDelayTime), CallDate),
Server=SubString(Server, 1, 15), Port=Str(NASPort,5),
IP=FramedAddress

from calls, servers

where DateAdd(Second, (0-AcctSessionTime-AcctDelayTime), CallDate)
>=@timeon
and DateAdd(Second, (0-AcctDelayTime), CallDate) <= @timeoff
and AcctSessionTime >= 60
and AcctStatusType = 2
and calls.NASIdentifier=servers.ipaddress
and UserName = @userid

Order BY SvcDate, Start

Compute SUM(AcctSessionTime/60) BY SvcDate

Compute SUM(AcctSessionTime/60)
************************

Because the time shown on the Emerald reports never matches the time
from the SQL query, I have to manually bill customers. As you can
imagine, this detracts significantly from the utility of the Emerald
billing program.

Does anyone out there have experience in this area? If I could match
the math done in the Emerald charge consolidation routine with my SQL
query, and could kick off an NT process just after midnight to do the
charge consolidation process, that would help minimize the irritation.

Thanks,

Howard Towt
htowt@solucian.com
(303) 520-0868