create procedure sp_box_office
as
begin
	select nazivFilma, nedeljniIznos, ukupanIznos from
		(select top 10 f.id as idFilma, f.naziv as nazivFilma, sum(z.iznos) as nedeljniIznos
		from filmovi f join zarada z
		on f.id=z.idFilma
		where z.dan between DATEADD(DAY,-7,GETDATE()) and GETDATE()  
		group by f.id, f.naziv
		) as zaradaProslaNedelja
	join
		(select f.id as idFilma, sum(z.iznos) as ukupanIznos
		from filmovi f join zarada z 
		on f.id=z.idFilma
		group by f.id, f.naziv
		) as zaradaUkupna
	on zaradaProslaNedelja.idFilma=zaradaUkupna.idFilma
	order by nedeljniIznos desc
end
go

exec sp_box_office
go

-------------------------------------------------------------------

create procedure sp_unosZarade
(
	@id int, @dan date, @idFilma int, @iznos decimal(10,2)
)
as
begin
	insert into zarada
	values(@id,@dan,@idFilma,@iznos)
end
go

exec sp_unosZarade 300000,'2015-12-28',10000,10000000
go
------------------------------------------------------

create procedure sp_filmografija
(
	@osoba int
)
as
begin
	select a.svojstvo, f.naziv, f.godina
	from osobe o join angazovanje a
	on o.id = a.idOsobe
	join filmovi f
	on a.idFilma = f.id
	where o.id = @osoba
	order by a.svojstvo, f.godina desc
end
go

exec sp_filmografija 1032
go
----------------------------------------------------

create procedure sp_viseSvojstava
as
begin
	select o.ime, f.naziv, broj from 
	(select a.idOsobe as idOsobe, a.idFilma as idFilma, count(*) as broj
	from angazovanje a
	group by a.idOsobe, a.idFilma
	having count(*)>1) as tabela1
	join osobe o on tabela1.idOsobe=o.id
	join filmovi f on tabela1.idFilma = f.id
end
go

exec sp_viseSvojstava
go