Changes between Version 19 and Version 20 of AdvancedReports

Timestamp:

01/26/22 03:59:01 (4 years ago)

Author:

195115

Comment:

--

AdvancedReports

@@ -4 +4 @@
 
 {{{#!sql
-select distinct f.firm_name,
-    case when tabelaQuarter.quarter=1 then tabelaSuma.count else 0 end as prv_kvartal_suma,
-    case when tabelaQuarter.quarter=2 then tabelaSuma.count  else 0 end as vtor_kvartal,
-    case when tabelaQuarter.quarter=3 then tabelaSuma.count  else 0 end as tret_kvartal,
-    case when tabelaQuarter.quarter=4 then tabelaSuma.count  else 0 end as cetvrt_kvartal,
-       
-    case when tabelaQuarter.quarter=1 then tabelaSuma.sum else 0 end as prv_kvartal_suma,
-    case when tabelaQuarter.quarter=2 then tabelaSuma.sum  else 0 end as vtor_kvartal_suma,
-    case when tabelaQuarter.quarter=3 then tabelaSuma.sum  else 0 end as tret_kvartal_suma,
-    case when tabelaQuarter.quarter=4 then tabelaSuma.sum  else 0 end as cetvrt_kvartal_suma
-       
-       
+select distinct(f.firm_id) as firma,
+    case when tabelaQuarter.quarter=1 then tabelaSuma.broj_kupeni else 0 end as prv_kvartal_kupeni,
+    case when tabelaQuarter.quarter=2 then tabelaSuma.broj_kupeni  else 0 end as vtor_kvartal_kupeni,
+    case when tabelaQuarter.quarter=3 then tabelaSuma.broj_kupeni  else 0 end as tret_kvartal_kupeni,
+    case when tabelaQuarter.quarter=4 then tabelaSuma.broj_kupeni  else 0 end as cetvrt_kvartal_kupeni,
+      
+    (case when tabelaQuarter.quarter=1 then tabelaSuma.sum else 0 end) as prv_kvartal_suma,
+    (case when tabelaQuarter.quarter=2 then tabelaSuma.sum  else 0 end) as vtor_kvartal_suma,
+    (case when tabelaQuarter.quarter=3 then tabelaSuma.sum  else 0 end) as tret_kvartal_suma,
+    (case when tabelaQuarter.quarter=4 then tabelaSuma.sum  else 0 end) as cetvrt_kvartal_suma
        
     from firm as f
-    join auditorium as a on a.firm_id = f.firm_id
+        join auditorium as a on a.firm_id = f.firm_id
     join gives as g on g.auditorium_id = a.auditorium_id
     join movieprojection as mp on mp.projection_id = g.projection_id
     join reservation as r on r.projection_id = mp.projection_id
+    join(
+    select extract(quarter from reservation_date) as quarter
+    from reservation
+    ) as tabelaQuarter on tabelaQuarter.quarter is not null
     
     join(
-    select distinct f.firm_name, extract(quarter from r.reservation_date) as quarter
-    from firm as f
-    join auditorium as a on a.firm_id = f.firm_id
-    join gives as g on g.auditorium_id = a.auditorium_id
-    join movieprojection as mp on mp.projection_id = g.projection_id
-    join reservation as r on r.projection_id = mp.projection_id
-   
-    ) as tabelaQuarter on tabelaQuarter.firm_name = f.firm_name
-    
-    join(
-    select distinct f.firm_id,f.firm_name , a.auditorium_name as sala,sum(mp.projection_price) ,count(distinct r.reservation_id ),mp.projection_price 
+    select distinct(f2.firm_id) as firma, count(distinct r.reservation_id) as broj_kupeni,sum(mp.projection_price) as sum
     from reservation as r
-    join movieprojection as mp on mp.projection_id = r.projection_id
-    join gives as g on g.projection_id = mp.projection_id
-    join auditorium as a on a.auditorium_id = g.auditorium_id 
-    join firm as f on f.firm_id = a.firm_id 
-    group by 1,2,3,6
-    order by sum desc
-    ) as tabelaSuma on tabelaSuma.firm_id = f.firm_id
+    left join movieprojection as mp on mp.projection_id = r.projection_id
+    left join gives as g on g.projection_id = mp.projection_id
+    left join auditorium as a on a.auditorium_id = g.auditorium_id 
+    left join firm as f2 on f2.firm_id = a.firm_id
+    group by f2.firm_id
+    ) as tabelaSuma on tabelaSuma.firma = f.firm_id
     where ((extract(year from now())::integer-1)=(extract(year from r.reservation_date)::integer))
+    group by f.firm_id,tabelaQuarter.quarter,tabelaSuma.broj_kupeni,tabelaSuma.sum
+    order by f.firm_id
 }}}