SIBD - S14.2

/*2*/
/*2.1*/
IF OBJECT_ID('TotDivida') IS NOT NULL
    DROP FUNCTION TotDivida
GO
CREATE FUNCTION TotDivida ()
RETURNS FLOAT
BEGIN
    RETURN (SELECT SUM(TotalFactura) FROM Facturas WHERE TotalFactura-Pagamento-Crédito > 0)
END

PRINT 'Valor total da dívida: ' + CONVERT(VARCHAR, dbo.TotDivida())

/*2.2*/
IF OBJECT_ID('fn_AplicaDesconto') IS NOT NULL
    DROP FUNCTION fn_AplicaDesconto
GO
CREATE FUNCTION fn_AplicaDesconto (@valor FLOAT)
RETURNS FLOAT
BEGIN
    IF @valor >= 5000
        RETURN @valor * 0.9
    IF @valor >= 500
        RETURN @valor * 0.95
    IF @valor >= 100
        RETURN @valor * 0.98
    RETURN @valor
END

PRINT 'Valor 50.00 com desconto: ' + CONVERT(VARCHAR, dbo.fn_AplicaDesconto(50))
PRINT 'Valor 100.00 com desconto: ' + CONVERT(VARCHAR, dbo.fn_AplicaDesconto(100))
PRINT 'Valor 560.00 com desconto: ' + CONVERT(VARCHAR, dbo.fn_AplicaDesconto(560))
PRINT 'Valor 10000.00 com desconto: ' + CONVERT(VARCHAR, dbo.fn_AplicaDesconto(10000))

/*2.3*/
IF OBJECT_ID('fn_MelFunc') IS NOT NULL
    DROP FUNCTION fn_MelFunc
GO
CREATE FUNCTION fn_MelFunc (@num INT)
RETURNS @TableRet TABLE (Fornecedor INT, Nome VARCHAR(50), TotalFactura MONEY)
BEGIN
    INSERT @TableRet
    SELECT TOP (@num)  Fornecedor, Nome, TotalFactura
        FROM  Fornecedores JOIN Facturas
            ON Fornecedor = IDFornecedor
        ORDER BY TotalFactura DESC
    RETURN
END

SELECT * FROM dbo.fn_MelFunc(5)

/*2.4*/
IF OBJECT_ID('fn_MelFunc') IS NOT NULL
    DROP FUNCTION fn_MelFunc
GO
CREATE FUNCTION fn_MelFunc (@quant INT)
RETURNS @TableForn TABLE (Nome VARCHAR(50), Cont INT, Soma MONEY)
BEGIN
    INSERT @TableForn
    SELECT Nome, COUNT(*), SUM(TotalFactura)
        FROM  Fornecedores JOIN Facturas
            ON Fornecedor = IDFornecedor
        GROUP BY Nome
        HAVING COUNT(*) >= @quant
    RETURN
END

SELECT * FROM dbo.fn_MelFunc(3)

/*2.5*/
IF OBJECT_ID('fn_MedFact') IS NOT NULL
    DROP FUNCTION fn_MedFact
GO
CREATE FUNCTION fn_MedFact (@quant INT)
RETURNS @TableMed TABLE (Forn INT, NomeF VARCHAR(50), DataMax DATE, MeD MONEY)
BEGIN
    INSERT @TableMed
    SELECT DISTINCT TOP(@quant) IDFornecedor, Nome, MAX(DataFactura), AVG(TotalFactura)
        FROM  Fornecedores JOIN Facturas
            ON Fornecedor = IDFornecedor
        GROUP BY IDFornecedor, Nome
        ORDER BY AVG(TotalFactura) DESC
    RETURN
END

SELECT * FROM dbo.fn_MedFact(5)

/*2.6*/
IF OBJECT_ID('fn_SepFrase') IS NOT NULL
    DROP FUNCTION fn_SepFrase
GO
CREATE FUNCTION fn_SepFrase(@frase VARCHAR(150), @separador CHAR)
RETURNS @TablePal TABLE (Palavra VARCHAR(50))
BEGIN
    DECLARE @INDEX INT = 1,@AUX INT = 1;
    WHILE (charindex(@separador,@frase,@AUX) > 0)
    BEGIN
        SET @INDEX = charindex(@separador,@frase,@AUX)
        INSERT @TablePal SELECT SUBSTRING(@frase, @AUX, @INDEX -@AUX)
        SET @AUX = @INDEX
    END
    RETURN
END

SELECT * FROM dbo.fn_SepFrase('91,12,65,78,56,789',',')