Re: [Emerald] Counting Calls

Josh Hillman ( (no email) )
Wed, 16 Feb 2000 20:17:47 -0500

From: Malcolm Joosse <malcolm@hotline.net.au>
> What I need specifically is to be able to select a customer and then get
> information on: the amount of times they dialed in, total connection time
> (hh:mm) for a given period - dd/mm/yy -> dd/mm/yy, total data transfered -
> in/out.

Make sure that in ISQL, your default database is set to "Emerald"
instead of "master" (there's a selection list at the top of the
window). Then run the following (changing the info in the three
lines below "enter in a username..."):

SET NOCOUNT ON
DECLARE @Username varchar(32)
DECLARE @BeginDate datetime
DECLARE @EndDate datetime

/* Enter in a username and a date range below */
SELECT @Username = 'someusername'
SELECT @BeginDate = '1/1/2000'
SELECT @EndDate = '2/1/2000'

SET NOCOUNT OFF
PRINT ' "Duration" = hours : minutes : seconds'
PRINT ''
SELECT 'End of session' = convert(varchar(20), CallDate),
'Duration' = /* hours */
convert(char(2),
AcctSessionTime/3600
) + ':' +
/* minutes */
convert(char(2),
AcctSessionTime/60
- 60 * (AcctSessionTime/3600)
) + ':' +
/* seconds */
convert(char(2),
AcctSessionTime
- 3600 * (AcctSessionTime/3600)
- 60 * ((AcctSessionTime/60) - 60 * (AcctSessionTime/3600))
),
'Bytes Down' = AcctOutputOctets,
'Bytes Up' = AcctInputOctets,
CallerID = convert(varchar(10), CallerID)
FROM Calls
WHERE AcctStatusType = 2
AND Username = @Username
AND CallDate >= @BeginDate
AND CallDate < @EndDate
Order By CallDate Desc

> -----Original Message-----
> From: emerald-request@iea-software.com
> [mailto:emerald-request@iea-software.com]On Behalf Of Josh Hillman
> Sent: Thursday, February 17, 2000 12:29 AM
> To: emerald@iea-software.com
> Subject: Re: [Emerald] Counting Calls
>
>
> From: Malcolm Joosse <malcolm@hotline.net.au>
> > Our beloved Telco has started charging timed calls illegally and it seems
> > some of my modem customers have been affected.
> > I have Emerald 2.5 and Crystal 7 pro but have no idea how to use Crystal
> > except to design invoices.
> > Can someone please tell me how I can extract the amount of calls by
> > customers I know the info is there.
> > Can someone please also point me to a how to use Crystal book or video
> > etc....
> > I am currently counting the calls in the Time-On area and some are making
> > >200 per month and it is taking awhile to count.
>
> If you can be a bit more specific about what details you want, I can
> probably provide a SQL script or two (I probably already have them) that
> will do what you're looking for. Although I do have CR Pro 6.0, I usually
> only use it to modify existing .rpt files...
>
> Josh
>
>
> For more information about this list (including removal) go to:
> http://www.iea-software.com/support/maillists/liststart
>
>
> For more information about this list (including removal) go to:
> http://www.iea-software.com/support/maillists/liststart
>

For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart