Re: Consolidation

Dale E. Reed Jr. ( (no email) )
Fri, 30 May 1997 21:41:31 -0700

Grant McClure wrote:
> Dale, is there any reason that all the consolidation routines aren't
> just coded as SQL Server procedures? It'd be so much easier to edit
> them if that were the case.

Yes. On a 5000 user test system, I had a really killer single SQL
statement that did a nine table cross-query returning single lines to
go into the callhistory. It required a log segment/tempdb the SAME size
of the calls table and crippled the SQL Server while it did it.

1) SQL Server does not support an INSERT INTO statement. You can create
tables, but you can't insert data into existing tables.

2) There are conditional calculations that have to be done during the
consolidation phase and optional entries into the charges table
with DRI to the CallHistory table.

IOW, it might be possible, but I don't want to try and figure it out.

> When it comes to the consolidations, I always just want to consolidate
> the calls from the first of the last month to the last day of the last
> month. I don't care about the expiry date. The only way I can get
> around this now is to batch update expiry dates to the correct day
> before running consolidations.

This is an option that will be in the new version. It will do monthly
time rather than anniversary. The problem with this is that the user's
account is being paid on a different time scale than their account and
if you charge for over usage, it splits this useage. They could get
away from twice their allowed time without paying a penny.

> I also noticed that if you do a consolidation more than once in a
> month that it tries to insert a 2nd row for a AccountID, Date Start
> and Months ... which has to be unique, so the insert fails and then
> the calls are deleted (lost). I sort of assumed it would do an update
> and add the calls and minutes to the prior amounts. I guess not.

This is the consolidation problem that I briefly mentioned that is now
fixed in Emerald 2.2.

> The other thing that I find a little out is that the default time
> period to check for calls in the Time On tab is from the expiry date
> forward a month. But if my expiry date is set forward a month in order
> to consolidate my calls for last month ... I have to manually change
> the period every time I go into Time On .... which I sometimes forget
> to do ... and end up thinking ... Oh, this person has had no calls
> this month.

Yes. This was to prevent the the type mismatch date problem. We
re-worked the logic and its putting the correct dates in now.

> I realize that I'm not quite using the system the way that it was
> intended. But the product should be flexible enough to handle these
> different ways of handling things.

We can only add options when people tell us they are interested in them.
We have always been open minded to this. You just have to mention it.

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