create procedure sp_prometPoDatumu
(
@datum date
)
as
select prod.naziv, prodato.prodataKolicina
from prodavnice prod join
(select idProdavnice, sum(promenaKolicine)*(-1) as prodataKolicina
from promene prom
where datumPromene = @datum and promenaKolicine<0
group by idProdavnice) as prodato
on prod.id = prodato.idProdavnice
order by prodataKolicina desc

exec sp_prometPoDatumu '2015-01-20'


create procedure sp_vrednostUProdavnicama
as
select prod.id, prod.naziv, (sum(proiz.cena*st.kolicina)) as vrednost
from prodavnice prod join stanje st
on prod.id = st.idProdavnice
join proizvodi proiz
on st.idProizvoda=proiz.id
group by prod.id, prod.naziv

exec sp_vrednostUProdavnicama


create procedure sp_najviseProizvoda
(
@idProizvoda int
)
as
select top 1 prod.naziv, st.kolicina-promenaProizvoda.mesecnaPromenaKolicine as predjasnjaKolicina
from stanje st join
(select idProdavnice, sum(promenaKolicine) as mesecnaPromenaKolicine
from promene
where datumPromene > dateadd(MONTH, -1, GETDATE()) and idProizvoda = @idProizvoda
group by idProdavnice) as promenaProizvoda
on st.idProdavnice = promenaProizvoda.idProdavnice
join prodavnice prod
on prod.id = st.idProdavnice
where st.idProizvoda = @idProizvoda
order by predjasnjaKolicina desc

exec sp_najviseProizvoda 1000