CODEWITHSUNDEEP
sqlplsql
chinnu
chinnu

Reputation: 5

How to divide the number for below different ranges

Given Number: 2111.03

T1 data:

Name No
AAA 2111.03
BBB 1226.79
CCC 965

T2 data:

Name Start Range End Range
AAA 0 100
AAA 100 130
AAA 130 155
AAA 155 99999
BBB 0 100
BBB 100 120
BBB 120 120
CCC 0 100
CCC 100 110
CCC 110 150
CCC 150 200

Expected Output: Number should go though each range and subtract by the max range, need to show the remaining number at the end.

Name No
AAA 100
AAA 30
AAA 55
AAA 1926.03
BBB 100
BBB 20
BBB 0
BBB 1106.79
CCC 100
CCC 10
CCC 40
CCC 50
CCC 765

Please help me to write a query in SQL, PLSQL.

There are different names and ranges, but I want to write a query to satisfy all the ranges. Please help me write the query in SQL or PL/SQL

Upvotes: 0

Views: 63

Answers (1)

d r
d r

Reputation: 7786

There are some issues regarding your expected result. The logic is not consistent for all names. Max range for name AAA is either 155 or 99999 but you put it in the result like it was 185... There is no additional row for AAA (I put it the same but it could be added if needed)
Anyway, if I got it right maybe this could help, if not then it could guide you to the solution.
Using analytic functions and case expressions you can get all the rows you need with final calculations in the last row for every name. Part of the solution is placed in joined subquery and the rest is in outer sql. Additional rows were added with Union All.

WITH      --  S a m p l e    D a t a :
    t1 AS
        (  Select 'AAA' "A_NAME", 2111.03 "A_NUMBER" From Dual Union All 
           Select 'BBB' "A_NAME", 1226.79 "A_NUMBER" From Dual Union All 
           Select 'CCC' "A_NAME", 965 "A_NUMBER" From Dual 
        ),
    t2 AS 
        (  Select 'AAA' "A_NAME", 0 "START_RANGE", 100 "END_RANGE" From Dual Union All 
           Select 'AAA' "A_NAME", 100 "START_RANGE", 130 "END_RANGE" From Dual Union All
           Select 'AAA' "A_NAME", 130 "START_RANGE", 155 "END_RANGE" From Dual Union All
           Select 'AAA' "A_NAME", 155 "START_RANGE", 99999 "END_RANGE" From Dual Union All
           --
           Select 'BBB' "A_NAME", 0 "START_RANGE", 100 "END_RANGE" From Dual Union All
           Select 'BBB' "A_NAME", 100 "START_RANGE", 120 "END_RANGE" From Dual Union All
           Select 'BBB' "A_NAME", 120 "START_RANGE", 120 "END_RANGE" From Dual Union All
           --
           Select 'CCC' "A_NAME", 0 "START_RANGE", 100 "END_RANGE" From Dual Union All
           Select 'CCC' "A_NAME", 100 "START_RANGE", 110 "END_RANGE" From Dual Union All
           Select 'CCC' "A_NAME", 110 "START_RANGE", 150 "END_RANGE" From Dual Union All
           Select 'CCC' "A_NAME", 150 "START_RANGE", 200 "END_RANGE" From Dual 
        )

--  M a i n   S Q L :
SELECT  A_NAME, A_NUMBER
FROM    ( SELECT    t1.A_NAME, 
                    Case When t2.END_RANGE != 99999 Then t2.DIFF Else t1.A_NUMBER - Max(t2.START_RANGE) Over(Partition By t1.A_NAME) End "A_NUMBER",
                    t2.RN
          FROM      t1
          INNER JOIN (  Select      ROW_NUMBER() OVER(Partition By A_NAME Order By START_RANGE) "RN",
                                  A_NAME,
                                  START_RANGE,
                                  END_RANGE,
                                  Max(Case When END_RANGE < 99999 Then END_RANGE End) 
                                      Over(Partition By A_NAME Order By START_RANGE 
                                            Rows Between Unbounded Preceding And Current Row) - START_RANGE "DIFF"
                        From          t2
                        Order By      t2.A_NAME, t2.START_RANGE
                      ) t2 ON(t2.A_NAME = t1.A_NAME)
      Union All
          SELECT t1.A_NAME, t1.A_NUMBER - Max(t2.END_RANGE) "A_NUMBER", 999 "RN"
          FROM   t1
          INNER JOIN t2 ON(t2.A_NAME = t1.A_NAME)
          GROUP BY t1.A_NAME, t1.A_NUMBER
          HAVING  Max(t2.END_RANGE) < 99999
        )
ORDER BY A_NAME, RN

/*  R e s u l t :
A_NAME   A_NUMBER
------ ----------
AAA           100
AAA            30
AAA            25
AAA       1956,03
BBB           100
BBB            20
BBB             0
BBB       1106,79
CCC           100
CCC            10
CCC            40
CCC            50
CCC           765   */

Upvotes: 0

Related Questions