Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_historical_built_report [2019/01/27 11:28]
 control_sql_-_historical_built_report [2019/01/27 11:28] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +==Explanation of SQL==
 +
 +
 +
 +There is no report in Control that will produce a Built report for a period in the past.  This SQL will pull that information and produce a Built report as of the specified time. 
 +
 +
 +
 +To Use: Change the date at the start of the report to the target date and time.
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**None**. This is a selection query and no data is modified in the running of it.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +DECLARE @BuiltDate DateTime;
 +SET @BuiltDate = '12/31/2008 23:59:59';
 +select 
 +  (select DivisionName from EmployeeGroup where EmployeeGroup.ID = GL.DivisionID) as DivisionName,
 +  TH.OrderNumber as [Order Number],
 +  TH.BuiltDate as [Built Date],
 +  substring(TH.Description, 1, 50) as [Order Description],
 +  Account.CompanyName as [Company Name],
 +  Sum(Amount) as [Built Balance],
 +  GL.TransactionID as TransHeaderID, 
 +  GL.AccountID,
 +  GL.DivisionID
 +from GL
 +left join Account on GL.AccountID = Account.ID
 +left join TransHeader TH on TH.ID = GL.TransactionID
 +where GLAccountID = 12  --Built Account
 +and EntryDateTime