?
Solved

sql query stock aging

Posted on 2011-10-19
4
Medium Priority
?
1,576 Views
Last Modified: 2012-08-13
Dear Experts
I have a stock receipt table1  (stockid,date,qty,rate) , i wish to get a query
(stockid, balance_qty, age30,age60,age90, age>90) on a particular day. Please try to help rgds Benny
0
Comment
Question by:bmkdubai
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36994870
you will need to clarify a bit (best with data samples) what exactly you are trying to achieve, and what you input parameters are ...
not 100% clear so far ...
0
 

Author Comment

by:bmkdubai
ID: 36994934
i have a stock receipts table which contain.
stockid, date, qty, rate
abc, 01/06/2011, 10, 35.25
bcd, 15/06/2011, 15,32
xyz, 01/7/2011, 25,36
qpr, 30/08/2011, 50, 40
qxy, 08/09/2011, 100 ,42

i wish to get a query on a particular day

stockid, total_stock, age0-30, age-31-60,age-61-90,age>90
abc, 10,0,0,0,10
bcd,15,0,0,0,15
qxy,100,0,100,0,0

means how old is the stock

xyz
qpr
qxy
0
 
LVL 3

Expert Comment

by:ajcheung78
ID: 36996141
I would do something like the following:

select stockid, sum(qty) total_stock
  , coalesce(sum("0-30"),0) "0-30"
  , coalesce(sum("31-60"),0) "31-60"
  , coalesce(sum("61-90"),0) "61-90"
  , coalesce(sum(">90"),0) ">90"
from
(
  select stockid, qty
    , case when days between 0 and 30 then qty end "0-30"
    , case when days between 31 and 60 then qty end "31-60"
    , case when days between 61 and 90 then qty end "61-90"
    , case when days > 90 then qty end ">90"
  from
  (
    select sr.stockid, sr.qty, (to_date(:baseDate) - sr.sdate) days
    from stockreceipts sr
  )
)
group by stockid
order by stockid
;
0
 
LVL 3

Accepted Solution

by:
ajcheung78 earned 2000 total points
ID: 36996163
When prompted enter a date in a format such as '19-OCT-11".  If you want to use a different date format then you can add the appropriate oracle date constants to the to_date function.  

Using some data you provided you should get something like the following with the current date and the snippet attached:

STOCKID      TOTAL_STOCK      0-30      31-60      61-90      >90
abc      10      0      0      0      10
bcd      15      0      0      0      15
qpr      50      0      50      0      0
qxy      101      0      101      0      0
xyz      25      0      0      0      25
select stockid, sum(qty) total_stock
  , coalesce(sum("0-30"),0) "0-30"
  , coalesce(sum("31-60"),0) "31-60"
  , coalesce(sum("61-90"),0) "61-90"
  , coalesce(sum(">90"),0) ">90"
from
(
  select stockid, qty
    , case when days between 0 and 30 then qty end "0-30"
    , case when days between 31 and 60 then qty end "31-60"
    , case when days between 61 and 90 then qty end "61-90"
    , case when days > 90 then qty end ">90"
  from
  (
    select sr.stockid, sr.qty, (to_date(:baseDate) - sr.sdate) days
    from
    (
      select 'abc' stockid, to_date('01/06/2011', 'dd/mm/yyyy') sdate, 10 qty, 35.25 rate from dual union
      select 'bcd', to_date('15/06/2011', 'dd/mm/yyyy'), 15,32 from dual union
      select 'xyz', to_date('01/7/2011', 'dd/mm/yyyy'), 25,36 from dual union
      select 'qpr', to_date('30/08/2011', 'dd/mm/yyyy'), 50, 40 from dual union
      select 'qxy', to_date('08/09/2011', 'dd/mm/yyyy'), 100 ,42 from dual union
      select 'qxy', to_date('08/09/2011', 'dd/mm/yyyy'), 1 ,42 from dual
    ) sr
  )
)
group by stockid
order by stockid
;

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month17 days, 11 hours left to enroll

831 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