Update column as update from 01-01-21 to 15-01-21 and so on as on after each 15 days it will change from dbms range

Question

What I have is this:

VI_ID   DP_DATE EMPLOYEE_ID QTY   PRATE

    1/1/2020    10000001    33          
    1/2/2020    10000001    30          
    1/3/2020    10000001    31          
    1/4/2020    10000001    34          
    1/5/2020    10000001    30          
    1/6/2020    10000001    30          
    1/7/2020    10000001    33          
    1/8/2020    10000001    31          

What I want is this:

VI_ID   DP_DATE         EMPLOYEE_ID QTY   PRATE

    1/1/2020    10000001    33            30
    1/2/2020    10000001    30            30
    1/3/2020    10000001    31            30
    1/4/2020    10000001    34            30
    1/5/2020    10000001    30            32
    1/6/2020    10000001    30            32 
    1/7/2020    10000001    33            32 
    1/8/2020    10000001    31            32

Like here it changes after 4 days. What can I add in this code which can gives me my desire?

update DAILY_PRODUCTION
set PRATE = floor(dbms_random.value(30, 35))

In this list, there is a lot other employees, also dbms will gives each employee random number and that will same for all date for 15 days remain after 15 days it will change.

Answer 1

Set up of sample data:

CREATE TABLE daily_production
AS
        SELECT DATE '2021-1-1' + LEVEL - 1            AS dp_date,
               10000001                               AS employee_id,
               FLOOR (DBMS_RANDOM.VALUE (30, 35))     AS qty,
               CAST (NULL AS NUMBER)                  AS prate
          FROM DUAL
    CONNECT BY LEVEL <= 60;

Then by using a MERGE statement, you can update the data as you had described. The example below will change the prate every 4 days. If you modify the 3 MOD statements from 4 to 15, then the prate will change every 15 days. Note that this will only work if there are no gaps within the dates since the query is using ROWNUM. You can also modify the WHERE clause on the inner select if you only want the changes to affect a specific date range.

MERGE INTO daily_production dp
     USING (SELECT dp_date,
                   CASE
                       WHEN MOD (ROWNUM - 1, 4) > 0
                       THEN
                           LAG (prate, MOD (ROWNUM - 1, 4)) OVER (ORDER BY dp_date)
                       ELSE
                           prate
                   END    AS prate
              FROM (  SELECT dp_date,
                             CASE
                                 WHEN MOD (ROWNUM, 4) = 1 THEN FLOOR (DBMS_RANDOM.VALUE (30, 35))
                             END    AS prate
                        FROM daily_production
                       WHERE dp_date >= DATE '2021-1-1' AND dp_date <= DATE '2021-1-20'
                    ORDER BY dp_date)) d
        ON (dp.dp_date = d.dp_date)
WHEN MATCHED
THEN
    UPDATE SET dp.prate = d.prate;