{$page}

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

code_formatsql

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

code

SQL for Control 3.x