Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query to find adjustment and add to minimum value

user12276240Nov 26 2009 — edited Dec 8 2009
Hi i need a query to perform the below operation
I have a table as shown below

invoice rec_no r_cost d_cost chg tax code
1 12345 114.35 100.30 1.00 1.00 234
2 12345 114.35 2.00 1.00 1.00 201
3 12345 114.35 14.00 1.00 1.00 600
4 23412 120.90 110.40 1.00 1.05 700
5 23412 120.90 12.50 1.00 1.05 900


From this table i need to find the adjustment for each rec_no as
(r_cost + chg +tax) -sum(d_cost)for rec_no=12345
(114.35 + 1.00+1.00) -(100.30+2.00+14.00)=0.05
This 0.05 should be added to the minimum(code) for the rec_no 12345.
so the d_cost of code, 201 with rec_no=12345 should be (2.00 + 0.05=2.05).

and,
(120.90 + 1.00+1.05) -(110.40+12.50)=0.05
This 0.05 should be added to the minimum(code) for the rec_no 23412.
so the d_cost of code, 700 with rec_no=23412 should be (110.40+ 0.05=110.45).

Kindly help me in writing a oracle query to perform the above operation.
This post has been answered by ravikumar.sv on Dec 1 2009
Jump to Answer

Comments

ravikumar.sv
check this out....may be you require this 
with tab as (
select 1 invoice,12345 rec_no,114.35 r_cost,100.30 d_cost,1.00 chg,1.00 tax,234 code from dual union all
select 2 invoice,12345 rec_no,114.35 r_cost,2.00 d_cost,1.00 chg,1.00 tax,201 code from dual union all
select 3 invoice,12345 rec_no,114.35 r_cost,14.00 d_cost,1.00 chg,1.00 tax,600 code from dual union all
select 4 invoice,23412 rec_no,120.90 r_cost,110.40 d_cost,1.00 chg,1.05 tax,700 code from dual union all
select 5 invoice,23412 rec_no,120.90 r_cost,12.50 d_cost,1.00 chg,1.05 tax,900 code from dual 
)
 SELECT tab.*,
  r_cost + chg +tax-SUM(d_cost) over (partition BY rec_no)+MIN(d_cost) keep (DENSE_RANK FIRST
ORDER BY code) over (partition BY rec_no) adj
   FROM tab
Ravi Kumar

Just formatted for better understanding

Edited by: ravikumar.sv on Nov 26, 2009 12:58 PM
1 person found this helpful
user12276240
Hi Ravi,
Thank you so much! Your query was really useful!
I want to make the result to be printed in a little different way.
The calculated adjustment amount should be added to the comm_cost whose comm_code is minimum only.
From the below query i get this result,
invoice rec_no r_cost d_cost chg tax code Adj
1 12345 114.35 100.30 1.00 1.00 234 2.05
2 12345 114.35 2.00 1.00 1.00 201 2.05
3 12345 114.35 14.00 1.00 1.00 600 2.05
4 23412 120.90 110.40 1.00 1.05 700 110.45
5 23412 120.90 12.50 1.00 1.05 900 110.45

Instead i want a result like this,
invoice rec_no r_cost d_cost chg tax code Adj
1 12345 114.35 100.30 1.00 1.00 234 100.30
2 12345 114.35 2.00 1.00 1.00 201 2.05
3 12345 114.35 14.00 1.00 1.00 600 14.00
4 23412 120.90 110.40 1.00 1.05 700 110.45
5 23412 120.90 12.50 1.00 1.05 900 12.50

Can this be done with oracle query? kindly guide me.
ravikumar.sv
Answer 
 with tab as (
    select 1 invoice,12345 rec_no,114.35 r_cost,100.30 d_cost,1.00 chg,1.00 tax,234 code from dual union all
    select 2 invoice,12345 rec_no,114.35 r_cost,2.00 d_cost,1.00 chg,1.00 tax,201 code from dual union all
    select 3 invoice,12345 rec_no,114.35 r_cost,14.00 d_cost,1.00 chg,1.00 tax,600 code from dual union all
    select 4 invoice,23412 rec_no,120.90 r_cost,110.40 d_cost,1.00 chg,1.05 tax,700 code from dual union all
    select 5 invoice,23412 rec_no,120.90 r_cost,12.50 d_cost,1.00 chg,1.05 tax,900 code from dual 
)
 SELECT tab.*,
  CASE
    WHEN DENSE_RANK() OVER (partition BY rec_no ORDER BY code)=1
    THEN r_cost + chg +tax-SUM(d_cost) over (partition BY rec_no)+MIN(d_cost) keep (DENSE_RANK FIRST
   ORDER BY code) over (partition BY rec_no)
    ELSE d_cost
  END adj
   FROM tab
ORDER BY invoice
check this out...

Ravi Kumar
Marked as Answer by user12276240 · Sep 27 2020
user12276240
Thank you so much Ravi :)
Aketi Jyuuzou
Hahaha I like OLAP usage like below :D
with tab as (
select 1 invoice,12345 rec_no,114.35 r_cost,100.30 d_cost,1.00 chg,1.00 tax,234 code from dual union all
select 2 invoice,12345 rec_no,114.35 r_cost,2.00 d_cost,1.00 chg,1.00 tax,201 code from dual union all
select 3 invoice,12345 rec_no,114.35 r_cost,14.00 d_cost,1.00 chg,1.00 tax,600 code from dual union all
select 4 invoice,23412 rec_no,120.90 r_cost,110.40 d_cost,1.00 chg,1.05 tax,700 code from dual union all
select 5 invoice,23412 rec_no,120.90 r_cost,12.50 d_cost,1.00 chg,1.05 tax,900 code from dual)
SELECT tab.*,
CASE
WHEN code = min(code) OVER (partition BY rec_no)
--DENSE_RANK() OVER (partition BY rec_no ORDER BY code)=1
THEN r_cost + chg +tax
    -SUM(d_cost) over(partition BY rec_no
                      order by code rows between 1 following
                                             and unbounded following)
ELSE d_cost END adj
FROM tab
ORDER BY invoice;

INVOICE  REC_NO  R_COST  D_COST  CHG   TAX  CODE     ADJ
-------  ------  ------  ------  ---  ----  ----  ------
      1   12345  114.35   100.3    1     1   234   100.3
      2   12345  114.35       2    1     1   201    2.05
      3   12345  114.35      14    1     1   600      14
      4   23412   120.9   110.4    1  1.05   700  110.45
      5   23412   120.9    12.5    1  1.05   900    12.5
DB2 allows order by code rows 1 following.
It means order by code rows between 1 following and unbounded following.
Does Oracle11gR2 allow above usage?
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 5 2010
Added on Nov 26 2009
5 comments
1,038 views