Changes between Version 39 and Version 40 of AdvancedReports

Timestamp:

12/24/22 16:59:50 (2 years ago)

Author:

181006

Comment:

--

AdvancedReports

@@ -6 +6 @@
 {{{#!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
+select
+        distinct u."name", u.surname,
+        case
+                when q1.quarter = 1 then q1.broj_kupeni
+                else 0
+        end as prv_kvartal_kupeni,
+        case
+                when q1.quarter = 2 then q1.broj_kupeni
+                else 0
+        end as vtor_kvartal_kupeni,
+        case
+                when q1.quarter = 3 then q1.broj_kupeni
+                else 0
+        end as tret_kvartal_kupeni,
+        case
+                when q1.quarter = 4 then q1.broj_kupeni
+                else 0
+        end as cetvrt_kvartal_kupeni,
+        -------------------------------------------------------------------------------
+ case
+                when q1.quarter = 1 then q1.suma
+                else 0
+        end as prv_kvartal_suma,
+        case
+                when q1.quarter = 2 then q1.suma
+                else 0
+        end as vtor_kvartal_suma,
+        case
+                when q1.quarter = 3 then q1.suma
+                else 0
+        end as tret_kvartal_suma,
+        case
+                when q1.quarter = 4 then q1.suma
+                else 0
+        end as cetvrt_kvartal_suma
+from
+        cart as c
+join users as u on c.id_user_customer = u.id_user
+join transactions as t on t.id_user_customer = c.id_user_customer
+join
+  (
+        select
+                distinct c2.id_user_customer,
+                extract(QUARTER
+        from
+                t2.created_at) as QUARTER,
+                sum(c2.total) as suma,
+                count(t2.id_invoice) as broj_kupeni
+        from
+                cart as c2
+        join transactions as t2 on t2.id_cart = c2.id_cart
+        join users as u2 on u2.id_user = t2.id_user_customer
+        group by 1, 2
+        ) as q1 
+        on u.id_user = q1.id_user_customer
   }}}
 }}}