Changes between Version 27 and Version 28 of AdvancedReports

Timestamp:

04/09/24 14:47:54 (9 months ago)

Author:

184006

Comment:

--

AdvancedReports

@@ -27 +27 @@
 == Прикажување на вработен (доктор) кој што има напишено најмногу извештаи за едно исто милениче.
 {{{
-WITH DoctorReportsCount AS (
+WITH DoctorPetReportsCount AS (
     SELECT
         u.id AS doctor_id,
@@ -39 +39 @@
     GROUP BY
         u.id, u.name, r.petsID
-    ORDER BY
-        reports_count DESC
-    LIMIT 1
+),
+
+MaxReportsPerPet AS (
+    SELECT
+        pet_id,
+        MAX(reports_count) AS max_reports_count
+    FROM
+        DoctorPetReportsCount
+    GROUP BY
+        pet_id
 )
 
 SELECT
-    drc.doctor_id,
-    drc.doctor_name,
-    drc.pet_id,
-    drc.reports_count
-FROM
-    DoctorReportsCount drc;
+    dprc.doctor_id,
+    dprc.doctor_name,
+    dprc.pet_id,
+    dprc.reports_count
+FROM
+    DoctorPetReportsCount dprc
+JOIN
+    MaxReportsPerPet mrpp ON dprc.pet_id = mrpp.pet_id AND dprc.reports_count = mrpp.max_reports_count
+JOIN
+    project.users u ON dprc.doctor_id = u.id
+WHERE
+    dprc.pet_id = (SELECT pet_id FROM MaxReportsPerPet ORDER BY max_reports_count DESC LIMIT 1);
 }}}
 == За секое прашање кое е поставено во Blog Post For Consultations колку одговори има, од кои корисници се дадени одговорите и каква е улогата на корисниците кои одговориле(admin, manager, employee, standard).