Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

12/26/25 14:13:10 (5 days ago)

Author:

213140

Comment:

--

AdvancedReports

@@ -1 @@
-== Целосна анализа на сите пациенти - расположение, успешност на консултации и терапија
+== Вкупна годишна анализа од пациенти - расположение, успешност на консултации и терапија
 {{{
 SELECT
-    p.id_user,
-    u.name || ' ' || u.surname AS Patient_Name,
+    EXTRACT(YEAR FROM d.date) AS Year,
+    COUNT(DISTINCT p.id_user) AS Total_Patients,
     COUNT(DISTINCT c.id_consultation) AS Total_Consultations,
     COUNT(DISTINCT th.id_therapy) AS Total_Therapies,
-    ROUND(AVG(d.daily_rating), 2) AS Avg_Mood,
-    MIN(d.daily_rating) AS Lowest_Mood
+    ROUND(AVG(d.daily_rating), 2) AS Avg_Mood_All_Patients,
+    MIN(d.daily_rating) AS Lowest_Mood_Recorded
 FROM project.patient p
-         JOIN project.user u ON u.id_user = p.id_user
-         LEFT JOIN project.consultation c ON c.id_patient = p.id_user
-         LEFT JOIN project.therapy th ON th.id_consultation = c.id_consultation
-         LEFT JOIN project.diary d ON d.id_patient = p.id_user
-GROUP BY p.id_user, u.name, u.surname
-ORDER BY Avg_Mood ASC;
+JOIN project.user u 
+    ON u.id_user = p.id_user
+LEFT JOIN project.consultation c 
+    ON c.id_patient = p.id_user
+LEFT JOIN project.therapy th 
+    ON th.id_consultation = c.id_consultation
+LEFT JOIN project.diary d 
+    ON d.id_patient = p.id_user
+GROUP BY EXTRACT(YEAR FROM d.date)
+ORDER BY Year;
 }}}