This query provides the basic information for tracking the time that line items stay in a particular station. Currently this query pulls all station changes within a specific time period. In order to use this for your particular use, you may need to modify it to pull all WIP orders, or all Sale Orders, etc. for that time period.

Note: This requires that the option to create journal entries on station changes be set up under

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

DECLARE @startdate datetime;
DECLARE @enddate datetime;
-- uncomment this for SQL, comment this out for Cyrious SQL Reports
SET @startdate = '4/15/2010';
SET @enddate   = '4/30/2010 11:59';
-- comment this out for SQL, uncomment this for Cyrious SQL Reports
-- set @startdate = ;
-- set @enddate   = ;
SELECT top 100
    (CASE WHEN TH.TransactionType IN (1,6) THEN TH.OrderNumber ELSE NULL END) AS OrderNumber,
    (CASE WHEN TH.TransactionType = 2 THEN TH.OrderNumber ELSE NULL END) AS EstimateNumber,
    TD.LineItemNumber,
    TD.GoodsItemCode AS Product,
    CONVERT(VARCHAR(16), StartDateTime, 20) AS StartDate,
    CONVERT(VARCHAR(16), EndDateTime, 20) AS EndDate,
    (SELECT StationName FROM Station WHERE ID = Journal.StationID) AS Station,
    DateDiff(MINUTE, StartDateTime, EndDateTime)/60.0 AS HoursIn,
    TH.Description AS OrderDescription,
    (SELECT CompanyName FROM Account WHERE ID = Journal.AccountID) AS CompanyName,
    (SELECT LastName +', '+FirstName FROM Employee WHERE ID = EmployeeID) AS EmployeeName,
    Journal.StationID,
    TransactionID AS TransHeaderID,
    Journal.LinkID AS TransDetailID,
    Journal.AccountID,
    EmployeeID
FROM Journal
LEFT JOIN TransDetail TD ON TD.ID = Journal.LinkID
LEFT JOIN TransHeader TH ON TH.ID = Journal.TransactionID
WHERE startdatetime < > enddatetime AND JournalActivityType = 45
  AND (StartDateTime BETWEEN @startdate AND @enddate OR EndDateTime BETWEEN @startdate AND @enddate)
  AND Journal.TransactionID IS NOT NULL
  AND Journal.StationID IS NOT NULL
ORDER BY OrderNumber, LineItemNumber, StartDateTime, Station  DESC

• Entered : 08/2010
• Version : Control 4.3+