Changes between Version 4 and Version 5 of AdvancedReports

Timestamp:

12/20/22 13:45:11 (21 months ago)

Author:

201045

Comment:

--

AdvancedReports

@@ -122 +122 @@
 }}}
 
+== Да се најдат храните кои се продаваат најмногу за дадени три календарски месеци во годината ==
+{{{#!sql
+create function najprodavano_za_dadeni_meseci(month_1 int, month_2 int, month_3 int)
+returns table(
+        id_stavka int,
+        ime_hra varchar(200),
+        id_mesto int,
+        ime_stavka varchar(200),
+        vkupno_prodadeni bigint
+)
+language plpgsql
+as $$
+begin   
+        return query select h.id_stavka, h.ime_hra,pr.id_mesto, pr.ime as ime_stavka, sum(nsh.kolicina) as vkupno_prodadeni
+        from naracka n join naracka_sodrzi_hrana nsh on n.naracka_id = nsh.naracka_id 
+        join hrana h on nsh.id_stavka = h.id_stavka 
+        join prodazhnomesto pr on pr.id_mesto = n.id_mesto 
+        where (lower(n.status) like '%zavrsena%' or lower(n.status) like '%завршена%')
+        and (extract (month from n.datum_naracka) in (month_1,month_2,month_3) and extract(year from now()) = extract (year from n.datum_naracka) )
+        group by h.id_stavka, pr.id_mesto,h.ime_hra, pr.ime 
+        having sum(nsh.kolicina) = 
+ (
+                select  sum(nshh.kolicina)
+                from naracka nn join naracka_sodrzi_hrana nshh on nn.naracka_id = nshh.naracka_id 
+                join hrana hh on nshh.id_stavka = hh.id_stavka 
+                join prodazhnomesto prr on prr.id_mesto = nn.id_mesto 
+                where (lower(nn.status) like '%zavrsena%' or lower(nn.status) like '%завршена%')
+                and (extract (month from nn.datum_naracka) in (month_1,month_2,month_3) and extract(year from now()) = extract (year from nn.datum_naracka) )
+                group by hh.id_stavka, prr.id_mesto,hh.ime_hra, prr.ime 
+                having prr.id_mesto = pr.id_mesto
+                order by sum(nshh.kolicina) desc 
+                limit 1
+);
+end
+$$
+}}}