Let us say there is a table which stores data for student’s attendance for classes. The student only need to attend each class once weekly. Hinging on the days_diff column, we decide which weeks the student should get credited for. As usual, each week has 7 days and the end of the week (in days) is divisible by 7. There is a different case for Week 3 in my scenario, whenever the week_3 is attended, the week ends at that attendance date and week_4 is started the next day.
I am using postgreSQL 11.4!
select * from students_data
| student_name | class_subject | registration_date | attendance_date | days_diff | week_num |
|---|---|---|---|---|---|
| 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 |
Desired Week Breakdown:
student days_in_week week_number Frank 1-7 week_1 Frank 8-14 week_2 Frank 15-17 week_3 <-----week_3 shakes up the week breakdown Frank 18-24 week_4 Frank 25-31 week_5........ Danny 1-7 week_1 Danny 8-14 week_2 Danny 15-19 week_3 <----week_3 shakes up the week breakdown Danny 20-26 week_4 Danny 27-33 week_5........
Requested: new_week_num column
| 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_7 |
| 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 |
Advertisement
Answer
Solution 1 : cte and JOIN
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
Solution 2 : LATERAL JOIN
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
Solution 3 : window function
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)
test in dbfiddle