| 10 | |
| 11 | {{{ |
| 12 | DECLARE correct_student_id INT; |
| 13 | SET correct_student_id = 11111; |
| 14 | |
| 15 | SELECT * |
| 16 | FROM ( |
| 17 | SELECT |
| 18 | ce.calendar_event_id, |
| 19 | ce.event_name, |
| 20 | ce.event_type, |
| 21 | ce.event_start_time, |
| 22 | ce.event_end_time, |
| 23 | nre.event_date |
| 24 | FROM |
| 25 | student_event AS se |
| 26 | JOIN |
| 27 | calendar_event AS ce ON se.calendar_event_id = ce.calendar_event_id |
| 28 | JOIN |
| 29 | non_repeating_events AS nre ON ce.calendar_event_id = nre.calendar_event_id |
| 30 | WHERE |
| 31 | nre.event_date >= date_trunc('week', CURRENT_DATE) |
| 32 | AND nre.event_date < date_trunc('week', CURRENT_DATE) + INTERVAL '1 week' |
| 33 | AND se.student_id = correct_student_id |
| 34 | |
| 35 | UNION |
| 36 | |
| 37 | SELECT |
| 38 | ce.calendar_event_id, |
| 39 | ce.event_name, |
| 40 | ce.event_type, |
| 41 | ce.event_start_time, |
| 42 | ce.event_end_time, |
| 43 | CASE |
| 44 | WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE |
| 45 | WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE |
| 46 | WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE |
| 47 | WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE |
| 48 | WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE |
| 49 | WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE |
| 50 | WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE |
| 51 | END AS event_date |
| 52 | FROM |
| 53 | student_event AS se |
| 54 | JOIN |
| 55 | calendar_event AS ce ON se.calendar_event_id = ce.calendar_event_id |
| 56 | JOIN |
| 57 | repeating_events AS re ON ce.calendar_event_id = re.calendar_event_id |
| 58 | WHERE |
| 59 | re.starting_date <= ( |
| 60 | CASE |
| 61 | WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE |
| 62 | WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE |
| 63 | WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE |
| 64 | WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE |
| 65 | WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE |
| 66 | WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE |
| 67 | WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE |
| 68 | END |
| 69 | ) |
| 70 | AND re.ending_date >= ( |
| 71 | CASE |
| 72 | WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE |
| 73 | WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE |
| 74 | WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE |
| 75 | WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE |
| 76 | WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE |
| 77 | WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE |
| 78 | WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE |
| 79 | END |
| 80 | ) |
| 81 | AND (EXTRACT(day FROM date_trunc('week', CURRENT_DATE) - date_trunc('week', ( |
| 82 | CASE |
| 83 | WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE |
| 84 | WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE |
| 85 | WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE |
| 86 | WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE |
| 87 | WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE |
| 88 | WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE |
| 89 | WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE |
| 90 | END |
| 91 | ))) % (7 * re.repeats_every_n_weeks)) = 0 |
| 92 | AND se.student_id = correct_student_id |
| 93 | ) AS Result |
| 94 | ORDER BY |
| 95 | event_date, -- Order by day of the week |
| 96 | event_start_time; -- Order by time |
| 97 | |
| 98 | }}} |
| 99 | |