CODEWITHSUNDEEP
visual-foxprofoxpro
odlan yer
odlan yer

Reputation: 771

Script to compare and sum two foxpro table

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?

Upvotes: 1

Views: 675

Answers (3)

nikos
nikos

Reputation: 11

Simply: 

SELECT a.ItemCode, a.BatchNumber, a.Qty
       b.QtyUsed, a.Qty - b.QtyUsed as Balance
  FROM ItemTable a
  LEFT JOIN TransactionTable b ON a.ItemCode = b.ItemCode 
                          and a.BatchNumber = b.BatchNumber

Sure if ItemCode and BatchNumber are distinct

Upvotes: 0

ShawnOrr
ShawnOrr

Reputation: 1329

Here is another way. Combine both tables using a UNION statement. This way you have all of your records in one cursor/table. In the second statement you can do your GROUP BY to get the totals for Qty, QtyUsed and Balance fields.

Sometimes splitting the work into smaller select statements can make the problem easier to figure out.

SELECT i.ItemCode, i.BatchNumber, i.Qty, 0000 as QtyUsed ;
    FROM Item i ;
UNION ;
SELECT t.ItemCode, t.BatchNumber, 0000 as Qty, t.QtyUsed ;
    FROM Transaction t ;
    INTO CURSOR one

SELECT ItemCode, BatchNumber, SUM(Qty) as Qty, SUM(QtyUsed) as QtyUsed, (SUM(Qty) - SUM(QtyUsed)) as Balance ;
    FROM one ;
    INTO CURSOR two ;
    GROUP BY ItemCode, BatchNumber

Upvotes: 1

DRapp
DRapp

Reputation: 48139

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

Upvotes: 3

Related Questions