Active records by max insert date

User_DIL60 · Oct 26, 2021 9:51PM

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?

EdStevens · Oct 26, 2021 10:30PM

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 · Oct 26, 2021 11:24PM

https://community.oracle.com/tech/developers/discussion/comment/16813443#Comment_16813443

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 · Oct 26, 2021 11:45PM

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 · Oct 27, 2021 12:04AM

https://community.oracle.com/tech/developers/discussion/comment/16813447#Comment_16813447

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 · Oct 27, 2021 12:18AM

https://community.oracle.com/tech/developers/discussion/comment/16813449#Comment_16813449

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

Frank Kulash · Oct 27, 2021 12:19AM

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 · Oct 27, 2021 12:54AM

https://community.oracle.com/tech/developers/discussion/comment/16813451#Comment_16813451

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 · Oct 27, 2021 2:06AM

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 · Oct 27, 2021 2:14AM

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 · Oct 27, 2021 7:01AM

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 ;

/

Forum Stats

Quick Links

Categories

Active records by max insert date

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Active records by max insert date

Answers