Query to find adjustment and add to minimum value

user12276240

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.

ravikumar.sv

 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

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

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

 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

user12276240

Thank you so much Ravi

Aketi Jyuuzou

Hahaha I like OLAP usage like below

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?