MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t(id int,   locationID int, Date date, Newdate date);
insert into t select 1  ,  1  , '2022-5-31',  null;
insert into t select 2  ,  1  , '2022-5-16',  null;
insert into t select 3  ,  1  , '2021-4-28',  null;
insert into t select 4  ,  2  , '2022-3-29',  null;
insert into t select 5  ,  2  , '2022-2-22',  null;
insert into t select 6  ,  3  , '2022-6-14',  null;
insert into t select 7  ,  3  , '2021-10-27', null;
insert into t select 8  ,  4  , '2022-2-01', null;
insert into t select 9  ,  4  , '2022-5-04', null;
insert into t select 10 ,  4  , '2021-6-14', null;
insert into t select 11 ,  5  , '2022-6-01', null;
insert into t select 12 ,  5  , '2022-5-29', null;
insert into t select 13 ,  5  , '2022-9-20', null;
insert into t select 14 ,  5  , '2022-8-11', null;
insert into t select 15 ,  5  , '2022-8-03', null;
select Id, locationId, Date, 
	Date_Add(First_Value(date) over(partition by locationId order by date desc), 
	  interval (13 + row_number() over(partition by locationId order by date desc)) day 
	) NewDate
from t
order by LocationId, NewDate;


Id
locationId
Date
NewDate


1
1
2022-05-31
2022-06-14

2
1
2022-05-16
2022-06-15

3
1
2021-04-28
2022-06-16

4
2
2022-03-29
2022-04-12

5
2
2022-02-22
2022-04-13

6
3
2022-06-14
2022-06-28

7
3
2021-10-27
2022-06-29

9
4
2022-05-04
2022-05-18

8
4
2022-02-01
2022-05-19

10
4
2021-06-14
2022-05-20

13
5
2022-09-20
2022-10-04

14
5
2022-08-11
2022-10-05

15
5
2022-08-03
2022-10-06

11
5
2022-06-01
2022-10-07

12
5
2022-05-29
2022-10-08


update t
join (
  select Id, 
    Date_Add(First_Value(date) over(partition by locationId order by date desc), 
      interval (13 + row_number() over(partition by locationId order by date desc)) day 
  ) NewDate
  from t
)nd on t.id = nd.id
set t.Newdate = nd.NewDate;

select * from t order by LocationId, NewDate;


id
locationID
Date
Newdate


1
1
2022-05-31
2022-06-14

2
1
2022-05-16
2022-06-15

3
1
2021-04-28
2022-06-16

4
2
2022-03-29
2022-04-12

5
2
2022-02-22
2022-04-13

6
3
2022-06-14
2022-06-28

7
3
2021-10-27
2022-06-29

9
4
2022-05-04
2022-05-18

8
4
2022-02-01
2022-05-19

10
4
2021-06-14
2022-05-20

13
5
2022-09-20
2022-10-04

14
5
2022-08-11
2022-10-05

15
5
2022-08-03
2022-10-06

11
5
2022-06-01
2022-10-07

12
5
2022-05-29
2022-10-08

Id	locationId	Date	NewDate
1	1	2022-05-31	2022-06-14
2	1	2022-05-16	2022-06-15
3	1	2021-04-28	2022-06-16
4	2	2022-03-29	2022-04-12
5	2	2022-02-22	2022-04-13
6	3	2022-06-14	2022-06-28
7	3	2021-10-27	2022-06-29
9	4	2022-05-04	2022-05-18
8	4	2022-02-01	2022-05-19
10	4	2021-06-14	2022-05-20
13	5	2022-09-20	2022-10-04
14	5	2022-08-11	2022-10-05
15	5	2022-08-03	2022-10-06
11	5	2022-06-01	2022-10-07
12	5	2022-05-29	2022-10-08