Hi All ,
I have requirement where i need to join tables on joining condition, if there multiple rows exists then for that set check alternate matching conditions.
Any help on this is very much appreciated.
You probably need to join the first two tables MEMB_ENRLT_FACT.memb_skey = MEMB_ENRLT_FACT.grp_skey without additional condition and use a QUALIFY ROW_NUMBER with ORDER BY MEMB_ENRLT_FACT.memb_subgrp_prodt_chng_dt_skey DESC, CASE WHEN CHASF.clm_incud_from_dt_skey BETWEEN MEMB_ENRLT_FACT.memb_subgrp_prodt_bg_dt_skey AND MEMB_ENRLT_FACT.memb_subgrp_prodt_termn_dt_ske THEN 1 ELSE 0 END DESC) = 1
Put it in a Derived Table and do the same logic for the second join.