[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Lifo SQL Query

Posted on 2012-09-13
4
Medium Priority
?
1,933 Views
Last Modified: 2012-09-13
Hai,

I want to generate the simple LIFO based SQL query for my Purchase table:

      The sample data would be in Purchase table like that:

      Order No.   Date                 Item Code         Quantity       Purchase Price
      1               02-Aug-2012          A                          97000        14.50
      2               03-Aug-2012          B                          12800        99.80
      3               04-Aug-2012          B                          27500        95.80
      4               05-Aug-2012          A                          45000        14.60
      5               05-Aug-2012          A                          55000        13.80
      6               06-Aug-2012          B                          45000        97.00
      7               07-Aug-2012          A                          11400        10.50
      8               08-Aug-2012          B                          11600        95.60
      9               09-Aug-2012          A                          12000        12.60


I want to get the purchase cost of 60000 Lots of Item A using LIFO based method, it means that lastest purchases on the basis of date will be calculated for cost so the aspected result will be:
      
      Purchase cost of 60000 lots of item A will be 799980/-

            Following is the record wise breakup of getting result of 799980/-
            09-Aug-2012      105      12000          14.60      
            07-Aug-2012      105      11400          10.50
            05-Aug-2012      105      36600          13.80


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

      create table purchase (order_number number(5), order_date date,
        item_code varchar2(10), Quantity number(10), Price number(8,2));

      insert into purchase values  ('1','02-Aug-2012','A',97000,14.50);
      insert into purchase values  ('2','03-Aug-2012','B',12800,99.80);
      insert into purchase values ('3','04-Aug-2012','B',27500,95.80);
      insert into purchase values ('4','05-Aug-2012','A',45000,14.60);
      insert into purchase values ('5','05-Aug-2012','A',55000,13.80);
      insert into purchase values ('6','06-Aug-2012','B',45000,97.00);
      insert into purchase values ('7','07-Aug-2012','A',11400,10.50);
      insert into purchase values ('8','08-Aug-2012','B',11600,95.60);
      insert into purchase values ('9','09-Aug-2012','A',12000,12.60);

Looking forward for prompt and positive feedback in this regard.

Thank you...
0
Comment
Question by:Sudees
  • 2
4 Comments
 
LVL 5

Expert Comment

by:elevationkevin
ID: 38393853
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 2000 total points
ID: 38393924
Your expected result is wrong,
>> 09-Aug-2012      105      12000          14.60    

for 09-Aug the last price is 12.6, and hence the output should be 775980

See the below query:

SQL> select * from purchase;

ORDER_NUMBER ORDER_DAT ITEM_CODE    QUANTITY      PRICE
------------ --------- ---------- ---------- ----------
           1 02-AUG-12 A               97000       14.5
           2 03-AUG-12 B               12800       99.8
           3 04-AUG-12 B               27500       95.8
           4 05-AUG-12 A               45000       14.6
           5 05-AUG-12 A               55000       13.8
           6 06-AUG-12 B               45000         97
           7 07-AUG-12 A               11400       10.5
           8 08-AUG-12 B               11600       95.6
           9 09-AUG-12 A               12000       12.6

9 rows selected.

SQL>
SQL> with x as (
  2  select 60000 req_qty from dual
  3  )
  4  select quantity, price, sm,
  5  ( case when sm - req_qty > 0 then req_qty - (sm - quantity) else quantity end ) * price amt
  6  from (
  7      select p.*, sum(quantity) over(order by order_date desc, order_number desc) sm
  8      from purchase p
  9      where item_code = 'A'
 10      order by order_date desc, order_number desc
 11  ), x
 12  where ( sm - req_qty) < x.req_qty;

  QUANTITY      PRICE         SM        AMT
---------- ---------- ---------- ----------
     12000       12.6      12000     151200
     11400       10.5      23400     119700
     55000       13.8      78400     505080

SQL>
SQL> with x as (
  2  select 60000 req_qty from dual
  3  )
  4  select sum(amt)
  5  from (
  6      select quantity, price, sm,
  7      ( case when sm - req_qty > 0 then req_qty - (sm - quantity) else quantity end ) * price amt
  8      from (
  9          select p.*, sum(quantity) over(order by order_date desc, order_number desc) sm
 10          from purchase p
 11          where item_code = 'A'
 12          order by order_date desc, order_number desc
 13      ), x
 14      where ( sm - req_qty) < x.req_qty
 15  );

  SUM(AMT)
----------
    775980

Open in new window

0
 

Author Closing Comment

by:Sudees
ID: 38394016
Thanks Sir for your quick and proper response
0
 
LVL 27

Expert Comment

by:sujith80
ID: 38394056
Great! Glad to know it helps
Let me know if you need help in understanding the query.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question