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: 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