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.

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

<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