Re: 2nd try

On Fri, 05 Nov 2004 15:53:55 -0500, Patrice Béliveau <pbel_at_hotmail.com> wrote:

>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
>
>So, I'm stuck, I do not know how to proceed
>
>Thanks

To make thing simplier:

Table 1:		Order_No (PK)
		Delivery_date (PK)
		Quantity

Table 2:		Shipping_no (PK)
		Order_no
		Quantity

I want to find out Order_no where the sum of all quantity in table_1 of the same Order_no is greater than the sum of all quantity in table_2 of the same Order_no

I know that I cannot do that, but it would look like

SELECT 	T1.ORDER_NO 
	FROM 	TABLE_1 T1
	WHERE 	(SELECT SUM(T2.QUANTITY) 
			FROM TABLE_1 T2 
			WHERE T1.ORDER_NO=T2.ORDER_NO)
			>
			(SELECT SUM(T3.QUANTITY)
			FROM TABLE_2 T3
			WHERE T1.ORDER_NO=T3.ORDER_NO);