-2

consider TABLE1 and has the below records.

ID  MBR_ID     STRT_DT       END_DT    ID_TYPE  
1    001      01-01-2019   31-12-2019   TYPE_1
2    002      02-05-2019   31-07-2020   TYPE_1
3    002      03-03-2019   31-12-2020   TYPE_2
4    003      03-03-2019   31-12-2020   TYPE_3
5    003      01-01-2019   31-12-2019   TYPE_4
6    003      13-05-2019   31-12-2020   TYPE_5

TABLE2

s.no   MBR_ID      DOB
1       001     30-03-2019
2       002     29-05-2019
3       003     01-08-2019

I want to fetch the records which satisfies the below conditions:

sel * 
from TABLE1 
join TABLE2 on TABLE1.MBR_ID=TABLE2.MBR_ID.MBR_ID 
where TABLE2.DOB between TABLE1.STRT_DT and TABLE2.END_DT
  • in case of duplicates, pull the ID from TABLE1 which has maximum of END_DT
  • if there are still duplicates, pull the ID from TABLE1 which has maximum of ID

The result should be like below:

ID  MBR_ID     STRT_DT       END_DT    ID_TYPE  
1    001      01-01-2019   31-12-2019   TYPE_1
3    002      03-03-2019   31-12-2020   TYPE_2
6    003      13-05-2019   31-12-2020   TYPE_5

I should fetch the ID from TABLE1. We have 3 different ID present for the same MBR_ID. But I should fetch the maximum ID from TABLE1 if I have same maximum END_DT.

In above table, 3 different ID are there for MBR_ID=003. So, first I should check if DOB lies between STRT_DT and END_DT of TABLE1.

I have 3 rows which satisfies the same. So I need to fetch the row which has high END_DT. For this, 2 rows have same high END_DT. So I need to fetch the maximum(ID) from TABLE1.

1
  • on TABLE1.MBR_ID=TABLE2.MBR_ID.MBR_ID - typo. between TABLE1.STRT_DT and TABLE2.END_DT - typo (?) too.
    – Akina
    Commented Aug 28, 2020 at 6:51

1 Answer 1

1

Apply a ROW_NUMBER using a Derived Table/CTE in Standard SQL or simplified using Teradata's QULIAFY syntax:

select * 
from TABLE1 
join TABLE2 on TABLE1.MBR_ID=TABLE2.MBR_ID 
where TABLE2.DOB between TABLE1.STRT_DT and TABLE2.END_DT
QUALIFY 
   ROW_NUMBER()
   OVER (PARTITION BY TABLE1.MBR_ID    --  for each MBR_ID
         ORDER BY TABLE2.END_DT DESC   --  ID from TABLE1 which has maximum of END_DT
                 ,TABLE1.ID DESC) = 1  --  ID from TABLE1 which has maximum of ID
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.