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