That query works great and I am sure that it will be of use to me, but I=
guess I should have been more clear on what I was asking for.
I am looking for users by region. I need something that I can put in a=
region or zip(or list of zips) and then the query will print out a list of=
usernames@domain.com
Thanks again!
John
*********** REPLY SEPARATOR ***********
On 10/3/2000 at 10:09 AM Josh Hillman wrote:
>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 =3D convert(varchar(20), getdate())
>
>
>-- Display number of active billing records
>
>SELECT Accounts =3D 'Billing Records',
> Personal =3D (SELECT count(*)
> FROM masteraccounts
> WHERE PaidThru >=3D getdate()
> AND Active =3D 1
> AND company =3D ''),
> Businesses =3D (SELECT count(*)
> FROM masteraccounts
> WHERE PaidThru >=3D getdate()
> AND Active =3D 1
> AND company !=3D ''),
> Total =3D (SELECT count(*)
> FROM masteraccounts
> WHERE PaidThru >=3D getdate()
> AND Active =3D 1)
>
>UNION
>
>-- Display number of active services
>
>SELECT Accounts =3D 'Services',
> Personal=3D (SELECT count(sa.accountid)
> FROM masteraccounts ma, subaccounts sa
> WHERE ma.customerid =3D sa.customerid
> AND ma.PaidThru >=3D getdate()
> AND ma.Active =3D 1
> AND sa.Active =3D 1
> AND ma.company =3D ''),
> Business =3D (SELECT count(sa.accountid)
> FROM masteraccounts ma, subaccounts sa
> WHERE ma.customerid =3D sa.customerid
> AND ma.PaidThru >=3D getdate()
> AND ma.Active =3D 1
> AND sa.Active =3D 1
> AND ma.company !=3D ''),
> Total =3D (SELECT count(sa.AccountID)
> FROM masteraccounts ma, subaccounts sa
> WHERE ma.customerid =3D sa.customerid
> AND ma.PaidThru >=3D getdate()
> AND ma.Active =3D 1
> AND sa.Active =3D 1)
>ORDER BY Accounts
>
>
>
>-- Service (AccountType) details for the month
>
>PRINT 'Service Details:'
>SELECT sa.AccountType,
> Total=3Dcount(sa.AccountType)
>FROM SubAccounts sa, MasterAccounts ma
>WHERE ma.CustomerID =3D sa.CustomerID
> AND PaidThru >=3D getdate()
> AND ma.Active =3D 1
> AND sa.Active =3D 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 =3D r.Region
> AND ma.PaidThru >=3D getdate()
> AND ma.Active =3D 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 =3D @Region,
> Total =3D count(ma.Region)
>FROM MasterAccounts ma
>WHERE ma.Region =3D @Region
>AND ma.PaidThru >=3D getdate()
>AND ma.Active =3D 1
>
>UNION
>
>SELECT Area =3D ' ' + ma.City +
> CASE WHEN @Region =3D 'Other'
> THEN ', ' + upper(ma.State)
> ELSE ''
> END,
> Total=3D count(ma.City)
>FROM MasterAccounts ma
>WHERE ma.Region =3D @Region
>AND ma.PaidThru >=3D getdate()
>AND ma.Active =3D 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