By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table your_table (student_name text, class_subject text,registration_date date,attendance_date date,days_diff integer,week_num text, constraint pk primary key (student_name, class_subject,registration_date,attendance_date)) ;
insert into your_table values
('Frank', 'Chemistry', '2021-10-15', '2021-10-18', 3, 'week_1'),
('Frank', 'Chemistry', '2021-10-15', '2021-10-24', 9, 'week_2'),
('Frank', 'Chemistry', '2021-10-15', '2021-11-01', 17, 'week_3'),
('Frank', 'Chemistry', '2021-10-15', '2021-11-10', 26, 'week_4'),
('Frank', 'Chemistry', '2021-10-15', '2021-11-20', 36, 'week_6'),
('Danny', 'Physics', '2021-10-20', '2021-10-25', 5, 'week_1'),
('Danny', 'Physics', '2021-10-20', '2021-11-02', 13, 'week_2'),
('Danny', 'Physics', '2021-10-20', '2021-11-08', 19, 'week_3'),
('Danny', 'Physics', '2021-10-20', '2021-11-23', 34, 'week_5'),
('Danny', 'Physics', '2021-10-20', '2021-11-30', 41, 'week_6')
10 rows affected
WITH list AS
(
SELECT student_name, class_subject, registration_date, days_diff
FROM your_table
WHERE week_num = 'week_3'
)
SELECT t.*
, CASE
WHEN t.days_diff <= l.days_diff
THEN t.week_num
ELSE 'week_' || (3 + ceil((t.days_diff - l.days_diff)/7.0)) :: text
END AS new_week_num
FROM your_table AS t
LEFT JOIN list AS l
ON t.student_name = l.student_name
AND t.class_subject = l.class_subject
AND t.registration_date = l.registration_date
| student_name | class_subject | registration_date | attendance_date | days_diff | week_num | new_week_num |
|---|---|---|---|---|---|---|
| Frank | Chemistry | 2021-10-15 | 2021-10-18 | 3 | week_1 | week_1 |
| Frank | Chemistry | 2021-10-15 | 2021-10-24 | 9 | week_2 | week_2 |
| Frank | Chemistry | 2021-10-15 | 2021-11-01 | 17 | week_3 | week_3 |
| Frank | Chemistry | 2021-10-15 | 2021-11-10 | 26 | week_4 | week_5 |
| Frank | Chemistry | 2021-10-15 | 2021-11-20 | 36 | week_6 | week_6 |
| Danny | Physics | 2021-10-20 | 2021-10-25 | 5 | week_1 | week_1 |
| Danny | Physics | 2021-10-20 | 2021-11-02 | 13 | week_2 | week_2 |
| Danny | Physics | 2021-10-20 | 2021-11-08 | 19 | week_3 | week_3 |
| Danny | Physics | 2021-10-20 | 2021-11-23 | 34 | week_5 | week_6 |
| Danny | Physics | 2021-10-20 | 2021-11-30 | 41 | week_6 | week_7 |
SELECT t.*
, CASE
WHEN t.days_diff <= l.days_diff
THEN t.week_num
ELSE 'week_' || (3 + ceil((t.days_diff - l.days_diff)/7.0)) :: text
END AS new_week_num
FROM your_table AS t
CROSS JOIN LATERAL
( SELECT l.days_diff
FROM your_table AS l
WHERE l.student_name = t.student_name
AND l.class_subject = t.class_subject
AND l.registration_date = t.registration_date
AND l.week_num = 'week_3'
LIMIT 1
) AS l
| student_name | class_subject | registration_date | attendance_date | days_diff | week_num | new_week_num |
|---|---|---|---|---|---|---|
| Frank | Chemistry | 2021-10-15 | 2021-10-18 | 3 | week_1 | week_1 |
| Frank | Chemistry | 2021-10-15 | 2021-10-24 | 9 | week_2 | week_2 |
| Frank | Chemistry | 2021-10-15 | 2021-11-01 | 17 | week_3 | week_3 |
| Frank | Chemistry | 2021-10-15 | 2021-11-10 | 26 | week_4 | week_5 |
| Frank | Chemistry | 2021-10-15 | 2021-11-20 | 36 | week_6 | week_6 |
| Danny | Physics | 2021-10-20 | 2021-10-25 | 5 | week_1 | week_1 |
| Danny | Physics | 2021-10-20 | 2021-11-02 | 13 | week_2 | week_2 |
| Danny | Physics | 2021-10-20 | 2021-11-08 | 19 | week_3 | week_3 |
| Danny | Physics | 2021-10-20 | 2021-11-23 | 34 | week_5 | week_6 |
| Danny | Physics | 2021-10-20 | 2021-11-30 | 41 | week_6 | week_7 |
SELECT t.*
, CASE
WHEN days_diff <= (array_agg(days_diff) FILTER (WHERE week_num = 'week_3') OVER w)[1]
THEN week_num
ELSE 'week_' || (3 + ceil((t.days_diff - (array_agg(days_diff) FILTER (WHERE week_num = 'week_3') OVER w)[1])/7.0)) :: text
END AS new_week_num
FROM your_table AS t
WINDOW w AS (PARTITION BY student_name, class_subject, registration_date ORDER BY days_diff ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
| student_name | class_subject | registration_date | attendance_date | days_diff | week_num | new_week_num |
|---|---|---|---|---|---|---|
| Danny | Physics | 2021-10-20 | 2021-10-25 | 5 | week_1 | week_1 |
| Danny | Physics | 2021-10-20 | 2021-11-02 | 13 | week_2 | week_2 |
| Danny | Physics | 2021-10-20 | 2021-11-08 | 19 | week_3 | week_3 |
| Danny | Physics | 2021-10-20 | 2021-11-23 | 34 | week_5 | week_6 |
| Danny | Physics | 2021-10-20 | 2021-11-30 | 41 | week_6 | week_7 |
| Frank | Chemistry | 2021-10-15 | 2021-10-18 | 3 | week_1 | week_1 |
| Frank | Chemistry | 2021-10-15 | 2021-10-24 | 9 | week_2 | week_2 |
| Frank | Chemistry | 2021-10-15 | 2021-11-01 | 17 | week_3 | week_3 |
| Frank | Chemistry | 2021-10-15 | 2021-11-10 | 26 | week_4 | week_5 |
| Frank | Chemistry | 2021-10-15 | 2021-11-20 | 36 | week_6 | week_6 |