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