======  ======



===== Explanation of SQL =====



This query provides a breakdown of the Amounts in the Cost of WIP & Built Report. This report is available in Control, the name of the crystal report template is **Accounting_CostofWipBuilt**. This report is It is formatted as an SQL Report. To run it for all time, set the End Date beyond the current date.



===== Risk of Data Corruption if Run Improperly =====

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



===== SQL =====



<code sql>
declare @EndDateTime    DateTime;
set @EndDateTime   = Cast('11/24/2014 00:00:00' as DateTime); -- comment this line when inserting into Crystal Reports
--set @EndDateTime   = {?fx_RangeEnd_Date}; -- remove the comment from this line when inserting into Crystal Reports
SELECT
  Account.CompanyName
  ,TransHeader.OrderNumber
  ,EmployeeGroup.DivisionName
  ,TransHeader.Description
  ,TransHeader.StatusText
  ,TransHeader.OrderCreatedDate
  ,TransHeader.BuiltDate
  ,TransHeader.ID as OrderID
  ,(Case
		When Transheader.BuiltDate is null or (@EndDateTime Between TransHeader.OrderCreatedDate and Transheader.BuiltDate)
		Then 'WIP'
		When Transheader.SaleDate is null or (@EndDateTime Between TransHeader.BuiltDate and Transheader.SaleDate)
		Then 'Built'
		When Transheader.ClosedDate is null or (@EndDateTime Between TransHeader.SaleDate and Transheader.ClosedDate)
		Then 'Sale'
		When @EndDateTime > Transheader.ClosedDate
		Then 'Closed'
                                Else 'Unknown'
	End) as StatusAsOfDate
  
  ,TransHeader.SubtotalPrice
  ,(SELECT SUM(Amount)
      FROM Ledger with(nolock) LEFT OUTER JOIN Part with(nolock) ON Ledger.PartID = Part.ID
      WHERE Ledger.TransactionID = TransHeader.ID AND GLAccountID = 34
      AND EntryDateTime