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!

Derive amount based on dates

Discussion in 'SQL PL/SQL' started by rohit_shinez, Feb 11, 2016.

  1. rohit_shinez

    rohit_shinez Active Member

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

    I am having below tables


    T1
    ID F_TYPE F_AMT DATE_COL
    1 F 6 11-FEB-16 00.00.00
    1 D 2 11-FEB-16 00.00.00
    1 D 3 12-MAR-16 00.00.00
    1 F 2 12-MAR-16 00.00.00
    1 D 1 11-APR-16 00.00.00
    1 F 4 11-APR-16 00.00.00

    T2
    ID START_DATE END_DATE F_ID FLAG
    1 11-FEB-16 00.00.00 11-MAR-16 00.00.00 1 Y
    1 12-MAR-16 00.00.00 11-APR-16 00.00.00 1 Y
    1 12-APR-16 00.00.00 21-APR-16 00.00.00 2 N
    1 22-APR-16 00.00.00 02-MAY-16 00.00.00 3 N

    Since f_id flag which 1 and flag is Y , the occurrence is only once hence there should be no subtraction to be performed



    ID F_AMT DATE_COL DERIVED_AMT

    --- ------- -------------------- -----------

    1 6 11-Feb-2016 00:00:00 6-0 = 6

    1 2 12-Mar-2016 00:00:00 2-0 = 2

    1 4 11-Apr-2016 00:00:00 4-0 = 4


    If the flag data is something like below


    update t2 set flag = 'Y' where flag = 'N' and start_date = '12-APR-2016';

    1 11-FEB-16 00.00.00 11-MAR-16 00.00.00 1 Y
    1 12-MAR-16 00.00.00 11-APR-16 00.00.00 1 Y
    1 12-APR-16 00.00.00 21-APR-16 00.00.00 2 Y
    1 22-APR-16 00.00.00 02-MAY-16 00.00.00 3 N
    Then

    ID F_AMT DATE_COL DERIVED_AMT

    --- ------- -------------------- -----------

    1 6 11-Feb-2016 00:00:00 4

    1 2 12-Mar-2016 00:00:00 -1

    1 4 11-Apr-2016 00:00:00 3

    Code (SQL):
    CREATE TABLE T1
    (id NUMBER,f_type CHAR(1),f_amt NUMBER(10),date_col DATE);
    CREATE TABLE t2
    (id NUMBER,start_date DATE,end_Date DATE,f_id NUMBER,flag CHAR(1));
    INSERT INTO T1
    SELECT 1,'F',6,trunc(sysdate) FROM dual
    UNION ALL
    SELECT 1,'F',2,trunc(sysdate)+30 FROM dual
    UNION ALL
    SELECT 1,'F',4, trunc(sysdate)+60 FROM dual
    UNION ALL
    SELECT 1,'D',2,trunc(sysdate) FROM dual
    UNION ALL
    SELECT 1,'D',3,trunc(sysdate)+30 FROM dual
    UNION ALL
    SELECT 1,'D',1, trunc(sysdate)+60 FROM dual;
    INSERT INTO T2
    SELECT 1,trunc(sysdate),trunc(sysdate+29),1,'Y' FROM dual
    UNION ALL
    SELECT 1,trunc(sysdate+30),trunc(sysdate+60),1,'Y' FROM dual
    UNION ALL
    SELECT 1,trunc(sysdate+61),trunc(sysdate+70),2,'N' FROM dual
    UNION ALL
    SELECT 1,trunc(sysdate+71),trunc(sysdate+81),3,'N' FROM dual;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Is this a question, a statement, or something else?
     
  3. rohit_shinez

    rohit_shinez Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80

    Sorry let me clarify the requirement

    i need the output like below
    ID F_AMT DATE_COL MAX(F_TYPE) DERIVED_AMT
    1 6 11-FEB-16 00.00.00 F 4(6-2)
    1 2 12-MAR-16 00.00.00 F -1(2-3)
    1 4 11-APR-16 00.00.00 F 3(4-1)

    Derivation logic:
    01. I need to check date_col falling between start dates and end dates of T2
    02. If you consider below record
    1 F 6 11-FEB-16 00.00.00
    1 D 2 11-FEB-16 00.00.00

    i need to consider the records where f_type is F and if there are any record with f_type as D with same date_col and records from T2 table with flag as N more than one then only i need to subtract

    1 6 11-FEB-16 00.00.00 F 4(6-2)
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That makes so much sense ... to you. I am still confused as to what, exactly, you want, and what, EXACTLY, your data will look like. You have provided only limited sample of the data, NONE of them matching the 'description' you recently posted.

    If you have a question then state it clearly so all can understand. So far you haven't done that.