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.

None. This is a selection query and no data is modified in the running of it.

Current SQL

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

(

  1. - 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.