Table of Contents

Explanation of SQL

This query returns customer sales from the GL for a given time frame.

Risk of Data Corruption if Run Improperly

None. This is a selection query and no data is modified in the running of it.

SQL for Control 4.x

DECLARE @StartDateTime datetime;
DECLARE @EndDateTime datetime;
SET @StartDateTime = '1/1/2009';
SET @EndDateTime = '1/1/2010'
SELECT * FROM
(
SELECT A.AccountNumber, A.CompanyName, M.ItemName AS Industry, AC.Title, (AC.FirstName +' '+  AC.LastName) AS ContactName, AC.Position, 
P.FormattedText AS PhoneNumber, 
F.FormattedText AS FaxNumber, 
AC.EmailAddress, 
Addr.StreetAddress1, Addr.StreetAddress2, Addr.City, Addr.State, Addr.PostalCode,
(SELECT -SUM(Amount) FROM GL WHERE AccountID = A.ID AND GLClassificationType = 8012 AND GL.EntryDateTime BETWEEN @StartDateTime AND @EndDateTime ) AS PreTaxSales
FROM Account A
LEFT JOIN AccountContact AC ON (A.PrimaryContactID = AC.ID)
LEFT JOIN MarketingListItem M ON (A.IndustryID = M.ID)
LEFT JOIN PhoneNumber P ON (P.ID = A.MainPhoneNumberID)
LEFT JOIN PhoneNumber F ON (F.ID = A.MainFaxNumberID)
LEFT JOIN Address Addr ON Addr.ID = A.BillingAddressID
WHERE A.IsClient = 1
) TempTable
WHERE COALESCE(PreTaxSales, 0)  0
ORDER BY PreTaxSales DESC

SQL for Control 3.x

DECLARE @StartDateTime datetime;
DECLARE @EndDateTime datetime;
SET @StartDateTime = '1/1/2009';
SET @EndDateTime = '1/1/2010'
SELECT * FROM
(
SELECT A.AccountNumber, A.CompanyName, M.ItemName AS Industry, AC.Title, (AC.FirstName +' '+  AC.LastName) AS ContactName, AC.Position, 
P.FormattedText AS PhoneNumber, 
F.FormattedText AS FaxNumber, 
AC.EmailAddress, 
Addr.StreetAddress1, Addr.StreetAddress2, Addr.City, Addr.State, Addr.PostalCode,
(SELECT -SUM(Amount) FROM GL WHERE AccountID = A.ID AND GLAccountClassTypeID = 8012 AND GL.EntryDateTime BETWEEN @StartDateTime AND @EndDateTime ) AS PreTaxSales
FROM Account A
LEFT JOIN AccountContact AC ON (A.PrimaryContactID = AC.ID)
LEFT JOIN MarketingListItem M ON (A.IndustryID = M.ID)
LEFT JOIN PhoneNumber P ON (P.ID = A.MainPhoneNumberID)
LEFT JOIN PhoneNumber F ON (F.ID = A.MainFaxNumberID)
LEFT JOIN Address Addr ON Addr.ID = A.BillingAddressID
WHERE A.IsClient = 1
) TempTable
WHERE COALESCE(PreTaxSales, 0)  0
ORDER BY PreTaxSales DESC

Version Information