Sale and Purchase Matching

1

I want to generate the simple Sale and Purchase Matching SQL query:

  The sample data would be in INVENTORY table like that:

  Order No.   Date                 Item Code         Quantity      Purchase Price   Sale/Purchase
  1               01-Aug-2012          A                        17000        14.50             Purchase
  2               02-Aug-2012          A                        97000        12.50             Purchase
  3               03-Aug-2012          B                        12800        99.80             Purchase
  4               04-Aug-2012          B                        27500        95.80             Purchase
  5               05-Aug-2012          A                        35000        14.60             Sale
  6               05-Aug-2012          A                        55000        13.80             Purchase
  7               06-Aug-2012          A                        15000        12.00             Sale
  8               07-Aug-2012          A                        11400        10.50             Sale
  9               08-Aug-2012          A                        21400          9.50             Sale

I want to match the sale inventory against purchase inventory on the basis of FIFO method for the item code A therefore the output will be like this:

  PurchaseDate        PurchaseQty    PurchaseRate   SaleDate     SaleQty   SaleRate
  1-Aug-2012            17000              14.50               5-Aug-2012 17000     14.60    
  2-Aug-2012            18000              12.50               5-Aug-2012 18000     14.60
  2-Aug-2012            15000              12.50               6-Aug-2012 15000     12.00
  2-Aug-2012            11400              12.50               7-Aug-2012 11400     10.50

Following is the SQL script for generating Inventory table and Inserting data in it:

  create table inventory (order_number INT, order_date DATETIME, 
    item_code varchar(10), Quantity FLOAT, Price FLOAT, sp_flag char(1));

   insert into inventory values (1,'01-Aug-2012','A',17000,14.50, 'P');
   insert into inventory values (2,'02-Aug-2012','A',97000,12.50,  'P');
   insert into inventory values (3, '03-Aug-2012', 'B',  12800,99.80,  'P');
   insert into inventory values (4, '04-Aug-2012', 'B',  27500,95.80,  'P');
   insert into inventory values (5, '05-Aug-2012',  'A',  35000,14.60,  'S');
   insert into inventory values (6, '05-Aug-2012',  'A',  55000,13.80,  'P');
   insert into inventory values (7, '06-Aug-2012',  'A',  15000,12.00,  'S');
   insert into inventory values (8, '07-Aug-2012',  'A',  11400,10.50,  'S');
   insert into inventory values (9, '08-Aug-2012',  'A',  21400,  9.50,  'S');
2

Help?

3

If you sketch out the process in pseudo-code, I think you can implement it using a cursor. Not sure about a simple query though. Perhaps, just perhaps, you could do it with a recursive CTE, though I think you might hit the recursion limit. Unfortunately, the maximum number of recursions allowed for a recursive CTE is only 32,767.

4

You could create a trigger to update a balance.

EDIT: This is an incredibly important AND difficult problem that no one to my knowledge has ever solved well. I worked on the Brazilian government's purchasing system (did all the reporting) in 1997. Tracking orders and shipping receipts was not trivial.

I like the event-based records. You need some ancillary tables to show status. Data is cheap. Create more tables as necessary.

5

The other option might be to get rid of all closed balances and have a function return the current balance. That function would be slow on all records, but more than fast enough if only calculating current.

How to get rid of all closed balances? SQL statement to eliminate all priors OR you could write a new event to the table (add a 'B' for Balance type to the Purchase/Sale column).

EDIT: If you add a Balance event, don't do an average cost. Only write the Balance event if the LIFO/FIFO zeroes out a price level.

EDIT 2: I haven't thought too much about this, but you could do 2 types: L for LIFO and F for FIFO, and write 2 events. Edit 3: Never mind, leave it as 'B'alance in the Purchase/Sale column, but don't average.