Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

12/26/25 15:01:31 (5 days ago)

Author:

213140

Comment:

--

AdvancedReports

@@ -20 +20 @@
 ORDER BY Year;
 }}}
+
+
+== Годишен извештај за честота на користење на консултации и процент на платени консултации заедно со нивната просечна цена 
+
+{{{
+SELECT
+    EXTRACT(YEAR FROM c.date) AS Year,
+    t.id_user,
+    u.name || ' ' || u.surname AS Therapist_Name,
+    COUNT(c.id_consultation) AS Total_Consultations,
+    COUNT(CASE WHEN date_of_payment IS NOT NULL THEN 1 END) AS Completed_Consultations,
+    ROUND(
+            COUNT(CASE WHEN date_of_payment IS NOT NULL THEN 1 END)::DECIMAL
+                / NULLIF(COUNT(c.id_consultation), 0) * 100,
+            2
+    ) AS Completion_Rate_Percentage,
+    ROUND(AVG(c.price), 2) AS Avg_Consultation_Price
+FROM project.therapist t
+         JOIN project.user u
+              ON u.id_user = t.id_user
+         JOIN project.consultation c
+              ON c.id_therapist = t.id_user
+GROUP BY
+    EXTRACT(YEAR FROM c.date),
+    t.id_user,
+    u.name,
+    u.surname
+ORDER BY
+    Year,
+    Completion_Rate_Percentage DESC;
+}}}