1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

To compare values based on range

Discussion in 'SQL PL/SQL' started by rohit_shinez, Oct 24, 2015.

  1. rohit_shinez

    rohit_shinez Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi,



    I am having below tables

    T1


    ID Balance
    1 500
    2 50
    3 1000

    T2

    Min Max Int
    0 100 2
    101 500 1
    501 1000 3

    Output required

    ID R1 R2 R3 R1_int R2_int R3_int
    1 0 500 0 0 1 0
    2 50 0 0 2 0 0
    3 0 0 1000 0 0 3

    i need to check balance falling in which range and take the respective R1_int

    for Eg. 500 falls between second range (R2)i.e 101-500 and take the int value which is 1 and place the value as R2_int(second range int value) with rest of R1_int and R3_int as zero

    eg for first record
    ID R1 R2 R3 R1_int R2_int R3_int
    1 0 500 0 0 1 0

    Code (SQL):
    CREATE TABLE T1(id NUMBER,balance NUMBER);
    CREATE TABLE T2(MIN NUMBER,MAX NUMBER,INT NUMBER);
    INSERT INTO T1
    SELECT 1,500 FROM dual
    UNION ALL
    SELECT 2,50 FROM dual
    UNION ALL
    SELECT 3,1000 FROM dual;
    INSERT INTO T2
    SELECT 0,100,2 FROM dual
    UNION ALL
    SELECT 101,500,1 FROM dual
    UNION ALL
    SELECT 501,1000,3 FROM dual;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    What is your Oracle vresion ?

    Formulate accurately a task.
    The name of columns doesn't correspond to the description in your tables
     
    Last edited: Oct 24, 2015
  3. rohit_shinez

    rohit_shinez Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi Its Oracle 11g

    I think the table name and column are fine as the output columns are derived column names

    ID R1 R2 R3 R1_int R2_int R3_int
    1 0 500 0 0 1 0
    2 50 0 0 2 0 0
    3 0 0 1000 0 0 3
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    for a start...helpfull links

    Database SQL Language Reference
    Analytic Functions
    PIVOT and UNPIVOT



    for example

    Code (SQL):
    WITH
        balance (id,balans) AS     (
            SELECT 1 ,500 FROM dual UNION ALL
            SELECT 2 ,50 FROM dual UNION ALL
            SELECT 3 ,1000 FROM dual
        )
        ,min_max (rn,MIN,MAX,INT) AS
        (
            SELECT 1,0,100, 2 FROM dual UNION ALL
            SELECT 2,101, 500, 1 FROM dual UNION ALL
            SELECT 3,501 ,1000 ,3 FROM dual

        )
        ,results (id,rn,r,val) AS
        (
            SELECT
                b.id,
                mm.rn,
                CASE WHEN b.balans BETWEEN mm.MIN AND mm.MAX THEN b.balans ELSE 0 END,
                CASE WHEN b.balans BETWEEN mm.MIN AND mm.MAX THEN mm.INT ELSE 0 END
            FROM
               balance b
               CROSS JOIN min_max mm

       
        )
    SELECT
        *
    FROM
        results          
        pivot

        (
            MAX(r) r,
            MAX(val) r_int

            FOR rn IN (1,2,3)
        );

    SQL>
      ID  1_R  1_R_INT  2_R  2_R_INT  3_R  3_R_INT
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
      1  0  0  500  1  0  0
      2  50  2  0  0  0  0
      3  0  0  0  0  1000  3




     


    or

    Code (SQL):
    WITH
        balance (id,balans) AS     (
            SELECT 1 ,500 FROM dual UNION ALL
            SELECT 2 ,50 FROM dual UNION ALL
            SELECT 3 ,1000 FROM dual
        )
        ,min_max (rn,MIN,MAX,INT) AS
        (
            SELECT 1,0,100, 2 FROM dual UNION ALL
            SELECT 2,101, 500, 1 FROM dual UNION ALL
            SELECT 3,501 ,1000 ,3 FROM dual

        )
        ,results (id,rn,r,val) AS
        (
            SELECT
                b.id,    
                mm.rn,
                CASE WHEN b.balans BETWEEN mm.MIN AND mm.MAX THEN b.balans ELSE 0 END,
                CASE WHEN b.balans BETWEEN mm.MIN AND mm.MAX THEN mm.INT ELSE 0 END
            FROM
               balance b
               CROSS JOIN min_max mm
     
           
        )
    SELECT
        rs.id,
        MAX(CASE WHEN rs.rn = 1 THEN rs.r ELSE 0 END ) r1,
        MAX(CASE WHEN rs.rn = 2 THEN rs.r ELSE 0 END ) r2,
        MAX(CASE WHEN rs.rn = 3 THEN rs.r ELSE 0 END ) r3,
        MAX(CASE WHEN rs.rn = 1 THEN rs.val ELSE 0 END ) r1_int,
        MAX(CASE WHEN rs.rn = 2 THEN rs.val ELSE 0 END ) r2_int,
        MAX(CASE WHEN rs.rn = 3 THEN rs.val ELSE 0 END ) r3_int
     
    FROM
        results rs
    GROUP BY
        rs.id;

    SQL>
            ID         R1         R2         R3     R1_INT     R2_INT     R3_INT
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
             1          0        500          0          0          1          0
             2         50          0          0          2          0          0
             3          0          0       1000          0          0          3
     
     
    Last edited: Oct 25, 2015
  5. rohit_shinez

    rohit_shinez Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi is it possible to do if my data in T2 is something like this

    Min Max Int
    0 100 2.5
    101 500 1.5
    501 1000 3.4

    Insert into T2

    select 0,100,2.5 from dual

    union all

    select 101,500,1.5 from dual

    union all

    select 501,1000,3.4 from dual;

    output required

    ID R1 R2 R3 R1_int R2_int R3_int
    1 0 500 0 0 1.5 0
    2 50 0 0 2.5 0 0
    3 0 0 1000 0 0 3.4
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    You changed data on the table T2.
    I think you can adapt the examples of queries for your task.
    it is very simple.