thanks.
Don Barron
Director
Surfindaweb Communications Pty Ltd (World-Link Internet)
----- Original Message -----
From: Josh Hillman <admin-maillist@talstar.com>
To: <emerald@iea-software.com>
Sent: Wednesday, October 04, 2000 1:54 AM
Subject: Re: [Emerald] Script Help!
> From: "Don Barron" <don@world-link.com.au>
> > How could i get something like this to show me active users per city and
> or
> > region ?
>
> That script I sent earlier does exactly that (based on the Region field).
>
> It'd have to be modified slightly to do it based simply on the City and/or
> State fields. I happen to have mine geared toward the Region field.
>
> Josh
>
> > ----- Original Message -----
> > From: Josh Hillman <admin-maillist@talstar.com>
> > To: <emerald@iea-software.com>
> > Sent: Tuesday, October 03, 2000 11:39 PM
> > Subject: Re: [Emerald] Script Help!
> >
> >
> > > From: "PowerNet" <psc5@powersupply.net>
> > > >I am looking for a SQL query or CR report that will show new
customers
> by
> > > region in the past x days or month.
> > >
> > > You can modify my script below slightly to get what you want.
Although
> we
> > > provide service nationwide, we have the regions geared toward Florida,
> > which
> > > is why the state abbreviation doesn't appear anywhere except when the
> > Region
> > > is "Other" (in our case, outside of Florida).
> > >
> > > I don't use these for anything in this script, but you will likely
need
> > them
> > > to get exactly what you're looking for:
> > > The MasterAccounts.CreateDate is when the billing record was created.
> > > The SubAccounts.SignDate is when the individual services were created.
> > >
> > > Josh
> > >
> > >
> > > -- Active Billing records grouped by region
> > >
> > >
> > > SET NOCOUNT ON
> > >
> > > PRINT 'Active Accounts (based on billing record PaidThru date)'
> > > PRINT ' '
> > >
> > > SELECT CurrentDate = convert(varchar(20), getdate())
> > >
> > >
> > > -- Display number of active billing records
> > >
> > > SELECT Accounts = 'Billing Records',
> > > Personal = (SELECT count(*)
> > > FROM masteraccounts
> > > WHERE PaidThru >= getdate()
> > > AND Active = 1
> > > AND company = ''),
> > > Businesses = (SELECT count(*)
> > > FROM masteraccounts
> > > WHERE PaidThru >= getdate()
> > > AND Active = 1
> > > AND company != ''),
> > > Total = (SELECT count(*)
> > > FROM masteraccounts
> > > WHERE PaidThru >= getdate()
> > > AND Active = 1)
> > >
> > > UNION
> > >
> > > -- Display number of active services
> > >
> > > SELECT Accounts = 'Services',
> > > Personal= (SELECT count(sa.accountid)
> > > FROM masteraccounts ma, subaccounts sa
> > > WHERE ma.customerid = sa.customerid
> > > AND ma.PaidThru >= getdate()
> > > AND ma.Active = 1
> > > AND sa.Active = 1
> > > AND ma.company = ''),
> > > Business = (SELECT count(sa.accountid)
> > > FROM masteraccounts ma, subaccounts sa
> > > WHERE ma.customerid = sa.customerid
> > > AND ma.PaidThru >= getdate()
> > > AND ma.Active = 1
> > > AND sa.Active = 1
> > > AND ma.company != ''),
> > > Total = (SELECT count(sa.AccountID)
> > > FROM masteraccounts ma, subaccounts sa
> > > WHERE ma.customerid = sa.customerid
> > > AND ma.PaidThru >= getdate()
> > > AND ma.Active = 1
> > > AND sa.Active = 1)
> > > ORDER BY Accounts
> > >
> > >
> > >
> > > -- Service (AccountType) details for the month
> > >
> > > PRINT 'Service Details:'
> > > SELECT sa.AccountType,
> > > Total=count(sa.AccountType)
> > > FROM SubAccounts sa, MasterAccounts ma
> > > WHERE ma.CustomerID = sa.CustomerID
> > > AND PaidThru >= getdate()
> > > AND ma.Active = 1
> > > AND sa.Active = 1
> > > GROUP BY AccountType
> > > ORDER BY AccountType
> > >
> > >
> > >
> > >
> > > -- Regional details
> > >
> > > PRINT ' '
> > > PRINT 'Regional Billing Records'
> > >
> > > DECLARE @Region varchar(30)
> > > DECLARE Region_cursor CURSOR
> > > FOR SELECT r.Region
> > > FROM Regions r, MasterAccounts ma
> > > WHERE ma.Region = r.Region
> > > AND ma.PaidThru >= getdate()
> > > AND ma.Active = 1
> > > GROUP BY r.Region, r.Sortorder
> > > ORDER BY count(ma.Region) DESC, r.SortOrder, r.Region
> > >
> > >
> > > OPEN Region_cursor
> > >
> > > FETCH NEXT FROM Region_cursor INTO @Region
> > > WHILE (@@fetch_status <> -1)
> > > BEGIN /* beginning of main loop */
> > >
> > >
> > > -- Display regions
> > >
> > > SELECT Region = @Region,
> > > Total = count(ma.Region)
> > > FROM MasterAccounts ma
> > > WHERE ma.Region = @Region
> > > AND ma.PaidThru >= getdate()
> > > AND ma.Active = 1
> > >
> > > UNION
> > >
> > > SELECT Area = ' ' + ma.City +
> > > CASE WHEN @Region = 'Other'
> > > THEN ', ' + upper(ma.State)
> > > ELSE ''
> > > END,
> > > Total= count(ma.City)
> > > FROM MasterAccounts ma
> > > WHERE ma.Region = @Region
> > > AND ma.PaidThru >= getdate()
> > > AND ma.Active = 1
> > >
> > > GROUP BY Region, ma.City, ma.State
> > > ORDER BY Total DESC, Region DESC
> > >
> > >
> > >
> > > FETCH NEXT FROM Region_cursor INTO @Region
> > >
> > > END /* end of main loop */
> > >
> > > CLOSE Region_cursor
> > > DEALLOCATE Region_cursor
> > >
> > >
> > > 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
For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart