asked on

Sale and Purchase Matching SQL Query

Hai,

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 number(5), order_date date,
item_code varchar2(10), Quantity number(10), Price number(8,2), 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');

Looking forward for prompt and positive feedback in this regard.

Thank you...

Swadhin Ray

Check this :

SELECT 
CASE WHEN SP_FLAG ='P'
THEN ORDER_DATE  END PURCHASEDATE,
case when SP_FLAG ='P'
THEN QUANTITY END AS PURCHASEQTY,
CASE WHEN SP_FLAG ='P'
THEN PRICE END AS PURCHASERATE,
CASE WHEN SP_FLAG ='S'
THEN ORDER_DATE  END SALEDATE,
CASE WHEN SP_FLAG ='S'
THEN QUANTITY END AS SaleQty,
CASE WHEN SP_FLAG ='S'
THEN PRICE END AS SaleRate
 FROM  INVENTORY WHERE ITEM_CODE='A'
 ORDER BY PURCHASEDATE,SALEDATE ;

Open in new window

Swadhin Ray

Check in decode too:

SELECT DECODE(SP_FLAG ,'P',ORDER_DATE) PURCHASEDATE,
  DECODE(SP_FLAG ,'P',QUANTITY) PURCHASEQTY,
  DECODE(SP_FLAG ,'P',PRICE) PURCHASERATE,
  DECODE(SP_FLAG ,'S',ORDER_DATE) SALEDATE,
  DECODE(SP_FLAG ,'S',QUANTITY) SaleQty,
  DECODE(SP_FLAG ,'S',PRICE) SaleRate
FROM INVENTORY
WHERE ITEM_CODE='A'
ORDER BY PURCHASEDATE,
  SALEDATE ;

Open in new window

Sudees

ASKER

Hai Slobaray,

Your both queries are giving following result, which is differ from my required result because in my required result Purchase quantity is breaking when sale quantity is lower then purchase quantity and Sale Quantity is breaking when purchase quantity is lower then Sale quantity so to make proper difference of every transaction.

PURDATE PURQTY PURRATE SALEDATE SALEQTY SALERATE
01-AUG-12 17000 14.5
02-AUG-12 97000 12.5
05-AUG-12 55000 13.8
05-AUG-12 35000 14.6
06-AUG-12 15000 12
07-AUG-12 11400 10.5
08-AUG-12 21400 9.5

Thanks,

Naveen Kumar

I had setup the table in my test database and you have 9 records in the table. we have 7 records for item code A and what is the output you need only for this item code 'A' for me to understand ( leave out the B item code for now ).

I do not understand how you get 4 records as output from those 9 records ? can you please explain and i will come up with a query to help you.

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

Assuming you only need 4 records in the output, i can understand the first record in the output but not the second and the others.

For example, for the second row - how did purchase qty come as 18000

ASKER CERTIFIED SOLUTION

Sudees

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naveen Kumar

I am happy if you found a solution for yourself

Sudees

ASKER

I have found the answer on Other Forum