[ABD] Freq 14/15 IV C)

/*IV*/
/*C*/
CREATE PROCEDURE PEstatMed
AS
BEGIN
    DECLARE @EstatMed TABLE (Medicamento VARCHAR(50), Ano INT, Mes INT, Total_Prescricoes INT, Especialidade VARCHAR(50), Valor FLOAT)

    DECLARE @NMed INT,
            @Med VARCHAR(50),
            @Ano INT,
            @Mes INT,
            @DATA DATE,
            @TotMed INT,
            @Espec VARCHAR(50),
            @Valor INT,
            @PercVal FLOAT,

    DECLARE C_Med CURSOR
    FOR (SELECT m.NM, m.principio_ativo, p.adm_inicio, e.Designacao, COUNT(*) FROM d_Medicamentos m JOIN d_Prescricoes p
        ON m.NM = p.Nmedicamento JOIN d_Medico me
            ON me.NM = m.Nmedico JOIN d_Especialidade e
                ON e.NE = me.Nespecialidade
        GROUP BY m.NM, e.NE)
    OPEN C_Med
    FETCH FIRST FROM C_Med INTO @NMed, @Med, @DATA, @Espec, @Valor
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Ano = DATEPART(YEAR, @DATA)
        SET @Mes = DATEPART(MONTH, @DATA)
        SET @TotMed = (SELECT COUNT(*) FROM d_Prescricoes WHERE Nmed = @NMed)
        SET @PercVal = 100 * @Valor / @TotMed

        INSERT INTO @EstatMed VALUES(@Med, @Ano, @Mes, @TotMed, @Espec, @PercVal)

        FETCH NEXT FROM C_Med INTO @NMed, @Med, @DATA, @Espec, @Valor
    END
    CLOSE C_Med
    DEALLOCATE C_Med

    SELECT * FROM @EstatMed
END

GO

EXEC PEstatMed