Function to return months of year sql server

Question

I’m new in sql server and would like if possible a help with an exercise I’m doing.

I have a query that she returns me the region of a resale, the name of the resale, the cnpj, the registration date and the amount of sending sales per month, I set up the consultation for the month of January, but instead of me giving a Ctrl+C and Ctrl+V I would like to create a function that was adding the months for me, someone could give a help?

There is the query:

SELECT      vc.vc                                   AS [VC]
        ,   r.ds_razaosocial                        AS [Revenda]
        ,   r.ds_cnpj                               AS [Cnpj]
        ,   CONVERT(VARCHAR, r.dt_cadastro, 103)    AS [Data de cadastro]
        ,   (
                SELECT  COUNT(*)
                FROM    enviovendas AS ei
                WHERE   ei.ds_cnpjrev       = r.ds_cnpj 
                    AND YEAR(ei.dt_envio)   = 2018
                    AND MONTH(ei.dt_envio)  = 1
            )                                       AS [Jan]
        ,   (
                SELECT  COUNT(*)
                FROM    enviovendas AS ei
                WHERE   ei.ds_cnpjrev       = r.ds_cnpj 
                    AND YEAR(ei.dt_envio)   = 2018
            )                                       AS [Total]
FROM        revendas        AS r 
INNER JOIN  usuarios        AS u    ON r.id_system_user = u.id_usuario 
INNER JOIN  vc_das_revendas AS vc   ON r.ds_cnpj        = vc.ds_cnpjrev
WHERE       u.fl_ativo_s_n      = 'S' 
        AND r.e_uma_revenda_fun = 1
        AND EXISTS  (
                        SELECT  *
                        FROM    enviovendas AS ee
                        WHERE   ee.ds_cnpjrev       = r.ds_cnpj
                            AND YEAR(ee.dt_envio)   = 2018
                    )
ORDER BY    vc.vc
        ,   [Revenda]

Answer 1

Try doing it this way:

CREATE FUNCTION fn_TotalMesAno(@Data DATE, @Ano INT, @Cnpj NVARCHAR(20))
RETURNS INT
AS
BEGIN
    DECLARE @Contador INT

    IF ISNULL(@Ano, 0) = 1
        SELECT      @Contador = COUNT(1)
        FROM        enviovendas
        WHERE       YEAR(dt_envio)  = YEAR(@Data)
                AND ds_cnpjrev      = @Cnpj
    ELSE
        SELECT      @Contador = COUNT(1)
        FROM        enviovendas
        WHERE       YEAR(dt_envio)  = YEAR(@Data)
                AND MONTH(dt_envio) = MONTH(@Data)
                AND ds_cnpjrev      = @Cnpj

    RETURN (@Contador)
END

Then the consultation is simplified with the use of Function:

SELECT      vc.vc                                                       AS [VC]
        ,   r.ds_razaosocial                                            AS [Revenda]
        ,   r.ds_cnpj                                                   AS [Cnpj]
        ,   CONVERT(VARCHAR, r.dt_cadastro, 103)                        AS [Data de cadastro]
        ,   ISNULL(dbo.fn_TotalMesAno(ev.dt_envio, 0, r.ds_cnpj), 0)    AS [ValorMes]
        ,   ISNULL(dbo.fn_TotalMesAno(ev.dt_envio, 1, r.ds_cnpj), 0)    AS [Total]
FROM        revendas        AS r 
INNER JOIN  usuarios        AS u    ON  r.id_system_user    = u.id_usuario 
INNER JOIN  vc_das_revendas AS vc   ON  r.ds_cnpj           = vc.ds_cnpjrev
LEFT JOIN   enviovendas     AS ev   ON  ev.ds_cnpjrev       = r.ds_cnpj
                                    AND YEAR(ev.dt_envio)   = 2018
WHERE       u.fl_ativo_s_n      = 'S' 
        AND r.e_uma_revenda_fun = 1
ORDER BY    vc.vc
        ,   r.ds_razaosocial

Withdraw the '2018' of LEFT JOIN will get the values of each month of each year.