0

I am having two tables AORDER for Purchase & BORDER for sale. I want to get pending quantity. Sale orders can have more than 1 records against one purchase order. I do not want to show those order having pending quantities 0. I tried this:

SELECT ;
      aorder.orderid,;
      aorder.orderdate,;
      aorder.itemname,;
      aorder.partyname,;
      aorder.qty as Purchase,;
      SUM(border.qty) AS Sale,;
      SUM(aorder.qty-border.qty) as Pending;
   FROM ;
      aorder;
         LEFT JOIN border ;
            ON aorder.orderid = border.porderid;
   GROUP BY ;
      aorder.orderid,;
      aorder.orderdate,;
      aorder.itemname,;
      aorder.partyname,;
      aorder.qty

But I am failed to hide those records having purchase qty = sale qty.

Thnx in advance.

DRapp
  • 47,638
  • 12
  • 72
  • 142
Gaurav Gupta
  • 1
  • 1
  • 1

1 Answers1

0

As Shahkalpesh mentioned, you do need to apply the having, but your SUM in incorrect.

It should be

aorder.qty - SUM(border.qty) > 0; && also for your field reference.

The reason, SUM is summing each part WITHIN the sum. You will have only one "Purchase" record, but many "Sale" records, as if inventory control First in / First Out (FIFO), Last In / First Out (LIFO), etc

So, say you have PURCHASE order #1 with a quantity of 10, and have sold separate times for quantities 2, 1, 1, 3, 2, 1... Total of 6 sale records. What you are doing is

sum( 10 - 2 
   + 10 - 1
   + 10 - 1
   + 10 - 3
   + 10 - 2
   + 10 - 1 )

The revised way is...

10 - SUM( 2 + 1 + 1 + 3 + 2 + 1 )
DRapp
  • 47,638
  • 12
  • 72
  • 142
  • Thanks for reply. DRapp I got the pending quantity in right way. But a purchase order having no sale order is not in the list. How to resolve it. I tried where and having clauses. But not working. In my query sale can be greater than the purchase. I mean sale can be negative also. – – Gaurav Gupta Mar 13 '13 at 10:27