Changes between Version 8 and Version 9 of AdvancedReports

Timestamp:

01/19/22 14:21:46 (3 years ago)

Author:

195115

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Напредни извештаи од базата (SQL и складирани процедури)
 
-== 
+== Извештаи за заработка на секоја фирма и генерален интерес за секое тромесечие
 
-
+{{{#!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
+       
+       
+       
+    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
+    
+    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 
+    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
+    where ((extract(year from now())::integer-1)=(extract(year from r.reservation_date)::integer))
+}}}
 
 ==