Changes between Version 17 and Version 18 of WikiStart/AdvancedReports

Timestamp:

02/05/24 16:43:43 (21 months ago)

Author:

133039

Comment:

--

WikiStart/AdvancedReports

@@ -421 +421 @@
 }}}
 
-
-
-
+== **[[span(style=color: #BF1B1B, 9:)]]**
+
+{{{
+WITH counted_events AS (
+  select 
+    s.student_id,
+    COUNT(distinct(se.calendar_event_id)) AS student_events,
+    COUNT(distinct(sje.calendar_event_id)) as subject_events
+  FROM
+    student s
+  LEFT join
+    student_event se ON s.student_id = se.student_id
+  left join 
+        subject_at_term sat on sat.student_id = s.student_id 
+  LEFT JOIN
+    subject_event sje ON sat.student_id = sje.student_id
+  LEFT JOIN
+    calendar_event ce ON se.calendar_event_id = ce.calendar_event_id or sje.calendar_event_id = ce.calendar_event_id 
+  LEFT JOIN
+    repeating_event re ON ce.calendar_event_id = re.calendar_event_id
+      AND EXTRACT(YEAR FROM CURRENT_DATE) >= EXTRACT(YEAR FROM re.starting_date)
+      AND EXTRACT(YEAR FROM CURRENT_DATE) <= EXTRACT(YEAR FROM re.ending_date)
+  LEFT JOIN
+    non_repeating_event nre ON ce.calendar_event_id = nre.calendar_event_id
+      AND EXTRACT(YEAR FROM nre.event_date) = EXTRACT(YEAR FROM CURRENT_DATE)
+  GROUP BY
+    s.student_id
+)
+
+SELECT 
+  AVG(student_events) AS average_student_events, 
+  AVG(subject_events) AS average_subject_events
+        FROM 
+        counted_events
+
+
+}}}
+