This SQL demonstrates a simple production tracking report you can implement at the order level.

To use it, create an order UDF called “Production Date” that is required. Enter this date for all orders (since it is required)! This report generates a list of orders with a production date in the specified period, sorted by status (Closed, then Sale, then Built, then WIP). The data is then organized by the ORDER station (not the line item station).

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

DECLARE @StartDate datetime;
DECLARE @EndDate datetime;
-- set @StartDate = '4/5/2010';
-- set @EndDate = '4/30/2010';
SET @StartDate = ;
SET @EndDate = ;
SELECT  
        DatePart( Week, UDF.Production_Date) AS [Week],
        CASE DatePart( WeekDay, UDF.Production_Date) 
          WHEN 1 THEN 'Sunday'
          WHEN 2 THEN 'Monday'
          WHEN 3 THEN 'Tuesday'
          WHEN 4 THEN 'Wednesday'
          WHEN 5 THEN 'Thursday'
          WHEN 6 THEN 'Friday'
          WHEN 7 THEN 'Saturday'
          ELSE '??'
        END AS [DAY OF Week],
		MONTH(UDF.Production_Date) AS [MONTH], 
        DAY(UDF.Production_Date) AS [DAY],
		(SELECT CompanyName FROM Account WHERE ID = TH.AccountID) AS [Customer],
		TH.OrderNumber AS [ORDER NUMBER],
		TH.Description AS [Description],
        CASE WHEN TH.StatusID = 1 
		  THEN COALESCE((SELECT StationName FROM Station WHERE Station.ID = TH.StationID), 'WIP') 
          ELSE TH.StatusText
        END AS [STATUS],
        CASE WHEN TH.StatusID = 1 
          THEN COALESCE(UDF.Production_Percent_Complete, 0) 
          ELSE 100
        END AS [Percent Complete],
		TH.DueDate AS [Due DATE],
		TH.BuiltDate AS [Built DATE],
		TH.SaleDate AS [Sale DATE],
        CASE WHEN TH.StatusID = 1 
          THEN CAST(TH.SubTotalPrice * COALESCE(UDF.Production_Percent_Complete, 0) AS Money)
          ELSE CAST(TH.SubTotalPrice AS Money)
        END AS [Sales Completed],
		CAST(TH.SubTotalPrice AS Money) AS [PreTax Sales Total],
		TH.AccountID,
		TH.StatusID,
		TH.StationID,
        UDF.Production_Date AS [Production DATE],
		TH.ID AS [TransHeaderID]
FROM TransHeader TH WITH(nolock)
LEFT JOIN TransHeaderUserField UDF WITH(nolock) ON TH.ID = UDF.ID
WHERE (UDF.Production_Date BETWEEN @StartDate AND @EndDate)
 
ORDER BY [Week], [StatusID] DESC, [Production DATE], [Due DATE], [STATUS], [Customer], [ORDER NUMBER]

• Entered : 4/2010
• Version : 4.2+