Function to return the months of the year sql server

Question

Function to return the months of the year sql server

Navigation

#1 by (0 votes)

1

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

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

Here's 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]

sql

asked by anonymous 03.10.2018 / 22:10

1 answer

Problem with Registration in PHP / MySql Error testing GET method with Jasmine / Angular

score 0 · Answer 1

Try 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 query becomes 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

If you remove% with% from% with%, you will be able to have the values of each month of each year.