/*
############################### RESOLUÇÃO da FICHA 2 ##################################
################################## alíneas A a J ######################################
############################### AULA DE 19-05-2004 ####################################
*/
--*********************************************************************************************
--alínea 1.A
--*********************************************************************************************

CREATE PROCEDURE spIstec AS

print 'ISTEC'
GO

--*********************************************************************************************
--alínea 1.B
--*********************************************************************************************

CREATE PROCEDURE spSoma
(
@num1 int,
@num2 int
)
AS

declare @res int
set @res = @num1 + @num2
print @res
GO

--*********************************************************************************************
--alínea 1.C
--*********************************************************************************************

CREATE PROCEDURE spSaudacao AS

if(datepart(hh,getdate())>=12 and datepart(hh,getdate())<20)
begin
	print 'boa tarde'
end
else if(datepart(hh,getdate())>=20 and datepart(hh,getdate())<=24)
begin 
	print 'boa noite'
end
else 
begin 
	print 'boa dia'
end
GO

--*********************************************************************************************
--alínea 1.D
--*********************************************************************************************

CREATE PROCEDURE spPares AS

declare @num int
set @num = 2

while(@num<=20)
begin
	print @num 
	set @num = @num + 2
end
GO

--*********************************************************************************************
--alínea 2.E 
--*********************************************************************************************

CREATE PROCEDURE spInserirHistorico
(
@idDep int
)
AS

INSERT INTO Historico(idDep) VALUES(@idDep)
GO

--*********************************************************************************************
--alínea 2.E 
--*********************************************************************************************

CREATE PROCEDURE spGestaoDepartamento
(
@operacao char(10),
@nome varchar(50)=null,
@idDep int = 0
)
 AS

if(@operacao='INSERIR')
	INSERT INTO departamento VALUES(@nome)
else if(@operacao='ALTERAR')
	UPDATE departamento SET nome=@nome WHERE idDep = @idDep
else if(@operacao='REMOVER')
	DELETE FROM departamento WHERE idDep = @idDep
else if(@operacao='CONSULTAR')
	SELECT * FROM departamento WHERE idDep = @idDep
else if(@operacao='LISTAR')
	SELECT * FROM departamento ORDER BY nome
GO

--*********************************************************************************************
--alínea 2.E 
--*********************************************************************************************

CREATE PROCEDURE spOcorrenciasPorDepartamento AS


select departamento.nome,count(*) AS 'Ocorrências'
from historico,departamento
where historico.idDep=departamento.idDep
group by departamento.nome
order by count(*) desc
GO

--*********************************************************************************************
--alínea 2.E 
--*********************************************************************************************

CREATE PROCEDURE spHistoricoDatas
(
@dataInicial as datetime,
@datafinal as datetime
)

 AS

SELECT * FROM historico WHERE data BETWEEN @dataInicial and @dataFinal
GO

--*********************************************************************************************
--alínea 2.E 
--*********************************************************************************************

CREATE PROCEDURE spDepartamentoMaisVisitado AS

declare @occurs int
declare @dep int
declare @nome varchar(50)

select top 1 @occurs=count(*), @dep = idDep
from historico
group by idDep
order by count(*) desc


select @nome=nome from departamento where idDep = @dep

print 'O departamento ' + @nome + ' foi o mais visitado com ' + convert(varchar,@occurs) + ' visitas.'
GO