Changes between Version 9 and Version 10 of AdvancedReports

Timestamp:

12/20/22 14:14:59 (3 years ago)

Author:

191285

Comment:

--

AdvancedReports

@@ -33 +33 @@
 {{{#!div style="font-size: 90%"
   {{{#!sql 
-
+select distinct u.user_name,u.user_surname,
+    case when tabela.quarter=1 then tabela.broj_kupeni else 0 end as prv_kvartal_kupeni,
+        case when tabela.quarter=2 then tabela.broj_kupeni  else 0 end as vtor_kvartal_kupeni,
+    case when tabela.quarter=3 then tabela.broj_kupeni  else 0 end as tret_kvartal_kupeni,
+    case when tabela.quarter=4 then tabela.broj_kupeni else 0 end as cetvrt_kvartal_kupeni,
+    -------------------------------------------------------------------------------
+    case when tabela.quarter=1 then tabela.suma else 0 end as prv_kvartal_suma,
+    case when tabela.quarter=2 then tabela.suma  else 0 end as vtor_kvartal_suma,
+    case when tabela.quarter=3 then tabela.suma  else 0 end as tret_kvartal_suma,
+    case when tabela.quarter=4 then tabela.suma else 0 end as cetvrt_kvartal_suma
+    
+    
+        from reservation as r
+        join client as c on c.user_id = r.user_id
+        join movieprojection as mp on mp.projection_id = r.projection_id 
+        join users as u on u.user_id = c.user_id
+        join(
+        select distinct c2.user_id,extract(quarter from r2.reservation_date) as quarter,sum(mp.projection_price) as suma,
+        count(r2.reservation_id) as broj_kupeni
+        from reservation as r2
+        join movieprojection as mp on mp.projection_id  = r2.projection_id
+        join client as c2 on c2.user_id = r2.user_id
+        group by 1,2
+        ) as tabela on c.user_id = tabela.user_id
   }}}
 }}}