0

I am having two tables one for purchase and second for sale. I want to create a query to get pending quantity.

Ex. In Purchase Order File (2 records exsits)

OrderID Party Quality Qty.
1       A     AA      10
2       B     BB      5

In Sale Order file

OrderID Party Quality Qty. PurchaserOrderID
11      A     AA      10   1
12      B     BB      15   2

From Query I want to get following result:

OrderID Party Quality Qty.
2       B     BB      -10

Because the users entered quantity more than purchase. If pending quantity of any purchase order or sale order is 0. I do not want to show those order. I just want to get the pending quantity whether its in + or -. A purchase order can have more than one sale order.

Sorry for my bad english. Hope u help soon.

Thanks in advance

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
user2147147
  • 1
  • It looks like a duplicate of [this question](http://stackoverflow.com/questions/15333757/visual-foxpro-query-for-pending-quantity) which I put an answer in to that.... Looks the same as what you are looking for. – DRapp Mar 11 '13 at 15:41

1 Answers1

0

Try this:

SELECT p.OrderID,  p.Party, p.Quality, (p.qty - o.qty) as qty
FROM ordertable AS o
INNER JOIN potable AS p on p.OrderID = o.purchaseOrderID
Jerry
  • 6,357
  • 8
  • 35
  • 50
  • Thanks Mahmoud and Jerry for helping. Sorry to say but I forgot to mention total of purchase order qty and the total sale order quantity with pending quantity. Sorry again. Please help. – user2147147 Mar 09 '13 at 10:18