Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

02/27/24 17:17:32 (3 months ago)

Author:

183101

Comment:

--

AdvancedReports

@@ -1 @@
-a
+= Напредни извештаи од базата (SQL и складирани процедури) =
+
+=== Извештај за просечен процент на завршеност по проект и категорија:  ===
+{{{#!sql
+SELECT
+    p.name AS project_name,
+    c.name AS category_name,
+    AVG(t.completion_percentage) AS average_completion
+FROM
+    Task t
+JOIN Project p ON t.project_id = p.project_id
+JOIN Category c ON t.task_id = c.task_id
+GROUP BY
+    p.name,
+    c.name
+ORDER BY
+    p.name,
+    c.name
+}}}
+
+=== Извештај за бројот на задачи и просечен процент на завршеност по корисник и месец:===
+{{{#!sql
+SELECT
+    u.email AS user_email,
+    strftime('%Y', r.date_and_time) AS year,
+    strftime('%m', r.date_and_time) AS month,
+    COUNT(t.task_id) AS number_of_tasks,
+    AVG(t.completion_percentage) AS average_completion
+FROM
+    User u
+JOIN Task t ON u.email = t.user_email
+JOIN Reminder r ON t.task_id = r.task_id
+GROUP BY
+    u.email,
+    strftime('%Y', r.date_and_time),
+    strftime('%m', r.date_and_time) 
+ORDER BY
+    u.email,
+    year,
+    month
+}}}