itortu (4/18/2014)
Down below is the SQL that creates the "Orders" table. It includes every version of an Order. In the code below, I used it to retrieve Order S20026. I returns its 7 versions.
SELECT DISTINCT
sha.[Bill-to Name]AS [CustomerNameBillTo]
,sha.[Bill-to Customer No_]AS [CustomerNumberBillTo]
,sha.[Sell-to Customer No_]AS [CustomerNumberSellTo]
,sha.[Sell-to Customer Name]AS [CustomerNameSellTo]
,sha.No_AS [SalesOrderNumber]
,sha.[Version No_]AS [VersionNumber]
,sla.[No_]AS [ItemNumber]
,sha.[Order Date]AS [OrderDate]
,sha.[Requested Delivery Date]AS [RequestedDeliveryDate]
,DATEDIFF(DAY, sha.[Order Date], sha.[Requested Delivery Date]) AS [LeadTimeReqDeliveryDate]
,sha.[Promised Delivery Date] AS [PromisedDeliveryDate]
,DATEDIFF(DAY, sha.[Order Date], sha.[Promised Delivery Date]) AS [LeadTimePromisedDeliveryDate]
,sla.QuantityAS [QuantityOrdered]
,sha.[Posting Date]AS [PostingDate]
,CONVERT(INT, REPLACE(sha.[Shipping Time], CHAR(2), '')) AS [ShippingTime]
,CASE
WHEN YEAR(sha.[Posting Date]) <> '1753'
THEN DATEADD(DAY, CONVERT(INT, REPLACE(sha.[Shipping Time], CHAR(2), '')), sha.[Posting Date])
ELSE NULL
ENDAS [ActualDeliveryDate]
,NULLAS [LeadTimeActualDeliveryDate]
,sla.[Quantity Shipped]AS [ShippedQuantity]
FROM NAV.dbo.[Sales Header Archive] sha
LEFT OUTER JOIN NAV.dbo.[Sales Line Archive] sla
ON sha.[No_] = sla.[Document No_]
AND sha.[Version No_] = sla.[Version No_]
WHERE sla.[Type] = 2
AND sha.No_ = 'S20026'
lets move this along a bit can we?
...you have two tables....SalesHeaderArchive and SalesLineArchive.....yes??
post some sample set up SQL scripts and data that CLEARLY show your data...and expected results please.
Happy to help you on your way
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day