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
- Entered : 02/2010
- Version : Control 3.x, Control 4.x
You could leave a comment if you were logged in.