Changes between Version 6 and Version 7 of AdvancedReports

Timestamp:

12/28/22 01:00:56 (17 months ago)

Author:

201088

Comment:

--

AdvancedReports

@@ -57 +57 @@
 (text here)
 {{{#!sql
---sql here
+select q2.god as godina, q2.maximum as max, 
+        case 
+        when q3.species = 0 then 'cat'
+        when q3.species = 1 then 'dog'
+        when q3.species = 2 then 'bird'
+        end as species
+        
+from
+        (       select q1.godina as god, max(broj_posvojuvanja) as maximum from
+                (
+                --posvojuvanja po godini i tipovi na milenichinja
+                select extract(year from a.start_date) as godina, p2.species, count(p2.id_pet) as broj_posvojuvanja
+                from adoption a 
+                join pet p2 on a.id_adoption = p2.id_adoption 
+                where (end_date_foster is null) and (a.approved is true)
+                group by godina, p2.species
+                --order by godina
+                ) q1
+                group by godina
+        )q2
+        join
+        (
+        select extract(year from a.start_date) as godina, p2.species, count(p2.id_pet) as broj_posvojuvanja
+                from adoption a
+                join pet p2 on a.id_adoption = p2.id_adoption 
+                where (end_date_foster is null) and (a.approved is true)
+                group by godina, p2.species
+                --order by godina
+        ) q3
+on q2.god = q3.godina and q2.maximum = q3.broj_posvojuvanja
 }}}