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