Forum Stats

  • 3,769,815 Users
  • 2,253,026 Discussions
  • 7,875,214 Comments

Discussions

Active records by max insert date

User_DIL60
User_DIL60 Member Posts: 20 Green Ribbon

Gurus,

I am trying to write a query to identify the max (inserted date) records for each ID. Once those records are identified, I want to set the active indicator to 0 for the remaining dates. Below is an example.


In the above pic, M_ID 123456 has ACT_IND 1 for 2 ID_NO 1 & 2 with different inserted dates. I want to identify the max inserted date record and update the other record to ACT_IND=0. Which means ID_No 2 should only have ACT_IND=1 as it has max inserted date of 23 oct 21. ID_No 1 should have ACT_IND=0.


Similarly for M_ID 234567 as three records having ACT_IND=1. Of the three, only the latest record which is ID_NO 5 inserted on 12-JUL-21 should have ACT_IND=1 and the other should be 0.

There could be only one active record with ACT_IND=1 but can be ignored. The issue is that somehow need to identify the M_ID's that have multiple inserted dates, retain the max inserted date active indicator to 1 and set the other to 0.

Expected output as below.

Scripts as below:

create table test_data

(

id_no int,

m_id varchar2(20 byte) ,

act_ind int,

inserted_dt date);


INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('1', '123456', '1', TO_DATE('2021-10-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('2', '123456', '1', TO_DATE('2021-10-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('3', '234567', '1', TO_DATE('2021-07-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('4', '234567', '1', TO_DATE('2021-07-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('5', '234567', '1', TO_DATE('2021-07-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('6', '345678', '1', TO_DATE('2021-04-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('7', '334455', '1', TO_DATE('2021-01-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('8', '334455', '1', TO_DATE('2021-05-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

COMMIT;

Any suggestions please?

Tagged:
«1

Answers

  • EdStevens
    EdStevens Member Posts: 28,531 Gold Crown

    And what have you tried?

    Seems to me that if the business requirement is that, for a given M_ID, only the latest INSERTED_DTE is to be considered 'active', then you really don't even need the indicator. The record is defined as 'active' merely by being the one with the latest INSERTED_DTE.

  • User_DIL60
    User_DIL60 Member Posts: 20 Green Ribbon

    Correct, only the latest INSERTED_DATE should have ACT_IND=1 rest all should have ACT_IND=0 for a given M_ID.


    SELECT M_ID,COUNT(*)

    FROM TEST_DATA

    GROUP BY M_ID

    HAVING COUNT(*)>1;

    The above query should not give any records but due to duplicates, I am getting records.

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Oct 26, 2021 11:52PM

    Assuming you have no ties on INSERTED_DT for an M_ID and INSERTED_DT is not nullable:

    select id_no, m_id
         , case when inserted_dt < max(inserted_dt) over (partition by m_id) then 0 else 1 end act_ind
         , inserted_dt
    from test_data
    /
         ID_NO|M_ID   |   ACT_IND|INSERTED_DT
    ----------|-------|----------|-----------
             1|123456 |         0|22/10/2021 
             2|123456 |         1|23/10/2021 
             3|234567 |         0|10/07/2021 
             4|234567 |         0|11/07/2021 
             5|234567 |         1|12/07/2021 
             7|334455 |         0|22/01/2021 
             8|334455 |         1|22/05/2021 
             6|345678 |         1|22/04/2021 
    
    8 rows selected.
    


  • User_DIL60
    User_DIL60 Member Posts: 20 Green Ribbon

    Thank you @Paulzip, but how to issue an update statement on the table to update the records? I want to update the data in the table.


    UPDATE TEST_DATA

    SET ACT_IND=0

    WHERE INSERTED_DT<MAX(INSERTED_DT) OVER (PARTITION BY M_ID); I am getting group function not allowed here. Looks like I can't use max function correct?

  • User_DIL60
    User_DIL60 Member Posts: 20 Green Ribbon

    Thank you. Yes, ID_NO is the primary key. It seems the data manipulation is not legal.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond
    edited Oct 27, 2021 12:21AM

    Hi, @User_DIL60

    I am getting group function not allowed here. 

    Right; analytic functions are computed after the WHERE clause is applied, so you can never use them in a WHERE clause. Here's one way that uses the aggregate MAX function:

    UPDATE  test_data
    SET	act_ind = 0
    WHERE	act_ind = 1
    AND	(m_id, inserted_dt) NOT IN
    	(
    	  SELECT   m_id
    	  ,	   MAX (inserted_dt)
    	  FROM     test_data
    	  WHERE    act_ind = 1
    	  GROUP BY m_id
    	)
    ;
    

    What do you want to do in case of ties? When the latest inserted_dt is not unique, the statement above leaves all the rows that have the latest inserted_dt with act_id=1.

  • User_DIL60
    User_DIL60 Member Posts: 20 Green Ribbon

    Thank you @Frank Kulash Correct, all the latest inserted date records no matter the active indicator status (either 0 or 1)should have an active indicator as 1. Rest of the records should have an active indicator 0.



    Like in the above example, M_ID 123456 with oldest inserted date 22 oct 21 is set to 0 where as 23 oct 21 should have active indicator 1. Similary, for M_ID 2345677 only latest inserted date 12-jul-21 should have active indicator 1 and all other records for that M_ID should be set to 0.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond
    edited Oct 27, 2021 12:54PM

    Hi, @User_DIL60

    all the latest inserted date records no matter the active indicator status (either 0 or 1) ...

    In that case, it would make sense to test with some sample data where act_id=0, rather than having act_id=1 on all rows. Here's one way to do it:

    MERGE ​INTO test_data dst
    USING (
              SELECT  id_no
       	  , 	  CASE  RANK () OVER ( PARTITION BY m_id
    				       ORDER BY   inserted_dt DESC
    	  	  	    	     )
    	              WHEN 1
    		      THEN 1
    		      ELSE 0
    		  END AS act_ind
    	  FROM    test_data
        ) src
    ON   (dst.id_no = src.id_no)
    WHEN MATCHED THEN UPDATE
    SET   dst.act_ind = src.act_ind
    WHERE	dst.act_ind <> src.act_ind
    ;
    

    assuming id_no is unique.

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

    Hi, @User_DIL60

    It seems like you should be able to use MERGE, where the USING clause is just what Paulzip posted earlier:

    MERGE INTO test_data dst
    USING (
              SELECT  id_no, m_id
       	  , 	  CASE
    	  	      WHEN inserted_dt < MAX (inserted_dt) OVER (PARTITION BY m_id)
    		      THEN 0
    		      ELSE 1
    		  END  AS act_ind
       	  , 	  inserted_dt
    	  FROM    test_data
        ) src
    ON   (dst.id_no = src.id_no)
    WHEN MATCHED THEN UPDATE
    SET     dst.act_ind =  src.act_ind
    WHERE	dst.act_ind <> src.act_ind
    ;
    

    but when I try it, I get this error:

                WHEN inserted_dt < MAX (inserted_dt) OVER (PARTITION BY m_id)
                                 *
    ERROR at line 5:
    ORA-00932: inconsistent datatypes: expected DATE got NUMBER
    

    I'm using Oracle 18.4.0.0.0. I wonder if this works in outher versions.

  • Arif Khadas
    Arif Khadas Member Posts: 1,073 Gold Badge
    edited Oct 27, 2021 7:07AM

    Hi,

    I am not sure at what stage do you want to update the records, but the below can be one of the option assuming that the latest record will always have the latest insert date.

    Use a BEFORE INSERT trigger

    CREATE OR REPLACE TRIGGER TEST_TRG

    BEFORE INSERT

    ON TEST_DATA

    REFERENCING NEW AS New OLD AS Old

    FOR EACH ROW

    DECLARE

    BEGIN

    UPDATE TEST_DATA

    SET  ACT_IND   = 0

    WHERE M_ID    = :NEW.M_ID

    AND ACT_IND = 1;

    :NEW.ACT_IND := 1;

    END ;

    /