Forum Stats

  • 3,836,844 Users
  • 2,262,198 Discussions
  • 7,900,128 Comments

Discussions

Categories

Query to find adjustment and add to minimum value

user12276240
user12276240 Member Posts: 23
edited Dec 8, 2009 5:40AM in SQL & PL/SQL
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.
· Share on TwitterShare on Facebook

Best Answer

  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072
    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
    · Share on TwitterShare on Facebook

Answers

  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072
    edited Nov 26, 2009 2:29AM
    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
    ravikumar.sv
    · Share on TwitterShare on Facebook
  • user12276240
    user12276240 Member Posts: 23
    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.
    · Share on TwitterShare on Facebook
  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072
    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
    · Share on TwitterShare on Facebook
  • user12276240
    user12276240 Member Posts: 23
    Thank you so much Ravi :)
    · Share on TwitterShare on Facebook
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Dec 8, 2009 5:40AM
    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?
    · Share on TwitterShare on Facebook
This discussion has been closed.