Forum Stats

  • 3,782,046 Users
  • 2,254,585 Discussions
  • 7,879,901 Comments

Discussions

Categories

First rec of the last group!!!

Thirumoorthy Selvaraj
Thirumoorthy Selvaraj Member Posts: 19 Red Ribbon
edited Jul 13, 2021 8:15PM in SQL & PL/SQL

Hi All

Any help for me to resolve this issue will be greatly appreciated.

I have data like this below

My history table data looks like

image.png

My current table data is

image.png

I need the SQL output that has Add date of 8/1/2020 (SNO : 6). I have RecVal and IND to match and use change_date to start my search from my current table data for the RecVal..

I need to search from the bottom of my history list and get to data that has same RecVal and IND of my current table record and get the minimum add_date based on my change_date.

I dont want history records 1 or 2 based on RecVal and IND

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • mathguy
    mathguy Member Posts: 10,223 Blue Diamond
    edited Jul 14, 2021 5:10PM Accepted Answer

    The solution below assumes RECVAL and IND are non-null in both tables. If NULL may appear in those columns, you will have to tell us how that should be handled. I am also assuming that CHANGE_DATE is unique (no duplicates).

    with
  t (recval, ind, qval, add_date, change_date, curr_recval, curr_ind) as (
    select h.recval, h.ind, h.qval, h.add_date, h.change_date, c.recval, c.ind
    from   hist_table h join curr_table c
                        on   h.change_date <= c.change_date
  )
select recval, ind, qval, add_date, change_date
from   t
match_recognize(
  order     by change_date desc
  all rows  per match
  pattern   ( ^ {- a* b* -} b )
  define    a as recval != curr_recval or  ind != curr_ind,
            b as recval  = curr_recval and ind  = curr_ind
);



RECVAL     IND QVAL ADD_DATE    CHANGE_DATE
---------- --- ---- ----------- -----------
TEST01     Y   P    01-AUG-2020 15-AUG-2020
    Thirumoorthy SelvarajUser_WI23P
    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,388 Red Diamond

    Hi, @Thirumoorthy Selvaraj

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of how you get those results from that data.

    Define any non-standard terms you use. For example, What is a "record"? How does it differ from a row?

    If you can show what the problem is using commonly available tables (like the tables in the Oracle-supplied SCOTT schema) then you don't need to post any sample data; just the results and explanation.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    My current record is

    image.png

    I don't see that row in the sample data.

    Thirumoorthy Selvaraj
    · Share on TwitterShare on Facebook
  • Thirumoorthy Selvaraj
    Thirumoorthy Selvaraj Member Posts: 19 Red Ribbon
    edited Jul 13, 2021 7:48PM

    Added Creates and Insert statements


     CREATE TABLE "HIST_TABLE" 

     ( "RECVAL" VARCHAR2(10 BYTE), 

       "IND" VARCHAR2(1 BYTE), 

       "QVAL" VARCHAR2(4 BYTE), 

       "ADD_DATE" DATE, 

       "CHANGE_DATE" DATE

     );


    Insert into HIST_TABLE values ('TEST01','Y','A',to_date('05-APR-20','DD-MON-RR'),to_date('05-MAY-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','Y','B',to_date('05-MAY-20','DD-MON-RR'),to_date('01-JUN-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST02','N','X',to_date('01-JUN-20','DD-MON-RR'),to_date('10-JUN-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST02','N','Y',to_date('10-JUN-20','DD-MON-RR'),to_date('01-JUL-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','N','Z',to_date('01-JUL-20','DD-MON-RR'),to_date('01-AUG-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','Y','P',to_date('01-AUG-20','DD-MON-RR'),to_date('15-AUG-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','Y','Q',to_date('15-AUG-20','DD-MON-RR'),to_date('10-SEP-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','Y','R',to_date('10-SEP-20','DD-MON-RR'),to_date('01-NOV-20','DD-MON-RR'));

    Insert into HIST_TABLE values ('TEST01','Y','S',to_date('01-NOV-20','DD-MON-RR'),to_date('02-DEC-20','DD-MON-RR'));



    CREATE TABLE "CURR_TABLE" 

     ( "RECVAL" VARCHAR2(10 BYTE), 

      "IND" VARCHAR2(1 BYTE), 

      "QVAL" VARCHAR2(4 BYTE), 

      "CHANGE_DATE" DATE

     );


    Insert into CURR_TABLE values ('TEST01','Y','Q',to_date('02-DEC-20','DD-MON-RR'));

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,388 Red Diamond
    edited Jul 13, 2021 7:58PM

    Hi,

    Would you like to get answers that work? Make sure that the CREATE TABLE and INSERT statements you post for the sample data work, too. Test (and, if necessary, fix) them before you post them.

    Don't forget to define any non-standard terms you use, such as "record" and "break" and "based on my change_date".

    Always give your full Oracle version.

    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,223 Blue Diamond
    edited Jul 14, 2021 5:10PM Accepted Answer

    The solution below assumes RECVAL and IND are non-null in both tables. If NULL may appear in those columns, you will have to tell us how that should be handled. I am also assuming that CHANGE_DATE is unique (no duplicates).

    with
  t (recval, ind, qval, add_date, change_date, curr_recval, curr_ind) as (
    select h.recval, h.ind, h.qval, h.add_date, h.change_date, c.recval, c.ind
    from   hist_table h join curr_table c
                        on   h.change_date <= c.change_date
  )
select recval, ind, qval, add_date, change_date
from   t
match_recognize(
  order     by change_date desc
  all rows  per match
  pattern   ( ^ {- a* b* -} b )
  define    a as recval != curr_recval or  ind != curr_ind,
            b as recval  = curr_recval and ind  = curr_ind
);



RECVAL     IND QVAL ADD_DATE    CHANGE_DATE
---------- --- ---- ----------- -----------
TEST01     Y   P    01-AUG-2020 15-AUG-2020
    Thirumoorthy SelvarajUser_WI23P
    · Share on TwitterShare on Facebook