Changes between Version 26 and Version 27 of AdvancedReports

Timestamp:

01/11/23 01:47:45 (3 years ago)

Author:

201088

Comment:

--

AdvancedReports

@@ -197 +197 @@
 
 == Извештај 6
-Успешност на верификатори - број на верифицирани посвојувачи и вкупен број на посвојувања направени од посвојувачи за секој вработен-верификатор
-{{{#!sql
-select 
-        e.id_user,
-        count(distinct q1.id_user) as verified_adopters,
-        sum(q1.number_of_adoptions) as number_of_adoptions_under_employee
-from
+Извештаи за прифатилишта - број на вработени, број на верифицирани посвојувачи од страна на вработени во тоа прифатилиште и вкупен број на посвојувања направени од посвојувачи кои биле верифицирани од страна на вработени во тоа прифатилиште
+{{{#!sql
+select 
+        s.name_shelter,
+        q3.number_of_employees,
+        q2.verified_adopters,
+        q2.number_of_adoptions_under_employee
+from shelter s 
+join
         (
         select 
-                        a.id_user,
-                        a.verified_by_employee,
-                        count(distinct a2.id_adoption) as number_of_adoptions
-        from adopter a 
-        join adoption a2 on a.id_user = a2.id_adopter 
-        group by a.id_user
-        ) q1
-join employee e on e.id_user = q1.verified_by_employee
-group by e.id_user
-order by e.id_user asc
+                e.id_user,
+                e.id_shelter,
+                count(distinct q1.id_user) as verified_adopters,
+                sum(q1.number_of_adoptions) as number_of_adoptions_under_employee
+        from
+                (
+                select 
+                                a.id_user,
+                                a.verified_by_employee,
+                                count(distinct a2.id_adoption) as number_of_adoptions
+                from adopter a 
+                join adoption a2 on a.id_user = a2.id_adopter 
+                group by a.id_user
+                ) q1
+        join employee e on e.id_user = q1.verified_by_employee
+        group by e.id_user
+        order by e.id_user asc
+        ) q2 on s.id_shelter = q2.id_shelter
+join (
+                select
+                        s.id_shelter,
+                        count(distinct e2.id_user) as number_of_employees
+                from shelter s 
+                join employee e2 on e2.id_shelter = s.id_shelter 
+                group by s.id_shelter 
+        ) q3 on q3.id_shelter = s.id_shelter 
+group by s.id_shelter, q2.id_shelter, q3.number_of_employees,q2.verified_adopters, q2.number_of_adoptions_under_employee
+order by s.id_shelter
 }}}