Re: 2nd try

On 8 Nov 2004 15:53:13 -0800, joel-garry_at_home.com (Joel Garry) wrote:

>Patrice Béliveau <pbel_at_hotmail.com> wrote in message news:<0qpno0htqfujamp7mmgubkaqdffv96qp6v_at_4ax.com>...
>> Hi,
>>
>> I have two table, one is containing a dated list of delivery to do and
>> the second contain a list of delivery done.
>>
>> ex: Table 1 --> Order No,
>> Delivery date requested,
>> Quantity requested
>>
>> Table 2 --> Shipping No.
>> Order No.
>> Date shipped
>> Quantity shipped
>>
>>
>> They are not into the same list because there is no one on one
>> relation between those two list (ex: some delivery might have been
>> back order, or that might have been some over shipment, etc...)
>>
>> The QUESTION:
>>
>> what query can I do to known where am I at in my delivery schedule?
>>
>>
>> What I would like is something like a list with
>> Delivery date requested
>> Quantity requested
>> Quantity shipped
>>
>> The only that I can see of how to do it, it's to write a function that
>> will figure out how much have been shipped for each delivery, but this
>> cause heavy overhead.
>>
>> Another way of looking at it would be:
>>
>> - Give me all Order No, where SUM(quantity requested) > SUM(quantity
>> shipped).
>>
>> but then sum on the left side of a test is not permitted
>
>Why do you need the left side sum? Are duplicate order numbers allowed in Table 1?

There is no duplicate order, but multiple "requested delivery" (a delivery schedule) for one order. What I need to know is which order are fulfill and which are not.
>
>>
>> So, I'm stuck, I do not know how to proceed
>>
>> Thanks
>
>jg
Received on Tue Nov 09 2004 - 09:35:35 CST