Changes between Version 1 and Version 2 of Advancedreports

Timestamp:

01/27/23 15:55:37 (3 years ago)

Author:

201134

Comment:

--

Advancedreports

@@ -98 +98 @@
 order by 2 asc
 }}}
+
+=== Извештај за број на настани од одреден тип за секоја кетеринг фирма,просечен број на келнери што служеле,број на локации кај што биле одржувани и вкупен промет од тој тип настани за таа кетеринг фирма ===
+
+{{{#!sql
+
+select ko.ime as ime_keterinzi, n.tip as tip_nastan, count(*) as broj_nastani_od_toj_tip, 
+round(avg(krn.kolicina)) as prosecno_kelneri, count(n.adresa) as broj_lokacii,
+vkupen_promet_od_toj_tip_nastani
+from nastani as n
+
+join ketering_rezervira_nastan krn on n.nastan_id = krn.nastan_id 
+join keterinzi as k on krn.korisnik_id = k.korisnik_id 
+join korisnici as ko on k.korisnik_id = ko.korisnik_id 
+join(
+    select ko.ime as ime_keterinzi, n.tip as tip_nastan, 
+        sum(k.cena) as vkupen_promet_od_toj_tip_nastani from nastani as n
+        join ketering_rezervira_nastan krn on n.nastan_id = krn.nastan_id 
+        join keterinzi as k on krn.korisnik_id = k.korisnik_id 
+        join korisnici as ko on k.korisnik_id = ko.korisnik_id 
+        group by 1 ,2
+        order by ime_keterinzi
+) as promet_ketering on ko.ime = promet_ketering.ime_keterinzi and n.tip = promet_ketering.tip_nastan
+
+group by 1, 2, 6
+order by ime_keterinzi
+}}}
+
+=== Извештај за приходи за секој квартал од годината ===
+
+{{{#!sql
+select
+(
+        select sum(ke.cena + b.cena + f.cena) as vkupna_cena_nastan
+        
+        from nastani as n
+        
+        join bend_sviri_nastan bsn on n.nastan_id = bsn.nastan_id 
+        join bendovi as b on bsn.korisnik_id = b.korisnik_id 
+        join ketering_rezervira_nastan krn on n.nastan_id = krn.nastan_id 
+        join fotograf_slika_nastan fsn on n.nastan_id = fsn.nastan_id 
+        join fotografi as f on fsn.korisnik_id = f.korisnik_id 
+        join keterinzi as ke on krn.korisnik_id = ke.korisnik_id
+
+        where extract(month from n.datum) between 1 and 3
+
+) as prihod_prv_kvartal,
+(
+        select sum(ke.cena + b.cena + f.cena) as vkupna_cena_nastan from nastani as n
+        
+        join bend_sviri_nastan bsn on n.nastan_id = bsn.nastan_id 
+        join bendovi as b on bsn.korisnik_id = b.korisnik_id 
+        join ketering_rezervira_nastan krn on n.nastan_id = krn.nastan_id 
+        join fotograf_slika_nastan fsn on n.nastan_id = fsn.nastan_id 
+        join fotografi as f on fsn.korisnik_id = f.korisnik_id 
+        join keterinzi as ke on krn.korisnik_id = ke.korisnik_id
+        
+        where extract(month from n.datum) between 4 and 6
+        
+) as prihod_vtor_kvartal,
+(
+        select sum(ke.cena + b.cena + f.cena) as vkupna_cena_nastan from nastani as n
+        
+        join bend_sviri_nastan bsn on n.nastan_id = bsn.nastan_id 
+        join bendovi as b on bsn.korisnik_id = b.korisnik_id 
+        join ketering_rezervira_nastan krn on n.nastan_id = krn.nastan_id 
+        join fotograf_slika_nastan fsn on n.nastan_id = fsn.nastan_id 
+        join fotografi as f on fsn.korisnik_id = f.korisnik_id 
+        join keterinzi as ke on krn.korisnik_id = ke.korisnik_id
+        
+        where extract(month from n.datum) between 7 and 9
+        
+) as prihod_tret_kvartal, 
+(
+        select sum(ke.cena + b.cena + f.cena) as vkupna_cena_nastan from nastani as n
+
+        join bend_sviri_nastan bsn on n.nastan_id = bsn.nastan_id 
+        join bendovi as b on bsn.korisnik_id = b.korisnik_id 
+        join ketering_rezervira_nastan krn on n.nastan_id = krn.nastan_id 
+        join fotograf_slika_nastan fsn on n.nastan_id = fsn.nastan_id 
+        join fotografi as f on fsn.korisnik_id = f.korisnik_id 
+        join keterinzi as ke on krn.korisnik_id = ke.korisnik_id
+        
+        where extract(month from n.datum) between 10 and 12
+        
+) as prihod_cetvrt_kvartal
+}}}
+