Forum Stats

  • 3,769,356 Users
  • 2,252,953 Discussions
  • 7,874,996 Comments

Discussions

Help to solve this SQL

Lazar
Lazar Member Posts: 190 Blue Ribbon

Hi,

DB version : 12 C

I need to frame a SQL based on the below result set.


Expected output:-


Requirement :-

SID and STATE are the composite primary keys.

Yellow highlighted is the latest and recent data record (ACT10), followed by ACT9, ACT8 , ... and so on.

So the requirement is to take the latest version CREATE_USER_DATE(ACT10) and to compare with the previous versions(ACT9, ACT8, etc..) CREATE_USER_DATE.

If the CREATE_USER_DATE is matching with the previous STATE(ACT9), then we need to ignore and to move onto compare the Next UNMATCHING USER_DATE (i.e. ACT6)

I need to frame a dynamic SQL in which the data might change interms of another SID.(Some SIDs have more than ACT20 versions).


Test Data :

CREATE TABLE TEST1(SID NUMBER,STATE VARCHAR(20),CREATE_USER_DATE DATE);

INSERT INTO TEST1 VALUES(10011,'ACT9','29-JUL-24');

INSERT INTO TEST1 VALUES(10011,'ACT8','29-JUL-24');

INSERT INTO TEST1 VALUES(10011,'ACT7','29-JUL-24');

INSERT INTO TEST1 VALUES(10011,'ACT6','29-JUL-20');

INSERT INTO TEST1 VALUES(10011,'ACT5','22-JUL-21');

INSERT INTO TEST1 VALUES(10011,'ACT10','29-JUL-24');

