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
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
You could leave a comment if you were logged in.