April 17, 2014 at 1:32 pm
Hi J Livingston
thank you so much for bringing up the oversimplification point.
This is what I can say about the questions you posted.
Item being deleted from order - This is something I want to show for. It will be the opposite of for order S19856 in which a 3rd Item gets added to the order at a different time
Order Lines being shipped/posted on different days - This is something we do not want to go to at this moment
change to location code - We can get rid of Location Code, do not need to show it anymore.
Quantity Ordered <> Quantity Shipped - Quantity Shipped might now be the same as the Quantity Ordered in version 1, but it always be equal to Quantity Ordered from last and previous to last version as far as I have seen in the data table.
I talked to the requester and what they are trying to to is to simplify this report and what changes to look for.
I updated the mock-up file, I got rid of the Quantity Changed column. User wants to see the Quantity changes in the Quantity column.
ALso I found out that the last version of an order has a flag: Invoice = 1
April 17, 2014 at 2:47 pm
I started creating a stored procedure in which i create two variable tables, one to store all the orders where version number = 1, and type = 2 (item) which I called
DECLARE @SalesOrders_Version_First
I then created another variable table that stored orders with a column Invoice = 1, type = 2 (item) and Quantity <> 0
My issue really resides in finding the other versions of an Order in which an Item gets added, or a Quantity gets modified.
Here is my stored proc. If you can help me figuring out some of the other code, that would be very kind.
Thank you much.
AS
BEGIN
DECLARE @SalesOrders_Version_First TABLE
(
[OrderNumber]NVARCHAR(20)
,[ItemNumber]NVARCHAR(20)
,[OrderDate]DATETIME
,[RequestedDeliveryDate]DATETIME
,[PromisedDeliveryDate] DATETIME
,[OrderQuantity]DECIMAL(38,18)
,[PostingDate]DATETIME
,[ShippedQuantity]DECIMAL(38,18)
)
INSERT INTO @SalesOrders_Version_First
(
[OrderNumber]
,[VersionNumber]
,[ItemNumber]
,[OrderDate]
,[RequestedDeliveryDate]
,[PromisedDeliveryDate]
,[OrderQuantity]
,[PostingDate]
,[ShippedQuantity]
)
SELECT DISTINCT
sha.[Order Date]
,sha.[Requested Delivery Date]
,sha.[Promised Delivery Date]
,sla.Quantity
,sha.[Posting Date]
,sla.[Quantity Shipped]
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 sla.[Version No_] = 1
AND sha.[No_] IN ('S19856', 'S20026')
--AND sha.[No_] = 'S19856'
--AND sha.[No_] = 'S20026'
--AND sha.[No_] = 'S20302'
DECLARE @SalesOrders_Version_Last TABLE
(
[OrderNumber]NVARCHAR(20)
,[VersionNumber]NVARCHAR(10)
,[ItemNumber]NVARCHAR(20)
,[OrderDate]DATETIME
,[RequestedDeliveryDate]DATETIME
,[PromisedDeliveryDate] DATETIME
,[OrderQuantity]DECIMAL(38,18)
,[PostingDate]DATETIME
,[ShippedQuantity]DECIMAL(38,18)
)
INSERT INTO @SalesOrders_Version_Last
(
[OrderNumber]
,[VersionNumber]
,[ItemNumber]
,[OrderDate]
,[RequestedDeliveryDate]
,[PromisedDeliveryDate]
,[OrderQuantity]
,[PostingDate]
,[ShippedQuantity]
)
SELECT DISTINCT
s.[OrderNumber]
,s.[VersionNumber]
,s.[ItemNumber]
,s.[OrderDate]
,s.[RequestedDeliveryDate]
,s.[PromisedDeliveryDate]
,s.[QuantityOrdered]
,s.[PostingDate]
,s.[ShippedQuantity]
FROM
(
SELECT DISTINCT
sha.No_
,sha.[Version No_]
,sla.[No_]
,sha.[Order Date]
,sha.[Requested Delivery Date]
,sha.[Promised Delivery Date]
,sla.Quantity
,sha.[Posting Date]
,sla.[Quantity Shipped]
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 sha.[Posting Date] IS NOT NULL
AND sla.[Type] = 2
AND sla.[Completely Shipped] = 1
AND sla.[Quantity Shipped] <> 0
AND sha.[No_] IN ('S19856', 'S20026')
--AND sha.[No_] = 'S19856'
--AND sha.[No_] = 'S20026'
--AND sha.[No_] = 'S20302'
) s
--DECLARE @SalesOrders_Version_Changes TABLE
--(
--[OrderNumber]NVARCHAR(20)
--,[ItemNumber]NVARCHAR(20)
--,[OrderDate]DATETIME
--,[RequestedDeliveryDate]DATETIME
--,[PromisedDeliveryDate] DATETIME
--,[OrderQuantity]DECIMAL(38,18)
--,[PostingDate]DATETIME
--,[ShippedQuantity]DECIMAL(38,18)
--)
--INSERT INTO @SalesOrders_Version_Changes
--(
--[OrderNumber]
--,[ItemNumber]
--,[OrderDate]
--,[RequestedDeliveryDate]
--,[PromisedDeliveryDate]
--,[OrderQuantity]
--,[PostingDate]
--,[ShippedQuantity]
--)
--SELECT DISTINCT
--sha.No_
--,sla.[No_]
--,sha.[Order Date]
--,sha.[Requested Delivery Date]
--,sha.[Promised Delivery Date]
--,sla.Quantity
--,sha.[Posting Date]
--,sla.[Quantity Shipped]
--FROM NAV.dbo.[CQC$Sales Header Archive] sha
--LEFT OUTER JOIN NAV.dbo.[CQC$Sales Line Archive] sla
--ON sha.[No_] = sla.[Document No_]
--AND sha.[Version No_] = sla.[Version No_]
--LEFT OUTER JOIN @SalesOrders_Version_First so_vf
--ON sha.[No_] COLLATE DATABASE_DEFAULT = so_vf.[OrderNumber]
--LEFT OUTER JOIN @SalesOrders_Version_Last so_vl
--ON sha.[No_] COLLATE DATABASE_DEFAULT = so_vl.[OrderNumber]
--WHERE sla.[Type] = 2
--AND sla.[Version No_] NOT IN (SELECT VersionNumber FROM @SalesOrders_Version_First)
--AND sla.[Quantity] NOT IN (SELECT OrderQuantity FROM @SalesOrders_Version_First)
--AND sla.[Version No_] NOT IN (SELECT VersionNumber FROM @SalesOrders_Version_Last)
--AND sla.[Quantity] NOT IN (SELECT ShippedQuantity FROM @SalesOrders_Version_Last)
--AND sla.[Quantity Shipped] = 0
--AND sha.[No_] IN ('S19856', 'S20026')
--AND sha.[No_] = 'S19856'
--AND sha.[No_] = 'S20026'
--AND sha.[No_] = 'S20302'
SELECT * FROM @SalesOrders_Version_First
SELECT * FROM @SalesOrders_Version_Last
--SELECT * FROM @SalesOrders_Version_Changes
END
April 18, 2014 at 4:01 am
does this get anywhere close...??
;
WITH cte
AS (
SELECT SalesOrderNumber,
ItemNumber,
QuantityOrdered,
MIN(VersionNumber) AS VerMin,
MAX(VersionNumber) AS VerMax
FROM Orders
GROUP BY SalesOrderNumber,
ItemNumber,
QuantityOrdered
)
SELECT cte.SalesOrderNumber,
cte.ItemNumber,
OMin.OrderDate,
OMin.RequestedDeliveryDate,
OMin.PromisedDeliveryDate,
OMin.QuantityOrdered,
OMax.PostingDate,
OMax.QuantityShipped
FROM Orders AS OMin
INNER JOIN cte ON OMin.SalesOrderNumber = cte.SalesOrderNumber
AND OMin.VersionNumber = cte.VerMin
AND OMin.ItemNumber = cte.ItemNumber
INNER JOIN Orders AS OMax ON cte.SalesOrderNumber = OMax.SalesOrderNumber
AND cte.VerMax = OMax.VersionNumber
AND cte.ItemNumber = OMax.ItemNumber
edit...tidied up the formatting :hehe:
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 18, 2014 at 11:52 am
I would like to try your code against my real tables, but how would I replace Orders? that is a table I just created to provide as an example.
When I ran it against the Orders table, it worked nicely.
April 18, 2014 at 12:07 pm
itortu (4/18/2014)
I would like to try your code against my real tables, but how would I replace Orders? that is a table I just created to provide as an example.When I ran it against the Orders table, it worked nicely.
what is your real table name?
assuming your test data reflects your real environment...just swap the names accordingly
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 18, 2014 at 12:13 pm
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'
I noticed that if I run your code against the Orders table the results are accurate.
When I replace every instance of the Orders table with the sql above (which I think is twice in your code) then the results are different. It returns an additional version
April 18, 2014 at 12:22 pm
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
April 18, 2014 at 1:25 pm
I am uploading the script and sample output file as requested.
Thank you for your help.
April 18, 2014 at 1:38 pm
using your setup script
SET NOCOUNT ON
CREATE TABLE SampleOrders (
[SalesOrderNumber] NVARCHAR(20),
[VersionNumber] NVARCHAR(10),
[ItemNumber] NVARCHAR(20),
[OrderDate] DATETIME,
[RequestedDeliveryDate] DATETIME,
[LeadTimeReqDeliveryDate] INT,
[PromisedDeliveryDate] DATETIME,
[LeadTimePromisedDeliveryDate] INT,
[QuantityOrdered] DECIMAL(38, 18),
[PostingDate] DATETIME,
[ShippingTime] INT,
[ActualDeliveryDate] DATETIME,
[LeadTimeActualDeliveryDate] INT,
[ShippedQuantity] DECIMAL(38, 18)
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
1,
'10568',
'2014-02-13 00:00:00.000',
'2014-03-14 00:00:00.000',
29,
'2014-03-14 00:00:00.000',
29,
22000.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
1,
'12309',
'2014-02-13 00:00:00.000',
'2014-03-14 00:00:00.000',
29,
'2014-03-14 00:00:00.000',
29,
10000.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
2,
'10568',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
22000.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
2,
'12309',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
10000.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
3,
'10526',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
7650.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
3,
'10568',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
22000.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
3,
'12309',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
10000.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
4,
'10526',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
7650.00000000000000000000,
'2014-03-09 00:00:00.000',
1,
'2014-03-10 00:00:00.000',
25,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
4,
'10568',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
22000.00000000000000000000,
'2014-03-09 00:00:00.000',
1,
'2014-03-10 00:00:00.000',
25,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
4,
'12309',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
10000.00000000000000000000,
'2014-03-09 00:00:00.000',
1,
'2014-03-10 00:00:00.000',
25,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
5,
'10526',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
7650.00000000000000000000,
'2014-03-09 00:00:00.000',
1,
'2014-03-10 00:00:00.000',
25,
7650.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
5,
'10568',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
22000.00000000000000000000,
'2014-03-09 00:00:00.000',
1,
'2014-03-10 00:00:00.000',
25,
22000.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S19856',
5,
'12309',
'2014-02-13 00:00:00.000',
'2014-03-10 00:00:00.000',
25,
'2014-03-10 00:00:00.000',
25,
10000.00000000000000000000,
'2014-03-09 00:00:00.000',
1,
'2014-03-10 00:00:00.000',
25,
10000.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S20026',
1,
'10568',
'2014-02-21 00:00:00.000',
'2014-03-14 00:00:00.000',
21,
'2014-03-14 00:00:00.000',
21,
40000.00000000000000000000,
'1753-01-01 00:00:00.000',
0,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S20026',
2,
'10568',
'2014-02-21 00:00:00.000',
'2014-03-14 00:00:00.000',
21,
'2014-03-14 00:00:00.000',
21,
40000.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S20026',
3,
'10568',
'2014-02-21 00:00:00.000',
'2014-03-14 00:00:00.000',
21,
'2014-03-14 00:00:00.000',
21,
40000.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S20026',
4,
'10568',
'2014-02-21 00:00:00.000',
'2014-03-14 00:00:00.000',
21,
'2014-03-14 00:00:00.000',
21,
23000.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S20026',
5,
'10568',
'2014-02-21 00:00:00.000',
'2014-03-14 00:00:00.000',
21,
'2014-03-14 00:00:00.000',
21,
23000.00000000000000000000,
'1753-01-01 00:00:00.000',
1,
NULL,
NULL,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S20026',
6,
'10568',
'2014-02-21 00:00:00.000',
'2014-03-14 00:00:00.000',
21,
'2014-03-14 00:00:00.000',
21,
24450.00000000000000000000,
'2014-03-14 00:00:00.000',
0,
'2014-03-14 00:00:00.000',
21,
0.00000000000000000000
)
INSERT INTO SampleOrders (
[SalesOrderNumber],
[VersionNumber],
[ItemNumber],
[OrderDate],
[RequestedDeliveryDate],
[LeadTimeReqDeliveryDate],
[PromisedDeliveryDate],
[LeadTimePromisedDeliveryDate],
[QuantityOrdered],
[PostingDate],
[ShippingTime],
[ActualDeliveryDate],
[LeadTimeActualDeliveryDate],
[ShippedQuantity]
)
VALUES (
'S20026',
7,
'10568',
'2014-02-21 00:00:00.000',
'2014-03-14 00:00:00.000',
21,
'2014-03-14 00:00:00.000',
21,
24450.00000000000000000000,
'2014-03-14 00:00:00.000',
0,
'2014-03-14 00:00:00.000',
21,
24450.00000000000000000000
)
SET NOCOUNT OFF;
WITH cte
AS (
SELECT SalesOrderNumber,
ItemNumber,
QuantityOrdered,
MIN(VersionNumber) AS VerMin,
MAX(VersionNumber) AS VerMax
FROM SampleOrders
GROUP BY SalesOrderNumber,
ItemNumber,
QuantityOrdered
)
SELECT cte.SalesOrderNumber,
cte.ItemNumber,
OMin.OrderDate,
OMin.RequestedDeliveryDate,
OMin.PromisedDeliveryDate,
OMin.QuantityOrdered,
OMax.PostingDate,
OMax.ShippedQuantity
FROM SampleOrders AS OMin
INNER JOIN cte ON OMin.SalesOrderNumber = cte.SalesOrderNumber
AND OMin.VersionNumber = cte.VerMin
AND OMin.ItemNumber = cte.ItemNumber
INNER JOIN SampleOrders AS OMax ON cte.SalesOrderNumber = OMax.SalesOrderNumber
AND cte.VerMax = OMax.VersionNumber
AND cte.ItemNumber = OMax.ItemNumber
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 18, 2014 at 1:48 pm
as a sidebar.....is there a reason why you cannot get a solution to you problem from your NAV reseller?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 22, 2014 at 1:04 pm
Hello and thank you for your help.
Your query works very well for what I need it to do.
I am not approaching to our NAV vendor because I wanted to be able to produce a report written in-house, and at times their response time is not the best.
I really appreciate your help on this question. You are a life saver.
If I could ask one more thing without wanting to be abusive. You see, I need to have a calculated column called "Fill Rate"
That uses the formula (as I have it so far but not working):
, CASE
WHEN OMax.PostingDate IS NOT NULL THEN
ISNULL(OMax.ShippedQuantity * 1.0 / NULLIF(OMin.[QuantityOrdered], 0), 0) * 100
ELSE NULL
END AS FillRate
The Fill Rate should only by calculated for the latest version that contains the quantity shipped, It uses the quantity shipped and the Original Quantity Ordered.
My question is, how can I access the Original Quantity Ordered from version1 to actually come up with a more accurate percentage?
I thought that using OMin.QuantityOrdered and OMax.ShippedQuantity would give me that, but OMinQuantityOrderd for that row, is always equal to ShippedQuantity si it always gives me 100%
An example would be order S20026, its original quantity was 40,000 but the shipped quantity was actually 24,450.
Running this sql:
SELECT ISNULL(24450 * 1.0 / NULLIF(40000, 0), 0) * 100
The Fill Rate would be: 61.13
S20026105682014-02-21 00:00:00.0002014-03-14 00:00:00.000212014-03-14 00:00:00.0002140000.00000000000000000000NULLNULLNULLNULL0.000000
S20026105682014-02-21 00:00:00.0002014-03-14 00:00:00.000212014-03-14 00:00:00.0002123000.00000000000000000000NULLNULLNULLNULL0.000000
S20026105682014-02-21 00:00:00.0002014-03-14 00:00:00.000212014-03-14 00:00:00.0002124450.000000000000000000002014-03-14 00:00:00.0002014-03-14 00:00:00.0002124450.00000000000000000000
April 22, 2014 at 1:50 pm
itortu (4/22/2014)
Hello and thank you for your help.Your query works very well for what I need it to do.
I am not approaching to our NAV vendor because I wanted to be able to produce a report written in-house, and at times their response time is not the best.
I really appreciate your help on this question. You are a life saver.
If I could ask one more thing without wanting to be abusive. You see, I need to have a calculated column called "Fill Rate"
That uses the formula (as I have it so far but not working):
, CASE
WHEN OMax.PostingDate IS NOT NULL THEN
ISNULL(OMax.ShippedQuantity * 1.0 / NULLIF(OMin.[QuantityOrdered], 0), 0) * 100
ELSE NULL
END AS FillRate
The Fill Rate should only by calculated for the latest version that contains the quantity shipped, It uses the quantity shipped and the Original Quantity Ordered.
My question is, how can I access the Original Quantity Ordered from version1 to actually come up with a more accurate percentage?
I thought that using OMin.QuantityOrdered and OMax.ShippedQuantity would give me that, but OMinQuantityOrderd for that row, is always equal to ShippedQuantity si it always gives me 100%
An example would be order S20026, its original quantity was 40,000 but the shipped quantity was actually 24,450.
Running this sql:
SELECT ISNULL(24450 * 1.0 / NULLIF(40000, 0), 0) * 100The Fill Rate would be: 61.13
S20026105682014-02-21 00:00:00.0002014-03-14 00:00:00.000212014-03-14 00:00:00.0002140000.00000000000000000000NULLNULLNULLNULL0.000000
S20026105682014-02-21 00:00:00.0002014-03-14 00:00:00.000212014-03-14 00:00:00.0002123000.00000000000000000000NULLNULLNULLNULL0.000000
S20026105682014-02-21 00:00:00.0002014-03-14 00:00:00.000212014-03-14 00:00:00.0002124450.000000000000000000002014-03-14 00:00:00.0002014-03-14 00:00:00.0002124450.00000000000000000000
in your result set...on what row are you expecting to see the fill factor...please provide example.
also...is it really necessary to see all amendments on separate rows....could you not just show original qty / shipped qty and fill factor? what doe s the business say?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 22, 2014 at 2:45 pm
what row are you expecting to see the fill factor...please provide example.
the filled rate should go in the last row, that is the last version of an order row. see attached file
is it really necessary to see all amendments on separate rows....could you not just show original qty / shipped qty and fill factor? what doe s the business say?
The business requires to show every change that occurs in an order quantity from when it is originally entered into the system until its shipped.
In the provided example, I think
Original Ordered Quantity could show as
40,000
23,000
no more changes in quantity then last version instead that showing a value in original ordered quantity, would show a value under shipped quantity.
Thank you for your help.
April 23, 2014 at 7:31 am
Hi, I was wondering if the post I made sort of were clear. I have been working on the query myself trying to get to that quantity but still no luck.
I tried using a left join to get to the quantity ordered in version 1 but that does not work for some orders like S19856 that contain and Item that was added on version 3.
I understand if you do not have time to help right now, just thought I made sure I was not missing anything.
Thank you kindly.
April 23, 2014 at 8:00 am
itortu (4/23/2014)
Hi, I was wondering if the post I made sort of were clear. I have been working on the query myself trying to get to that quantity but still no luck.I tried using a left join to get to the quantity ordered in version 1 but that does not work for some orders like S19856 that contain and Item that was added on version 3.
I understand if you do not have time to help right now, just thought I made sure I was not missing anything.
Thank you kindly.
bit busy at the moment:-)
think you need to break this out into parts......you have a solution that works for your first problem...so maybe use that as a CTE/temp table and possibly ROW_NUMBER() and join back to min.date/qty....quick thinks!!
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply