Changes between Version 3 and Version 4 of AdvancedReports

Timestamp:

12/30/22 04:29:50 (23 months ago)

Author:

181213

Comment:

--

AdvancedReports

@@ -1 +1 @@
 == Напредни извештаи од базата (SQL и складирани процедури)
 
-- Ресторан со најмногу нарачки за последниот Х период?
+- Ресторан со најмногу нарачки во последните 3 месеци
 {{{#!sql
-SELECT q1.restoran_ime 
+SELECT q1.restoran_ime as restoran, q1.naracki as broj_na_naracki 
 FROM(
 SELECT r.restoran_ime, COUNT(n.naracka_id) as naracki
@@ -10 +10 @@
 JOIN obrok as o on m.meni_id=o.meni_id 
 JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id 
-JOIN naracka as n on sso.naracka_id=n.naracka_id 
+JOIN naracka as n on sso.naracka_id=n.naracka_id
+where n.naracana_na between (now()-interval '3 months') and now()
 GROUP BY r.restoran_ime) q1
 WHERE q1.naracki=(SELECT MAX(naracki)FROM (
-SELECT r.restoran_ime, COUNT(n.naracka_id) as naracki
+SELECT r.restoran_ime, COUNT (n.naracka_id) as naracki
 FROM restoran as r
 JOIN meni as m on r.restoran_id=m.restoran_id 
 JOIN obrok as o on m.meni_id=o.meni_id 
 JOIN se_sostoi_od as sso on o.obrok_id=sso.obrok_id 
-JOIN naracka as n on sso.naracka_id=n.naracka_id 
-GROUP BY r.restoran_ime) q1
+JOIN naracka as n on sso.naracka_id=n.naracka_id
+where n.naracana_na between (now()-interval '3 months') and now()
+GROUP BY r.restoran_ime) q2
 )
 }}}
-- Најголем профит?
+- Ресторан со најголем профит
+{{{#!sql
+select rr.restoran_ime as restoran, q1.cena_vkupna as profit from restoran rr join(select r.restoran_id,sum(sso.cena * sso.kolicina) as cena_vkupna 
+from restoran r
+join meni as m on r.restoran_id=m.restoran_id 
+join obrok as o on m.meni_id=o.meni_id 
+join se_sostoi_od as sso on o.obrok_id=sso.obrok_id 
+join naracka as n on sso.naracka_id = n.naracka_id
+group by r.restoran_id)q1
+on q1.restoran_id=rr.restoran_id
+where cena_vkupna=(select max(cena_vkupna) from(
+select r.restoran_id,sum(sso.cena * sso.kolicina) as cena_vkupna 
+from restoran r
+join meni as m on r.restoran_id=m.restoran_id 
+join obrok as o on m.meni_id=o.meni_id 
+join se_sostoi_od as sso on o.obrok_id=sso.obrok_id 
+join naracka as n on sso.naracka_id = n.naracka_id
+group by r.restoran_id
+)q2)
+}}}
 - Најнарачан производ по ресторан?