Solved

# sql query stock aging

Posted on 2011-10-19
Medium Priority
1,576 Views
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
Question by:bmkdubai
• 2

LVL 143

Expert Comment

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

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

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

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
;
``````
0

## Featured Post

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.