Changes between Version 13 and Version 14 of WikiStart/AdvancedReports

Timestamp:

01/29/24 16:23:34 (12 months ago)

Author:

121232

Comment:

--

WikiStart/AdvancedReports

@@ -91 +91 @@
 FROM EventResult
 LEFT JOIN task ON EventResult.event_date = task.task_date;
-
 }}}
 
@@ -126 +125 @@
 
 == **[[span(style=color: #BF1B1B, 3. Листање(приказ) на испити за наредните седум дена:)]]**
-{{{
-SELECT se.*, ce.*
-FROM subject_event se
-JOIN calendar_event ce ON se.calendar_event_id = ce.calendar_event_id
-WHERE ce.event_type = 'Exam'
-  AND ce.event_start_time >= CURRENT_TIMESTAMP
-  AND ce.event_start_time <= CURRENT_TIMESTAMP + INTERVAL '7 days';
-
-}}}
+
+{{{
+DECLARE correct_student_id INT;
+SET correct_student_id = 11111;
+
+SELECT 
+    ce.event_name, 
+    sat.subject_name,
+    ce.event_start_time,
+    ce.event_end_time
+FROM 
+    subject_event AS se
+JOIN 
+    calendar_event AS ce ON se.calendar_event_id = ce.calendar_event_id
+JOIN 
+    subject_at_term AS sat ON se.term_subject_id = sat.term_subject_id
+        AND se.term_type = sat.term_type 
+        AND se.starting_year = sat.starting_year
+        AND se.finishing_year = sat.finishing_year
+        AND se.student_id = sat.student_id
+JOIN 
+    non_repeating_event AS nre ON ce.calendar_event_id = nre.calendar_event_id -- Corrected table name
+WHERE 
+    ce.event_type = 'Exam'
+    AND se.student_id = correct_student_id
+    AND nre.event_date >= CURRENT_DATE
+    AND nre.event_date <= CURRENT_DATE + INTERVAL '7 days'
+ORDER BY
+    nre.event_date,
+    ce.event_start_time;
+}}}
+
 == **[[span(style=color: #BF1B1B, 4. Листање(приказ) на сите евенти за одредена недела:)]]**
 
@@ -223 +245 @@
   event_date, -- Order by day of the week
   event_start_time; -- Order by time
-
 }}}
 
@@ -331 +352 @@
 FROM 
     SubjectBooks AS sb;
-
 }}}