Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

01/19/22 18:46:48 (2 years ago)

Author:

193230

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (SQL и складирани процедури)
+
+= Извештај за секоја продавница, бројот на производи во неа, вкупната вредност на производите, просечна цена по производ и вкупно направени нарачки во последните 3 месеци
+
+{{{#!sql
+select os.names, os.store_id,
+coalesce((
+select count(pis.product_in_store_id)
+        from online_stores as os
+        join product_in_store as pis on os.store_id=pis.store_id
+        ),0) as vkupno_proizvodi,
+(
+select sum(p.price)
+        from online_stores as os
+        join product_in_store as pis on os.store_id=pis.store_id
+        join price as p on pis.product_in_store_id=p.product_in_store_id
+        
+) as vkupna_vrednost,
+(
+select avg(p.price)
+
+
+        from online_stores as os
+        join product_in_store as pis on os.store_id=pis.store_id
+        join price as p on pis.product_in_store_id=p.product_in_store_id
+        
+) as prosek_vrednost,
+(
+select count(sb.shopping_id)
+        from online_stores as os 
+        join product_in_store as pis on os.store_id=pis.product_id
+        join orders as ord on pis.product_in_store_id=ord.product_in_store_id
+        join shopping_bag as sb on ord.shopping_id=sb.shopping_id
+        where sb.order_date between now() - interval '3 months' and now()
+) as vkupno_naracki
+
+        
+from online_stores as os
+group by 1, 2
+
+        }}}
+
+== Вработен кој извршил достава на најмногу нарачки
+{{{#!sql
+create view vkupen_broj_naracki as 
+
+select se.shipping_employee_id, u.user_name, u.surname, count(s.shipping_id) as broj_naracki
+from shipping_employee as se
+join users as u on se.user_id=u.user_id
+join shipping as s on se.user_id=s.user_id
+join shopping_bag as sb on s.shopping_id=sb.shopping_id
+
+group by 1, 2, 3;
+
+select vbn.shipping_employee_id, vbn.user_name, vbn.surname, vbn.broj_naracki as najmnogu_naracki
+from vkupen_broj_naracki as vbn
+where vbn.broj_naracki = (select max(broj_naracki) from vkupen_broj_naracki);
+}}}