Need a sql help to check when multiple rows exists if match found then do alternate match found.

Database

Need a sql help to check when multiple rows exists if match found then do alternate match found.

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.

requirement :

  • MEMB_DIMN.memb_skey to join to the MEMB_ENRLT_FACT.memb_skey to get the MEMB_ENRLT_FACT.grp_skey.  If multiple records are found, select the record where the CHASF.clm_incud_from_dt_skey is greater than or equal to the MEMB_ENRLT_FACT.memb_subgrp_prodt_bg_dt_skey and less than or equal to the MEMB_ENRLT_FACT.memb_subgrp_prodt_termn_dt_ske for the max MEMB_ENRLT_FACT.memb_subgrp_prodt_chng_dt_skey. Use the MEMB_ENRLT_FACT.grp_skey to join to the EGR.EMPR_GRP_DIMN.grp_skey.  If multiple records exist, select the record where the CHASF.clm_incud_from_dt_skey is on or after the EMPR_GRP_DIMN.srce_efft_from_dt and or before the EMPR_GRP_DIMN.term_dt for the max EMPR_GRP_DIMN.rec_beg_efft_dt and EMPR_GRP_DIMN.rec_end_efft_dt range.  If multiple records exist, select the record where the rec_cur_row_indc is Y.

Any help on this is very much appreciated.

1 REPLY
Senior Apprentice

Re: Need a sql help to check when multiple rows exists if match found then do alternate match found.

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.

Dieter