Table of Contents

{$page}

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_formatsql

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