Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Get original order quantities and dates, and find any changes or additions to quantities in subsequent order version(s) Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 12:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 9, 2014 6:20 PM
Points: 65, Visits: 101
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.Quantity AS [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
END AS [ActualDeliveryDate]
, NULL AS [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
Post #1563129
Posted Friday, April 18, 2014 12:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:06 AM
Points: 1,917, Visits: 19,596
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.Quantity AS [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
END AS [ActualDeliveryDate]
, NULL AS [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
Post #1563131
Posted Friday, April 18, 2014 1:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 9, 2014 6:20 PM
Points: 65, Visits: 101
I am uploading the script and sample output file as requested.
Thank you for your help.


  Post Attachments 
SampleOrders_Create_Insert_Script.txt (3 views, 11.12 KB)
sampleoutput.xlsx (13 views, 10.13 KB)
Post #1563155
Posted Friday, April 18, 2014 1:38 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:06 AM
Points: 1,917, Visits: 19,596
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
Post #1563160
Posted Friday, April 18, 2014 1:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:06 AM
Points: 1,917, Visits: 19,596
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
Post #1563163
Posted Tuesday, April 22, 2014 1:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 9, 2014 6:20 PM
Points: 65, Visits: 101
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


S20026	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	NULL	NULL	NULL	NULL	0.000000
S20026 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 NULL NULL NULL NULL 0.000000
S20026 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 2014-03-14 00:00:00.000 21 24450.00000000000000000000

Post #1563998
Posted Tuesday, April 22, 2014 1:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:06 AM
Points: 1,917, Visits: 19,596
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) * 100

The Fill Rate would be: 61.13


S20026	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	NULL	NULL	NULL	NULL	0.000000
S20026 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 NULL NULL NULL NULL 0.000000
S20026 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 2014-03-14 00:00:00.000 21 24450.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
Post #1564012
Posted Tuesday, April 22, 2014 2:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 9, 2014 6:20 PM
Points: 65, Visits: 101
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.


  Post Attachments 
sample-output.xlsx (12 views, 10.31 KB)
Post #1564040
Posted Wednesday, April 23, 2014 7:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 9, 2014 6:20 PM
Points: 65, Visits: 101
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.
Post #1564262
Posted Wednesday, April 23, 2014 8:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:06 AM
Points: 1,917, Visits: 19,596
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
Post #1564280
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse