> 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
sp
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. (daler@iea.com)
_________________________________________________________________
IEA Software, Inc. | RadiusNT, Emerald, and NT FAQs
Internet Solutions for Today | http://www.iea-software.com