Tagged:

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,486 Red Diamond
    Accepted Answer

    And just to show how awkward it is if you don't have a proper means to order your data, having to split the number part out of the "state" means doing something like:

    SQL> with t(sid, state, create_user_date) as (
      2    select 10011, 'ACT10', date '2024-07-29' from dual union all
      3    select 10011, 'ACT9', date '2024-07-29' from dual union all
      4    select 10011, 'ACT8', date '2024-07-29' from dual union all
      5    select 10011, 'ACT7', date '2024-07-29' from dual union all
      6    select 10011, 'ACT6', date '2020-07-29' from dual union all
      7    select 10011, 'ACT5', date '2021-07-22' from dual
      8    )
      9   ,sub_set as (
     10      select sid
     11            ,max(to_number(replace(state,'ACT'))) keep (dense_rank first order by to_number(replace(state,'ACT')) desc) as act
     12  --          ,create_user_date
     13            ,max(create_user_date) keep (dense_rank first order by to_number(replace(state,'ACT')) desc) as create_user_date
     14      from   t
     15      group by sid, create_user_date
     16      order by sid--, to_number(replace(state,'ACT')) desc
     17      )
     18  select sid
     19        ,'ACT'||to_char(act) as state
     20        ,create_user_date
     21        ,row_number() over (partition by sid order by act desc, create_user_date desc) as rn
     22        ,case when row_number() over (partition by sid order by act desc, create_user_date desc) = 1 then 'Current'
     23              when row_number() over (partition by sid order by act desc, create_user_date desc) = 2 then 'Previous'
     24         else 'Older'
     25         end as which_is_which
     26  from   sub_set
     27  order by act desc, create_user_date desc
     28  /
    
    
           SID STATE                                       CREATE_USER_DATE             RN WHICH_IS
    ---------- ------------------------------------------- -------------------- ---------- --------
         10011 ACT10                                       29-JUL-2024 00:00:00          1 Current
         10011 ACT6                                        29-JUL-2020 00:00:00          2 Previous
         10011 ACT5                                        22-JUL-2021 00:00:00          3 Older
    


    If you had something better to order on it would be a little tidier.

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,486 Red Diamond

    Yellow highlighted is the latest and recent data record (ACT10), followed by ACT9, ACT8 , ... and so on.


    How is that determined?

    'ACT10' is just a string, so you can't order by that:

    SQL> with t(str) as (
      2    select 'ACT1' from dual union all
      3    select 'ACT2' from dual union all
      4    select 'ACT3' from dual union all
      5    select 'ACT10' from dual union all
      6    select 'ACT11' from dual union all
      7    select 'ACT21' from dual union all
      8    select 'ACT22' from dual
      9    )
     10  select str
     11  from   t
     12  order by str
     13  /
    
    STR
    -----
    ACT1
    ACT10
    ACT11
    ACT2
    ACT21
    ACT22
    ACT3
    
    7 rows selected.
    


    without somehow fudging it to create an order... e.g.

    SQL> ed
    Wrote file afiedt.buf
    
    
      1  with t(str) as (
      2    select 'ACT1' from dual union all
      3    select 'ACT2' from dual union all
      4    select 'ACT3' from dual union all
      5    select 'ACT10' from dual union all
      6    select 'ACT11' from dual union all
      7    select 'ACT21' from dual union all
      8    select 'ACT22' from dual
      9    )
     10  select str
     11  from   t
     12* order by to_number(replace(str,'ACT'))
    SQL> /
    
    STR
    -----
    ACT1
    ACT2
    ACT3
    ACT10
    ACT11
    ACT21
    ACT22
    
    7 rows selected.
    

    Though hopefully you're not doing something as silly as that.

    Perhaps your CREATE_USER_DATE has a time component to it? Is that what gives it the order?

  • BluShadow
    BluShadow Member, Moderator Posts: 41,486 Red Diamond
    Accepted Answer

    And just to show how awkward it is if you don't have a proper means to order your data, having to split the number part out of the "state" means doing something like:

    SQL> with t(sid, state, create_user_date) as (
      2    select 10011, 'ACT10', date '2024-07-29' from dual union all
      3    select 10011, 'ACT9', date '2024-07-29' from dual union all
      4    select 10011, 'ACT8', date '2024-07-29' from dual union all
      5    select 10011, 'ACT7', date '2024-07-29' from dual union all
      6    select 10011, 'ACT6', date '2020-07-29' from dual union all
      7    select 10011, 'ACT5', date '2021-07-22' from dual
      8    )
      9   ,sub_set as (
     10      select sid
     11            ,max(to_number(replace(state,'ACT'))) keep (dense_rank first order by to_number(replace(state,'ACT')) desc) as act
     12  --          ,create_user_date
     13            ,max(create_user_date) keep (dense_rank first order by to_number(replace(state,'ACT')) desc) as create_user_date
     14      from   t
     15      group by sid, create_user_date
     16      order by sid--, to_number(replace(state,'ACT')) desc
     17      )
     18  select sid
     19        ,'ACT'||to_char(act) as state
     20        ,create_user_date
     21        ,row_number() over (partition by sid order by act desc, create_user_date desc) as rn
     22        ,case when row_number() over (partition by sid order by act desc, create_user_date desc) = 1 then 'Current'
     23              when row_number() over (partition by sid order by act desc, create_user_date desc) = 2 then 'Previous'
     24         else 'Older'
     25         end as which_is_which
     26  from   sub_set
     27  order by act desc, create_user_date desc
     28  /
    
    
           SID STATE                                       CREATE_USER_DATE             RN WHICH_IS
    ---------- ------------------------------------------- -------------------- ---------- --------
         10011 ACT10                                       29-JUL-2024 00:00:00          1 Current
         10011 ACT6                                        29-JUL-2020 00:00:00          2 Previous
         10011 ACT5                                        22-JUL-2021 00:00:00          3 Older
    


    If you had something better to order on it would be a little tidier.

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

    Hi, @Lazar

    Thanks for posting the sample data.

    CREATE TABLE TEST1(SID NUMBER,STATE VARCHAR(20),CREATE_USER_DATE DATE);

    INSERT INTO TEST1 VALUES(10011,'ACT9','29-JUL-24');

    Don't try to insert string values (such as '29-JUL-24') into a DATE column (such as create_user_date). Use TO_DATE, or DATE literals.

    Wouldn't it be better to test with a couple of different sid values? What do you want if the is only one row for a sid? What if there are multiple rows, but they all have the same create_user_date?

    I need to frame a dynamic SQL in which the data might change interms of another SID.(Some SIDs have more than ACT20 versions).

    Why does this need to be dynamic? Perhaps you're using "dynamic" to mean something different from what most other people mean. Rephrase the section above without using the word "dynamic".

  • mathguy
    mathguy Member Posts: 10,155 Blue Diamond

    It seems that you are treating the number part of something like ACT6 as significant (a version number or similar).

    In your data, the "created date" for ACT6 is before the "creation date" of ACT5. Is that a mistake on your part, or can that actually happen?

    In any case - in your problem description you said "next unmatching user date" - but that would be the date for ACT5, not the date for ACT6. (This is why I asked the first question: was that a mistake?)

    If the order "by version" should not contradict the order "by date", and the data you gave us was wrong, then the problem can be stated much more simply: find the second most recent (second highest) date in the date column. The ACT... business would not even be needed.

    Please clarify.

    Also: What if there are two or more rows tied for "second most recent date"? Do you need just one row - the one with the highest version number? And what if all the rows in the input have the same date? Or is that impossible in your data? (If it is possible, then perhaps the result is zero rows - no output at all?)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond
    edited Aug 13, 2021 12:53PM

    Hi, @Lazar

    Does state always contain exactly one sub-string consisting of all digits? (If not, give some examples of possible statue values that do not match that pattern.)

    What is the correct order of the following state values?

    • 'ACT10'
    • 'XYZ9'
    • 'act1'
    • 'ACT010'
    • 'ACT10SCENE ii'
    • 'ACT 12'

    Whatever you answer, why is that the correct order?

  • EdStevens
    EdStevens Member Posts: 28,525 Gold Crown

    Congratulations. You just re-created the Y2K bug, 22 years after the industry solved it.

    Is '29-JUL-24' supposed to be July 29, 2024? or July 29, 1924? or 24 July, 1929? or ... or .. or

    Please, please, please, or the sake of all sanity, always, always, always use 4-digit years. It seems that all the work that I and legions of my colleagues around the world did in 1998 and 1999 is being lost.

    PaulzipBeefStuBluShadowKayK