Changes between Version 10 and Version 11 of AdvancedReports

Timestamp:

01/12/22 01:58:32 (3 years ago)

Author:

193041

Comment:

--

AdvancedReports

@@ -11 +11 @@
                 from 
                         (
-                                select p.id_predmet as predmet, n.id_nastavnik ,count(u.id_ucenik) as ucenici
+                                select p.id_predmet as predmet, n.id_nastavnik, count(u.id_ucenik) as ucenici
                                 from predmeti p 
                                 join predava_predmet pp on p.id_predmet = pp.id_predmet 
@@ -21 +21 @@
                         as broj_ucenici 
                         group by broj_ucenici.predmet
-                        ) as q1
+                        ) q1
         
                 join (
-                                select p.id_predmet as predmet2 ,n.id_nastavnik ,count(u.id_ucenik) as ucenici2
+                                select p.id_predmet as predmet2, n.id_nastavnik, count(u.id_ucenik) as ucenici2
                                 from predmeti p 
                                 join predava_predmet pp on p.id_predmet = pp.id_predmet 
@@ -30 +30 @@
                                 join predava_na pn on n.id_nastavnik = pn.id_nastavnik 
                                 join ucenici u on u.id_ucenik =pn.id_ucenik 
-                                group by p.id_predmet , n.id_nastavnik 
-                                ) as q2 
+                                group by p.id_predmet, n.id_nastavnik 
+                                ) q2 
                         on q2.predmet2 = q1.predmet and q2.ucenici2 = q1.ucenici
 
-) as q3
+) q3
 join nastavnici n on n.id_nastavnik = q3.id_nastavnik
 join predmeti p on p.id_predmet = q3.predmet
+}}}
+
+== Приказ на предмети со најголем број одржани часови на годишно ниво ==
+{{{#!sql
+select q4.god as godina, p.ime as predmet, q4.broj_casovi as broj_casovi
+from
+(
+
+        select * from 
+        (
+                select q1.godina as god, max(q1.broj_casovi) as maksimum from
+                (
+                select extract (year from c.vreme_pocetok) as godina, p.id_predmet as predmet, count(c.id_cas) broj_casovi
+                from predmeti p
+                join casovi c on c.id_predmet = p.id_predmet 
+                group by godina,predmet
+                ) q1
+                group by godina
+        ) q2
+        join 
+        (
+        select extract (year from c.vreme_pocetok) as godina, p.id_predmet as predmet, count(c.id_cas) broj_casovi
+                from predmeti p
+                join casovi c on c.id_predmet = p.id_predmet 
+                group by godina,predmet
+        ) q3
+        on q2.god=q3.godina and q2.maksimum=q3.broj_casovi
+
+) q4
+join predmeti p on p.id_predmet = q4.predmet
+order by godina desc;
 }}}