SQL & PL/SQL

If I've understood you correctly, maybe this...

select t.cust_id, min(t.reg_date) min_reg_date

from txn_dtl t

join reg_tb r on r.r_cust_id = t.cust_id and t.reg_date >= r.reg_date - interval '1' day and t.reg_date <= r.reg_date

group by t.cust_id

CUST_ID        MIN_REG_DATE

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

111            02/04/2020 13:44:54.110795

This assumes the limit is 24 hours inclusive of the reg_date

Try this:

SELECT x.ID, x.CUST_ID, x.REG_DATE, x.STATUS

FROM

( SELECT td.*, ROW_NUMBER() OVER(ORDER BY td.REG_DATE DESC) AS RwNbr

FROM TXN_DTL td

WHERE td.REG_DATE > (SELECT MAX(REG_DATE) - 1 FROM REG_TB)

) x

WHERE x.RwNbr = 1;

Results:

L. Fernigrini wrote:

Try this:

SELECT x.ID, x.CUST_ID, x.REG_DATE, x.STATUS

FROM

( SELECT td.*, ROW_NUMBER() OVER(ORDER BY td.REG_DATE DESC) AS RwNbr

FROM TXN_DTL td

WHERE td.REG_DATE > (SELECT MAX(REG_DATE) - 1 FROM REG_TB)

) x

WHERE x.RwNbr = 1;

Results:

REG_DATE is a timestamp, arithmetic like MAX(REG_DATE) - 1 converts to a date. You should always use intervals for timestamp arithmetic.

select dump(systimestamp - 1) co11, dump(sysdate) co12, dump(systimestamp - interval '1' day) co13

from dual;

| CO11 | CO12 | CO13 |
| Typ=13 Len=8: 228,7,4,1,17,35,18,0 | Typ=13 Len=8: 228,7,4,2,17,35,18,0 | Typ=188 Len=20: 228,7,4,1,16,35,18,0,192,20,151,22,1,0,5,0,0,0,2,0 |

13 = date

188 = timestamp with tz

As an aside, your INSERT to populate test data is relying on a default value of your NLS_TIMESTAMP parameters.  You just got lucky that it worked.  Better to be explicit:

INSERT INTO TXN_DTL VALUES(1,111,to_timestamp('02-MAR-2020 10.24.32.110795 AM','dd-MON-yyyy hh:mi:ss.ffffff AM'),'SUCCESS');

Also note that I changed your date to use full 4-digit year.

(sobbing while banging head on desk . . . . )

Twenty-two years ago I and thousands of my colleagues around the world began a project of busting our a*** to prevent a melt-down come 1-JAN-2000.  No, planes weren't going to fall out of the sky, but the threat to almost every computerized system was real.  That was TWENTY-TWO YEARS AGO!!!!  For crying out loud, how is it people in this business still use 2-digit years?

Thanks experts all solution worked perfectly.

I'm not convinced that the answer marked correct is correct.

I'd bet RegName might not unique, so shouldn't be grouped by.  I personally know loads of JOHNs.

Yes, you are 100% correct, I overlooked at the column definition .

Also, I understood that OP wanted just 1 row, no one per "Customer". Both your solution and Solomon's return the last one for each "customer" (using CUST_ID or REG_NAME) , but that is was clear on the requirement (at least for me...) I would have added "FOR EACH CUST_ID / REGNAME" to the first line:

Here I want the result for least registration date in txn_detl (window period less than 24 hours) table based on reg_tb .
02-APR-20 01.44.54.110795 PM  -- > Because john registered on 02-APR-20 09.42.54.110795 PM and minus 24 hours will be 01-APR-20 09.42.54.110795 PM.

Nevertheless, OP already made clear with his correct answer selection that he needed data per customer, although your answer seems better since handles the chance of REG_NAME not being unique (although also CUST_ID is not unique in the table definition, but "seems" like it should be ).

And I also agree with EdStevens suggestion on not using implicit conversions!