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 replace value based on date condition

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

  1. rohit_shinez

    rohit_shinez Active Member

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

    I am having below tables
    Balance




    ID BAL_DATE BAL LIMIT_AMOUT
    1234 01-Jan-08 -195.34 -5000
    1234 02-Jan-08 -6000 -5000
    1234 03-Jan-08 -209.84 -25
    1234 04-Jan-08 -54.96 -25
    1234 14-Oct-09 -195.34 -25
    1234 16-Oct-09 -209.84 -25
    1234 15-Jun-14 -195.34 -5000
    1234 16-Jun-14 -2000 -5000
    1234 19-Jun-14 -5500 -25


    bucket


    ID CODE START_DATE END_DATE PRODUCT BUK_ID
    879 1490 16-Nov-07 09-Oct-09 2300 1
    879 3333 20-Nov-07 08-Oct-08 2300 4
    879 3334 09-Oct-08 08-Oct-09 2300 4
    879 1490 10-Oct-09 19-Nov-10 2 2
    879 1490 10-Oct-09 19-Nov-10 2 2
    879 1490 16-Jun-14 20-Aug-14 2300 3


    ref_table_1



    PRODUCT EFF_FROM_DATE EFF_TO_DATE TYPE MIN_AMT MAX_AMT RATE CHARGE
    2300 10-Jun-02 01-Jun-08 T1 0 0 0
    2300 10-Jun-02 01-Jun-08 T2 1 5000 14.628
    2300 10-Jun-02 01-Jun-08 T4 24.582
    2300 08-Jun-09 12-Jan-11 T1 0 0 0
    2300 08-Jun-09 12-Jan-11 T2 1 5000 17.82
    2300 08-Jun-09 12-Jan-11 T4 0
    2300 16-Jun-14 31-Dec-99 T1 0 15 0
    2300 16-Jun-14 31-Dec-99 T2 16 1000 0.75
    2300 16-Jun-14 31-Dec-99 T3 1001 2000 1.5
    2300 16-Jun-14 31-Dec-99 T4 2001 5000 3

    I need the output something like this


    ID BAL_DATE T1VAL T2VAL T3VAL T4VAL T1PER T2PER T4PER
    1234 01-Jan-08 200 5000 0 0 0 14.628 24.582
    1234 02-Jan-08 200 5000 0 0 0 14.628 24.582
    1234 03-Jan-08 200 5000 0 0 0 14.628 24.582
    1234 04-Jan-08 200 5000 0 0 0 14.628 24.582
    1234 16-Jun-14 15 1000 2000 5000
    1234 19-Jun-14 15 1000 2000 5000

    Derivation logic:



    01. First to check if bal_date is falling between start and end dates of bucket and take the product and compare with eff_from_date and eff_to_date of ref_table_1

    02. Take the respective max_amt and rate to corresponding dates

    03. While checking the dates from bucket table dont consider code which starts with 3 or not like '3%'

    04.When i see any bal_date falling between start and end dates of bucket for product which starts with 3, then make T1val as 200 instead of considering from ref_table


    Test cases:

    Code (SQL):
    CREATE TABLE "BALANCE"
       ( "ID" NUMBER(10,0) NOT NULL ENABLE,
      "BAL_DATE" DATE NOT NULL ENABLE,
      "BAL" NUMBER(15,2) NOT NULL ENABLE,
      "LIMIT_AMOUT" NUMBER(15,2)
       );
      CREATE TABLE "BUCKET"
       ( "ID" VARCHAR2(10 BYTE),
      "CODE" NUMBER(4,0),
      "START_DATE" DATE,
      "END_DATE" DATE,
      "PRODUCT" VARCHAR2(20 BYTE),
      "BUK_ID" NUMBER
       );
    CREATE TABLE ."REF_TAB_1"
       ( "PRODUCT" NUMBER(4,0),
      "EFF_FROM_DATE" DATE,
      "EFF_TO_DATE" DATE,
      "TYPE" CHAR(2 BYTE),
      "MIN_AMT" NUMBER(10,0),
      "MAX_AMT" NUMBER(10,0),
      "RATE" NUMBER(6,3),
      "CHARGE" NUMBER(5,2)
       );
    INSERT INTO balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) VALUES (1234,to_date('01-JAN-08','DD-MON-RR'),-195.34,-5000);
    INSERT INTO balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) VALUES (1234,to_date('02-JAN-08','DD-MON-RR'),-6000,-5000);
    INSERT INTO balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) VALUES (1234,to_date('03-JAN-08','DD-MON-RR'),-209.84,-25);
    INSERT INTO balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) VALUES (1234,to_date('04-JAN-08','DD-MON-RR'),-54.96,-25);
    INSERT INTO balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) VALUES (1234,to_date('14-OCT-09','DD-MON-RR'),-195.34,-25);
    INSERT INTO balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) VALUES (1234,to_date('16-OCT-09','DD-MON-RR'),-209.84,-25);
    INSERT INTO balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) VALUES (1234,to_date('15-JUN-14','DD-MON-RR'),-195.34,-5000);
    INSERT INTO balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) VALUES (1234,to_date('16-JUN-14','DD-MON-RR'),-2000,-5000);
    INSERT INTO balance (ID,BAL_DATE,BAL,LIMIT_AMOUT) VALUES (1234,to_date('19-JUN-14','DD-MON-RR'),-5500,-25);
    INSERT INTO ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) VALUES (2300,to_date('10-JUN-02','DD-MON-RR'),to_date('01-JUN-08','DD-MON-RR'),'T1',0,0,0,NULL);
    INSERT INTO ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) VALUES (2300,to_date('10-JUN-02','DD-MON-RR'),to_date('01-JUN-08','DD-MON-RR'),'T2',1,5000,14.628,NULL);
    INSERT INTO ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) VALUES (2300,to_date('10-JUN-02','DD-MON-RR'),to_date('01-JUN-08','DD-MON-RR'),'T4',NULL,NULL,24.582,NULL);
    INSERT INTO ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) VALUES (2300,to_date('08-JUN-09','DD-MON-RR'),to_date('12-JAN-11','DD-MON-RR'),'T1',0,0,0,NULL);
    INSERT INTO ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) VALUES (2300,to_date('08-JUN-09','DD-MON-RR'),to_date('12-JAN-11','DD-MON-RR'),'T2',1,5000,17.82,NULL);
    INSERT INTO ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) VALUES (2300,to_date('08-JUN-09','DD-MON-RR'),to_date('12-JAN-11','DD-MON-RR'),'T4',NULL,NULL,0,NULL);
    INSERT INTO ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) VALUES (2300,to_date('16-JUN-14','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),'T1',0,15,NULL,0);
    INSERT INTO ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) VALUES (2300,to_date('16-JUN-14','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),'T2',16,1000,NULL,0.75);
    INSERT INTO ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) VALUES (2300,to_date('16-JUN-14','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),'T3',1001,2000,NULL,1.5);
    INSERT INTO ref_tab_1 (PRODUCT,EFF_FROM_DATE,EFF_TO_DATE,TYPE,MIN_AMT,MAX_AMT,RATE,CHARGE) VALUES (2300,to_date('16-JUN-14','DD-MON-RR'),to_date('31-DEC-99','DD-MON-RR'),'T4',2001,5000,NULL,3);
    INSERT INTO bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) VALUES ('879',1490,to_date('16-NOV-07','DD-MON-RR'),to_date('09-OCT-09','DD-MON-RR'),'2300',1);
    INSERT INTO bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) VALUES ('879',3333,to_date('20-NOV-07','DD-MON-RR'),to_date('08-OCT-08','DD-MON-RR'),'2300',4);
    INSERT INTO bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) VALUES ('879',3334,to_date('09-OCT-08','DD-MON-RR'),to_date('08-OCT-09','DD-MON-RR'),'2300',4);
    INSERT INTO bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) VALUES ('879',1490,to_date('10-OCT-09','DD-MON-RR'),to_date('19-NOV-10','DD-MON-RR'),'2',2);
    INSERT INTO bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) VALUES ('879',1490,to_date('10-OCT-09','DD-MON-RR'),to_date('19-NOV-10','DD-MON-RR'),'2',2);
    INSERT INTO bucket (ID,CODE,START_DATE,END_DATE,PRODUCT,BUK_ID) VALUES ('879',1490,to_date('16-JUN-14','DD-MON-RR'),to_date('20-AUG-14','DD-MON-RR'),'2300',3);

    Query i have used but i am unable to proceed how to check if bal_date falling between start dates where product code starts with 3 because i should not include while comparing

    Code (SQL):
    SELECT id,
       TB.BAL_DATE,
       MAX(DECODE(TRIM(b.TYPE),'T1',b.MAX_AMT)) T1VAL,
       MAX(DECODE(TRIM(b.TYPE),'T2',b.MAX_AMT)) T2VAL,
       MAX(DECODE(TRIM(b.TYPE),'T3',b.MAX_AMT))T3VAL,
       MAX(DECODE(TRIM(b.TYPE),'T4',b.MAX_AMT))T4VAL,
       MAX(DECODE(TRIM(b.TYPE),'T1',b.RATE)) T1PER,
       MAX(DECODE(TRIM(b.TYPE),'T2',b.RATE)) T2PER,
       MAX(DECODE(TRIM(b.TYPE),'T4',b.RATE)) T4PER
    FROM balance TB,
       ref_tab_1 b,
      (SELECT DISTINCT start_date,
       end_date,
       product,
       buk_id
       FROM bucket BK
       WHERE id = 879
       AND code NOT LIKE '3%') bkt
    WHERE bkt.product = b.product
      AND TB.BAL_DATE BETWEEN b.EFF_FROM_DATE AND b.EFF_TO_DATE
      AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
    GROUP BY id,
       TB.BAL_DATE
    ORDER BY tb.bal_date;
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Rules 3 and 4 are not compatible as you have supplied them. If I had to guess, I'd say that the logic should have been stated as follows (but I most certainly am guessing):

    03. If the product code in the BUCKET table does not start with 3, T1VAL should be pulled from from REF_TABLE.
    04. If the product code in the BUCKET table starts with 3, T1VAL should be 200.

    If that's the case, the filter you have which is removing the products starting with '3' should not be used. Instead the T1VAL column should use a DECODE to display the value from REF_TABLE when a product code does not start with 3, and 200 if it does.

    I appreciate that you supplied the test case. Unfortunately the wording of the conditions you have supplied are not clear enough to be able to provide advice that I am confident about.
     
  3. rohit_shinez

    rohit_shinez Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    HI Matthew,

    You are correct below conditions is what i required, can you help in my query itself where i need to change because i am unable to proceed, but i also need to check if bal_date is falling between the dates of bucket where product code starts with 3 then only i need to make T1VAL as 200

    03. If the product code in the BUCKET table does not start with 3, T1VAL should be pulled from from REF_TABLE.
    04. If the product code in the BUCKET table starts with 3 and bal_date falling between Start and end dates of respctive code which starts with 3 then , T1VAL should be 200.
     
    Last edited: Oct 16, 2015
  4. rohit_shinez

    rohit_shinez Active Member

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

    Hope you understood or let me know if you have any queries
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The query then should look something like the following. I haven't installed the test case (and don't have time at the moment), so there may well be syntax errors. The below removes the pre-filter on BUCKET where you were eliminating the rows with a code starting with '3', and adds a DECODE to the T1VAL column.

    Code (Text):

    SELECT id,
       TB.BAL_DATE,
       MAX(DECODE(TRIM(b.TYPE),'T1',DECODE(SUBSTR(bkt.CODE, 1, 1), '3', 200, b.MAX_AMT))) T1VAL,
       MAX(DECODE(TRIM(b.TYPE),'T2',b.MAX_AMT)) T2VAL,
       MAX(DECODE(TRIM(b.TYPE),'T3',b.MAX_AMT))T3VAL,
       MAX(DECODE(TRIM(b.TYPE),'T4',b.MAX_AMT))T4VAL,
       MAX(DECODE(TRIM(b.TYPE),'T1',b.RATE)) T1PER,
       MAX(DECODE(TRIM(b.TYPE),'T2',b.RATE)) T2PER,
       MAX(DECODE(TRIM(b.TYPE),'T4',b.RATE)) T4PER
    FROM balance TB,
       ref_tab_1 b,
       bucket bkt
    WHERE bkt.product = b.product
      AND TB.BAL_DATE BETWEEN b.EFF_FROM_DATE AND b.EFF_TO_DATE
      AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
      AND bkt.id = 879
    GROUP BY id,
       TB.BAL_DATE
    ORDER BY tb.bal_date;{/CODE]
     
  6. rohit_shinez

    rohit_shinez Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Thanks what if i am having re
    Thanks Mathew what if i have configured the product codes which is configured in another lookup table like lookup_tab how can i achieve that

    create table lookup_tab(id number);

    insert into lookup_tab
    select 3333 from dual
    union all
    select 3334 from dual;

    How can i use below logic

    MAX(DECODE(TRIM(b.TYPE),'T1',DECODE(SUBSTR(bkt.CODE, 1, 1), '3', 200, b.MAX_AMT))) T1VAL,

    and morever i need to check only if bal_date falls between start and end dates of code which starts with 3 then make T1VAL as 200
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I have no idea what you're asking.
     
  8. rohit_shinez

    rohit_shinez Active Member

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

    What I am trying to ask is I will configure the product codes in lookup table which starts with 3 and need to check if the bal_date is falling between start and end dates then make t1val as 200.

    for eg in my lookup_tab the code will be 3333, and in bucket i have

    879 3333 20-Nov-07 08-Oct-08 2300 4

    i need to check if bal_date falling between the above start dates and end dates then make t1val as 200, can this be done in same query
     
    Last edited: Oct 16, 2015
  9. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    1. In the query I suggested, the DECODE (marked by arrow 1 below) should return 200 if the BUCKET.CODE value starts with '3' and REF_TAB_1.MAX_AMT if it does not. So why are you asking about a lookup table?
    2. In the WHERE clause of the same query, the condition (marked by arrow 2 below) restricts the rows being returned from the join with BUCKET using the BALANCE.BAL_DATE and the BUCKET start and end dates. So why are you asking about restricting by start and end dates?

    You have to explain why it is that these suggestions do not meet the requirements you have provided before it is possible to answer any questions about alternatives.

    Code (Text):
    SELECT id,
       TB.BAL_DATE,
       MAX(DECODE(TRIM(b.TYPE),'T1',
    1 -->           DECODE(SUBSTR(bkt.CODE, 1, 1), '3', 200, b.MAX_AMT))) T1VAL,
       MAX(DECODE(TRIM(b.TYPE),'T2',b.MAX_AMT)) T2VAL,
       MAX(DECODE(TRIM(b.TYPE),'T3',b.MAX_AMT))T3VAL,
       MAX(DECODE(TRIM(b.TYPE),'T4',b.MAX_AMT))T4VAL,
       MAX(DECODE(TRIM(b.TYPE),'T1',b.RATE)) T1PER,
       MAX(DECODE(TRIM(b.TYPE),'T2',b.RATE)) T2PER,
       MAX(DECODE(TRIM(b.TYPE),'T4',b.RATE)) T4PER
    FROM balance TB,
       ref_tab_1 b,
       bucket bkt
    WHERE bkt.product = b.product
      AND TB.BAL_DATE BETWEEN b.EFF_FROM_DATE AND b.EFF_TO_DATE
    2 -->  AND TB.BAL_DATE BETWEEN BKT.start_date AND BKT.end_date
      AND bkt.id = 879
    GROUP BY id,
       TB.BAL_DATE
    ORDER BY tb.bal_date;
     
  10. rohit_shinez

    rohit_shinez Active Member

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

    whats i am trying to say is instead of hardcoding to product which starts with 3 i will be putting the product codes which starts with 3 or it may be case that those products may start with other than 3 so i need to make t2val as 200 for the products which are present in lookup_tab
    eg

    bucket
    879 3333 20-Nov-07 08-Oct-08 2300 4
    879 3334 09-Oct-08 08-Oct-09 2300 4
    879 1234 08-OCT-00 09-Oct-09 2300 4

    here i will configure 3333,3334,1234 in lookup_tab and i which check if bucket having these products then make t2val as 200 instead of hardcoding to check that it starts with 3
     
  11. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Rohit:

    I don't allow the people who pay my salary give me tasks that change requirements once I've delivered. I certainly don't work that way for assistance I give out free of charge. This is especially true when my first post specifically asked you to confirm those requirements... which you did.

    The DECODE won't work well given the new requirement. Best I can think of offhand is to have two queries that are near-duplicates of what I provided earlier. Once has a condition of BUCKET.CODE IN (SELECT code FROM lookup_table). The second has a condition of BUCKET.CODE NOT IN (SELECT code FROM lookup_table). UNION ALL the results of the two together. There is probably a more elegant way of doing it, but I'm not concerned about elegant at this point.
     
  12. rohit_shinez

    rohit_shinez Active Member

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

    I agree with your point and it met the my requirement at first place but i would like to have this new requirement fit in same query then it would be greatful. i think it wouldnt be possible or can you help how i can achieve by looking into lookup_table
     
  13. rohit_shinez

    rohit_shinez Active Member

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

    can you advise on the query how i can use it