All Forums Database
aniki8 2 posts Joined 11/08
18 May 2016
Distribution Data Function/Solution

Dear members,
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.

dnoeth 4628 posts Joined 11/04
19 May 2016

Can you show how you currently do it?

Dieter

aniki8 2 posts Joined 11/08
19 May 2016

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(
CHANNEL_TYP_CD
,CHANNEL_ID
,PARTY_ID
,START_DT
,END_DT
,RECORD_DELETED_FLG
)
SEL
CRANK.CHANNEL_TYP_CD
,CRANK.CHANNEL_ID
,PRANK.PARTY_ID
,DATE AS START_DT
,NULL AS END_DT
,0 AS RECORD_DELETED_FLG
FROM
(SEL
CHANNEL_TYP_CD,
CHANNEL_ID,
ROW_NUMBER() OVER(
ORDER BY CHANNEL_ID)
FROM P1_TEMP.C100_ACRM_CHANNEL_TEMP) CRANK (CHANNEL_TYP_CD,CHANNEL_ID,CHANNEL_RANK)
CROSS JOIN
(SEL COUNT(*)
FROM P1_TEMP.C100_ACRM_CHANNEL_TEMP) CTOT(CHANNEL_TOTAL)
INNER JOIN
(
SELECT
PARTY.PARTY_ID,

ROW_NUMBER() OVER(
ORDER BY PARTY.PARTY_ID DESC)  
FROM P1_TEMP.C100_ACRM_PARTY_TEMP PARTY
GROUP BY 1) PRANK(PARTY_ID,PARTY_RANK)
 ON CRANK.CHANNEL_RANK=(
  CASE
   WHEN PRANK.PARTY_RANK MOD CTOT.CHANNEL_TOTAL=0
THEN CTOT.CHANNEL_TOTAL
ELSE PRANK.PARTY_RANK MOD CTOT.CHANNEL_TOTAL END);
 

dnoeth 4628 posts Joined 11/04
20 May 2016

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):

SEL
  crank.channel_typ_cd
 ,crank.channel_id
 ,prank.party_id
 ,DATE AS start_dt
 ,NULL AS end_dt
 ,0    AS record_deleted_flg
FROM
   ( 
      SEL
         channel_typ_cd 
       , channel_id 
       , ROW_NUMBER() OVER( ORDER BY channel_id)
      FROM 
         P1_TEMP.C100_ACRM_CHANNEL_TEMP 
   ) crank (channel_typ_cd,channel_id,channel_rank)
INNER JOIN
   (
      SELECT
         party.party_id 
       , ((ROW_NUMBER() OVER( ORDER BY party.party_id DESC) -1) 
          * (SEL COUNT(*) FROM P1_TEMP.C100_ACRM_CHANNEL_TEMP)
          / COUNT(*) OVER()) + 1
      FROM 
         P1_TEMP.C100_ACRM_PARTY_TEMP PARTY
      GROUP BY 
         1 
   ) prank(party_id,party_rank)
ON crank.channel_rank=prank.party_rank;

 

Dieter

You must sign in to leave a comment.