Changes between Version 16 and Version 17 of AdvancedReports

Timestamp:

01/22/22 10:06:39 (3 years ago)

Author:

196021

Comment:

--

AdvancedReports

@@ -143 +143 @@
 
 
-== 
+== Извештај за секоја сала,за секоја година со најмногу одржани филмски проекции и вкупен број на резервации за таа сала 
 
+{{{#!sql
+select distinct a.auditorium_name , q4.god as godina,a.auditorium_capacity,vkupna_brojka.vkupno,q4.broj_odrzani_filmovi as broj_odrzani_filmovi
+ from 
+ (
+      select * from 
+      (
+          select q1.godina as god , max(q1.broj_odrzani_filmovi) as maksimum from 
+          ( 
+             select extract(year from r.reservation_date)as godina ,r.reservation_id as rez ,count(r.projection_id) as broj_odrzani_filmovi
+             from reservation as r
+             join seat as s2 on r.seat_id=s2.seat_id
+             join auditorium as a2 on s2.auditorium_id=a2.auditorium_id
+             join movieprojection as mp1 on r.projection_id=mp1.projection_id
+             group by 1,2
+          )q1
+          group by godina
+        
+      )q2
+      join 
+      (
+      select extract (year from r.reservation_date)as godina , r.reservation_id as rez,count(r.projection_id) as broj_odrzani_filmovi
+      from reservation as r
+             join seat as s3 on r.seat_id=s3.seat_id
+             join auditorium as a3 on s3.auditorium_id=a3.auditorium_id
+             join movieprojection as mp2 on r.projection_id=mp2.projection_id
+             group by 1,2
+      )q3
+       on q2.god=q3.godina and q2.maksimum=q3.broj_odrzani_filmovi
+ 
+ ) q4
 
+ join( select distinct r3.reservation_id, count(r3.projection_id) as vkupno
+         from reservation as r3
+          join movieprojection as mp5 on r3.projection_id=mp5.projection_id 
+          join movie as m4 on mp5.movie_id=m4.movie_id
+          group by 1
+ )as vkupna_brojka on vkupna_brojka.vkupno is not null
 
-== 
+ join auditorium as a on a.auditorium_id=q4.rez
+ order by godina desc 
+}}}
+'''//Преку овој извештај вработените може да проверат која
+сала е најмногу оптеретена и да направат распределба на проекциите во однос на капацитетот на седишта што го нуди салата//'''