Changes between Version 9 and Version 10 of AdvancedReports

Timestamp:

01/19/22 23:34:38 (2 years ago)

Author:

195115

Comment:

--

AdvancedReports

@@ -67 +67 @@
 
 
-== Клиентот кој има направено најголема потрошувачка во последната година според дадена проекција
+== Извештаи за клиент според тромесечие, купени карти и придонес
 
 {{{#!sql
-  select c.user_id,sum(mp.projection_price) as vkupna_potroshuvacka,u.user_name,u.user_surname from users as u
-     join client as c on c.user_id = u.user_id
-     join reservation as r on r.user_id = c.user_id
-     and r.reservation_date between now() - interval '1 year' and now()
-     join movieprojection as mp on mp.projection_id = r.projection_id
-     where mp.projection_id = 12732  --?--
-     group by c.user_id,u.user_name,u.user_surname
-     order by vkupna_potroshuvacka desc
-     limit 1;
+   select distinct u.user_name,u.user_surname,
+    case when tabela.quarter=1 then tabela.broj_kupeni else 0 end as prv_kvartal,
+        case when tabela.quarter=2 then tabela.broj_kupeni  else 0 end as vtor_kvartal,
+    case when tabela.quarter=3 then tabela.broj_kupeni  else 0 end as tret_kvartal,
+    case when tabela.quarter=4 then tabela.broj_kupeni else 0 end as cetvrt_kvartal,
+    -------------------------------------------------------------------------------
+    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
 }}}