====== {$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 =====



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



[[code]]



===== Version Information =====
  * Entered : 02/2010
  * Version : Control 3.x, Control 4.x



===== Related SQLs =====
  * Backlinks [[include_pagepage_componentbacklinks]]