Re: Billing Issues

Dale E. Reed Jr. ( (no email) )
Sun, 7 Dec 1997 10:56:33 -0800 ()

On Sun, 7 Dec 1997, Howard Towt wrote:

> I'm not sure about the nuance of "anniversary billing" vs. monthly
> billing. These are accounts that we set up with a "pay period" value
> set to "monthly", and the "expire" value set to the 1st of the month.
> I'm also not sure about the example of using 2/6/97 as an expiration
> date that should result in a query of 11/7/97 to 12/6/97. With our
> version of Emerald, accounts expiring on 2/6/97 actually don't expire
> (cease RADIUS authorization) until the date turns to 2/7/97. With that
> logic, it seemed appropriate to set the date boundary as 11/7/97 to
> 12/7/97. Also, our expiration date gets advanced each month as invoices
> are paid. Does the 2/6/97 example imply that there is an anniversary
> date value lurking in the database that stays static?

Yes, I didn't got to the exact minute. I usually look at it as day to
day, but its actually day+1 to day.

> Anyway, two questions remain:
> 1) When the batch charge consolidation is run, the Calls table values
> are deleted up to the second the consolidation is run, while the summary
> values loaded to the Call History are cut off as of the previous
> midnight. Why aren't the two operations consistent in the calculations
> used?

Its an issue with 2.1 that we corrected in 2.2 and higher.

> 2) I have to supply reports to customers (mostly small businesses with
> ISDN routers) who want to see the detail of the activity that results in
> the number of hours for which they are billed. I gave an example query
> based on the assumed logic of the Emerald product. Evidently my
> assumptions were "mostly wrong". Does anyone have a query that matches
> the logic used to summarize the transactions to the Call History table?

I don't remember the 2.1 logic, but here is the consolidation stored
proc from 2.2 and higher.

Select AccountID, Mins=Sum(c.AcctSessionTime/60+1), Sum(sp.CostPerMinute),
Diff=DateDiff(Month, c.CallDate, maExpireDate) - DateDiff(Month,
GetDate(), maExpireDate)

From Calls c, MasterAccounts ma, SubAccounts sa, Servers s, ServerPorts
Where ma.CustomerID = sa.CustomerID
And sa.Login = c.UserName
And c.AcctStatusType = 2
And (DateDiff(Month, c.CallDate, maExpireDate) - DateDiff(Month,
GetDate(), maExpireDate)) >= @Dif
And s.IPAddress = c.NASIdentifier
And sp.Port = c.NASPort
And s.ServerID = sp.ServerID

Group By AccountID, DateDiff(Month, c.CallDate, maExpireDate) -
DateDiff(Month, GetDate(), maExpireDate)
Order By AccountID, Diff DESC

Just add a criteria of sa.Login = 'user' and set @Dif to 1.

Dale E. Reed Jr. (
IEA Software, Inc. | RadiusNT, Emerald, and NT FAQs
Internet Solutions for Today |