Script to compare and sum two foxpro table

Question

I Have 2 tables

Item Table

ItemCode     BatchNumber   Qty
 Item01      Batch1         50
 Item02      Batch2         75

Transaction Table

ItemCode     BatchNumber   QtyUsed
 Item01      Batch1          4
 Item02      Batch2          7 

Desired Output

ItemCode     BatchNumber   Qty    QtyUsed   Balance
 Item01      Batch1         50       4       46
 Item02      Batch2         75       7       68

What sql script to achieve this output?

Answer 1

This should get it for you. The trick is to pre-query each set so at most you have one record per item / batch combination. If not, and you have multiple in either table, you will get a Cartesian result. Starting with the item table, these are guaranteed to have a record, while the transaction table may never have a sale of said item. This way the item pre-summarized query is done first, then compared to the sum of all transactions having at most a 1:1 ratio of record. But if no corresponding transactions for an item / batch, the NVL() will return a zero value, but I set to 000000 so if the first record has a null value you don't get a single digit column width answer by only doing null with a single 0.

select;
      TmpItem.ItemCode,;
      TmpItem.BatchNumber,;
      TmpItem.SumQ as Qty,;
      cast( NVL( TmpTrans.SumUsed, 0 ) as int) as QtyUsed,;
      TmpItem.SumQ - NVL( TmpTrans.SumUsed, 0 ) as Balance;
   from ;
      ( select ItemCode, BatchNumber, SUM(Qty) as SumQ;
           FROM ItemTable;
           GROUP BY 1, 2 ) TmpItem;
           LEFT JOIN;
           ( select ItemCode, BatchNumber, SUM(QtyUsed) as SumUsed;
                FROM TransTable;
                GROUP BY 1, 2 ) TmpTrans;
           ON TmpItem.ItemCode = TmpTrans.ItemCode;
           AND TmpItem.BatchNumber = TmpTrans.BatchNumber