| 124 | == Да се најдат храните кои се продаваат најмногу за дадени три календарски месеци во годината == |
| 125 | {{{#!sql |
| 126 | create function najprodavano_za_dadeni_meseci(month_1 int, month_2 int, month_3 int) |
| 127 | returns table( |
| 128 | id_stavka int, |
| 129 | ime_hra varchar(200), |
| 130 | id_mesto int, |
| 131 | ime_stavka varchar(200), |
| 132 | vkupno_prodadeni bigint |
| 133 | ) |
| 134 | language plpgsql |
| 135 | as $$ |
| 136 | begin |
| 137 | return query select h.id_stavka, h.ime_hra,pr.id_mesto, pr.ime as ime_stavka, sum(nsh.kolicina) as vkupno_prodadeni |
| 138 | from naracka n join naracka_sodrzi_hrana nsh on n.naracka_id = nsh.naracka_id |
| 139 | join hrana h on nsh.id_stavka = h.id_stavka |
| 140 | join prodazhnomesto pr on pr.id_mesto = n.id_mesto |
| 141 | where (lower(n.status) like '%zavrsena%' or lower(n.status) like '%завршена%') |
| 142 | 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) ) |
| 143 | group by h.id_stavka, pr.id_mesto,h.ime_hra, pr.ime |
| 144 | having sum(nsh.kolicina) = |
| 145 | ( |
| 146 | select sum(nshh.kolicina) |
| 147 | from naracka nn join naracka_sodrzi_hrana nshh on nn.naracka_id = nshh.naracka_id |
| 148 | join hrana hh on nshh.id_stavka = hh.id_stavka |
| 149 | join prodazhnomesto prr on prr.id_mesto = nn.id_mesto |
| 150 | where (lower(nn.status) like '%zavrsena%' or lower(nn.status) like '%завршена%') |
| 151 | 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) ) |
| 152 | group by hh.id_stavka, prr.id_mesto,hh.ime_hra, prr.ime |
| 153 | having prr.id_mesto = pr.id_mesto |
| 154 | order by sum(nshh.kolicina) desc |
| 155 | limit 1 |
| 156 | ); |
| 157 | end |
| 158 | $$ |
| 159 | }}} |