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...
SudeesSoftware DeveloperAsked:
Who is Participating?
 
SudeesConnect With a Mentor Software DeveloperAuthor Commented:
Hello,

I have found the answer of my query from other Forum so the same is posted here for  reference of the Members and Experts of this Forum...

This query will give  the desired result:

      WITH purchase AS
      (
       SELECT item_code,
        order_date,
         price,
        quantity,
        SUM (quantity) OVER ( PARTITION BY  item_code ORDER BY order_date,                        
          order_number) AS purchase_total,
        order_number
       FROM    inventory
       where sp_flag = 'P'
       and item_code = 'A'
      )
      , SALE AS
      (
       SELECT item_code,
        order_date,
         price,
        quantity,
        SUM (quantity) OVER ( PARTITION BY  item_code ORDER BY order_date,
          order_number) AS sale_total,
        order_number
       FROM    inventory
       where sp_flag = 'S'
       and item_code = 'A'
      )
      SELECT   s.item_code
      ,   p.order_date
      ,   LEAST ( purchase_total  + s.quantity - sale_total
          , sale_total + p.quantity  - purchase_total
        , s.quantity, p.quantity
        )  AS purchase_quantity
      ,   p.price
      ,   s.order_date
      ,   LEAST ( purchase_total  + s.quantity - sale_total
          , sale_total + p.quantity  - purchase_total
        , s.quantity, p.quantity
        )  AS sale_quantity
      ,   s.price
      FROM   sale s
      JOIN   purchase p   ON s.item_code = p.item_code
         AND s.sale_total  > p.purchase_total  - p.quantity
         AND p.purchase_total   > s.sale_total - s.quantity
      ORDER BY
        s.item_code
      ,   s.order_date
      ,   s.order_number
      ,   p.order_date
      ,   p.order_number


Regards,
0
 
Swadhin RaySenior Technical Engineer Commented:
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

0
 
Swadhin RaySenior Technical Engineer Commented:
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

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
SudeesSoftware DeveloperAuthor Commented:
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,
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
I am happy if you found a solution for yourself
0
 
SudeesSoftware DeveloperAuthor Commented:
I have found the  answer on Other  Forum
0
All Courses

From novice to tech pro — start learning today.