Differences

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

Link to this comparison view

control_sql_-_historical_wip_report [2019/01/27 11:28]
 control_sql_-_historical_wip_report [2019/01/27 11:28] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +==Explanation of SQL==
 +
 +
 +
 +There is no report in Control that will produce a WIP report for a period in the past.  This SQL will pull that information and produce a WIP 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 =====
 +
 +
 +
 +===Current SQL===
 +
 +
 +
 +[[code_formatesql|code formate="sql"]]
 +
 +
 +
 +DECLARE @WIPDate DateTime;
 +
 +
 +
 +SET @WIPDate = '12/31/2008 23:59:59';
 +
 +
 +
 +SELECT
 +
 +
 +
 +    (SELECT DivisionName FROM EmployeeGroup WHERE EmployeeGroup.ID = GLList.DivisionID) AS DivisionName,
 +
 +
 +
 +    TH.OrderNumber AS [ORDER Number],
 +
 +
 +
 +    TH.OrderCreatedDate AS [ORDER Date],
 +
 +
 +
 +    substring(TH.Description, 1, 50) AS [ORDER Description],
 +
 +
 +
 +    Account.CompanyName AS [Company Name],
 +
 +
 +
 +    GLList.*,
 +
 +
 +
 +    Account.ID as AccountID
 +
 +
 +
 +FROM
 +
 +
 +
 +(
 +    -- Select the List of Orders and Divisions with the Total
 +
 +
 +
 +    SELECT 
 +
 +
 +
 +      Sum(Amount) AS [WIP Balance],
 +
 +
 +
 +      GL.TransactionID AS TransHeaderID, 
 +
 +
 +
 +      GL.DivisionID,
 +
 +
 +
 +      (Select top 1 AccountID from GL as GL2 where GL2.TransactionID = GL.TransactionID order by EntryDateTime DESC) AS LastAccountID
 +
 +
 +
 +     
 +
 +
 +
 +    FROM GL
 +
 +
 +
 +    WHERE GLAccountID = 11  --WIP Account
 +
 +
 +
 +    AND EntryDateTime