Adding a new column with sequential numbers that repeat over and over again-CodePudding

How do we add a column to the results of the query below in order to assign...

Key_Week1 to the 1st record 
Key_Week2 to the 2nd record 
Key_Week3 to the 3rd record 
   
Key_Week1 to the 4th record 
Key_Week2 to the 5th record 
Key_Week3 to the 6th record
   
Key_Week1 to the 7th record 
Key_Week2 to the 8th record 
Key_Week3 to the 9th record

And on and on and on...following the above pattern repeatedly?

Alternatively, you can use 1, 2 and 3 instead of Key_Week1, 
Key_Week2 and Key_Week3 for the new columns values

select distinct trunc(GenerateTimeBy1Day,'day') as claim_eff_date, trunc(GenerateTimeBy1Day,'day')   20 AS bwe_to_complete_by from
(
    select from_dt   (level - 1)/1 as GenerateTimeBy1Day 
    from (select from_dt
        ,to_dt
        ,to_dt - from_dt   1 as days_between    
    from (select to_date('22-Dec-2019') as from_dt
               , to_date('30-Dec-2040') as to_dt 
          from dual))
    connect by (level - 1) <= days_between  
)
order by claim_eff_date

Current Results: (Simply run the above query. No sample data is needed)

Claim_Eff_Date	BWE_To_Complete_By
22-DEC-19	11-JAN-20
29-DEC-19	18-JAN-20
05-JAN-20	25-JAN-20
12-JAN-20	01-FEB-20
19-JAN-20	08-FEB-20
26-JAN-20	15-FEB-20
02-FEB-20	22-FEB-20
09-FEB-20	29-FEB-20
16-FEB-20	07-MAR-20

Desired Results:

Claim_Eff_Date	BWE_To_Complete_By	Key_Week_Group
22-DEC-19	11-JAN-20	Key_Week1
29-DEC-19	18-JAN-20	Key_Week2
05-JAN-20	25-JAN-20	Key_Week3
12-JAN-20	01-FEB-20	Key_Week1
19-JAN-20	08-FEB-20	Key_Week2
26-JAN-20	15-FEB-20	Key_Week3
02-FEB-20	22-FEB-20	Key_Week1
09-FEB-20	29-FEB-20	Key_Week2
16-FEB-20	07-MAR-20	Key_Week3

Claim_Eff_Date	BWE_To_Complete_By	Key_Week_Group
22-DEC-19	11-JAN-20	1
29-DEC-19	18-JAN-20	2
05-JAN-20	25-JAN-20	3
12-JAN-20	01-FEB-20	1
19-JAN-20	08-FEB-20	2
26-JAN-20	15-FEB-20	3
02-FEB-20	22-FEB-20	1
09-FEB-20	29-FEB-20	2
16-FEB-20	07-MAR-20	3

And on and on and on

CodePudding user response：

One option is just to calculate the row_number() and then do a mod to get the repitition.

with your_data as (
select distinct trunc(GenerateTimeBy1Day,'day') as claim_eff_date, 
                trunc(GenerateTimeBy1Day,'day')   20 AS bwe_to_complete_by 
from
(
    select from_dt   (level - 1)/1 as GenerateTimeBy1Day 
    from (select from_dt
        ,to_dt
        ,to_dt - from_dt   1 as days_between    
    from (select to_date('22-Dec-2019') as from_dt
               , to_date('30-Dec-2040') as to_dt 
          from dual))
    connect by (level - 1) <= days_between  
)
order by claim_eff_date
)
select your_data.*,
       mod( row_number() over (order by claim_eff_date) - 1, 3 )   1 key_week_group
  from your_data