I have a requirement to distribute two channel equally whenever there is an add or deletion occurs. Is there any ways to allocate the relationship without randomly allocate if there is a change? Currently using the MOD function and its allocated randomly and cause a huge change of the data.
I have a sample for 3 scenarios and the expected outcome. Appreciate if anyone can share what is the function that can be used to cater such requirement.
I am using the current code as below. It closed and open the new records frequently as the party is randomly allocated.
INSERT INTO P1_TEMP.PARTY_CHANNEL_ALLOCATION_TASK(
,DATE AS START_DT
,NULL AS END_DT
,0 AS RECORD_DELETED_FLG
ORDER BY CHANNEL_ID)
FROM P1_TEMP.C100_ACRM_CHANNEL_TEMP) CRANK (CHANNEL_TYP_CD,CHANNEL_ID,CHANNEL_RANK)
FROM P1_TEMP.C100_ACRM_CHANNEL_TEMP) CTOT(CHANNEL_TOTAL)
ORDER BY PARTY.PARTY_ID DESC)
FROM P1_TEMP.C100_ACRM_PARTY_TEMP PARTY
GROUP BY 1) PRANK(PARTY_ID,PARTY_RANK)
WHEN PRANK.PARTY_RANK MOD CTOT.CHANNEL_TOTAL=0
ELSE PRANK.PARTY_RANK MOD CTOT.CHANNEL_TOTAL END);
Ouch, your current aproach will product join Party and Channel.
When you can apply QUANTILE logic to your problem there's no more product join and the mapping should be more stable. Try this (untested):
,DATE AS start_dt
,NULL AS end_dt
,0 AS record_deleted_flg
, ROW_NUMBER() OVER( ORDER BY channel_id)
) crank (channel_typ_cd,channel_id,channel_rank)
, ((ROW_NUMBER() OVER( ORDER BY party.party_id DESC) -1)
* (SEL COUNT(*) FROM P1_TEMP.C100_ACRM_CHANNEL_TEMP)
/ COUNT(*) OVER()) + 1