SQL query to know the sales of a period (year), dividing by month and showing the total of each month and year

Question

I work as a technical support in an automation system, a customer asked if there was a report in the system that showed the total sales of 2018 divided by month and showing the total of each month and the overall total of the year, turns out that this report does not have in the system so I recommended it to export the reports of total sales month by month and so 12 reports.

has a function in the system for creating reports that requires an sql statement to get the results and show them in the report

the table that stores sales is as follows:

CREATE TABLE [dbo].[venda_geral](
    [venda_id] [uniqueidentifier] NOT NULL,
    [dt_contabil] [date] NOT NULL,
    [modo_venda_id] [smallint] NOT NULL,
    [maquina_id] [smallint] NOT NULL,
    [codigo_situacao] [varchar](1) NULL,
    [perfil_id] [int] NULL,
    [cliente_id] [uniqueidentifier] NULL,
    [vl_limite] [numeric](15, 2) NULL,
    [vl_subtotal_itens] [dbo].[moeda] NULL,
    [vl_subtotal_para_desconto] [dbo].[moeda] NULL,
    [vl_subtotal_para_servico] [dbo].[moeda] NULL,
    [qtd_fechamentos] [int] NULL,
    [numero_chamada] [int] NULL,
    [operacao_id] [uniqueidentifier] NOT NULL,
    [operacao_origem_id] [uniqueidentifier] NULL,
    [dt_hr_ultimo_consumo] [datetime] NULL,
    [dt_alt] [datetime] NULL,
    [obs] [varchar](200) NULL,
    [func_atendeu_id] [int] NULL,
    [codigo_ticket] [varchar](10) NULL,
    [dt_hr_abertura] [datetime] NOT NULL,
    [numero_venda] [int] NULL,
    [situacao]  AS (case [codigo_situacao] when 'N' then 'normal' when 'E' then 'estornada' when 'C' then 'cancelada' when 'T' then 'transferida' else 'desconhecida' end),
    [cancelado]  AS (CONVERT([bit],case [codigo_situacao] when 'N' then (0) else (1) end,(0))),
    [transferido]  AS (CONVERT([bit],case [codigo_situacao] when 'T' then (1) else (0) end,(0))),
    [ticket]  AS (case [modo_venda_id] when (1) then 'balcão ' when (2) then 'entrega ' when (3) then 'mesa ' when (4) then 'ficha ' else 'ticket ' end+[codigo_ticket]),
    [vl_consumacao] [money] NOT NULL,
    [vl_entrada] [money] NOT NULL,
 CONSTRAINT [pk_venda_geral] PRIMARY KEY NONCLUSTERED 
(
    [venda_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

for being a dynamic query I think that a stored Procedure would be more appropriate to allow the declaration of variables, I spent a few hours trying to create one in which the user set the initial date ex: 01/01/2018 and final date 31/12/2018, and the report present the following result.

Periodo de 01/01/2018 até 31/12/2018
Mes       total mensal
janeiro    10000
fevereiro  11000
março      10500
... e assim por diante e no final mostre um total anual
Total anual: 1.500.000,00

PS.: the closest I saw of the result was the following query

SELECT 
'Ano' = YEAR(dt_contabil),
'Mes' = MONTH(dt_contabil),
'Total' =  SUM(vl_subtotal_itens)
FROM venda_geral
GROUP BY 
YEAR(dt_contabil), MONTH(dt_contabil)

Answer 1

Icaro, have you ever tried to do it this way? the only detail here is that the months that have not had sale will not list.

SELECT CASE MONTH(dt_contabil) 
         WHEN 1 THEN 'Jan'
         WHEN 2 THEN 'Fev'
         WHEN 3 THEN 'Mar'
         WHEN 4 THEN 'Abr'
         WHEN 5 THEN 'Mai'
         WHEN 6 THEN 'Jun'
         WHEN 7 THEN 'Jul'
         WHEN 8 THEN 'Ago'
         WHEN 9 THEN 'Set'
         WHEN 10 THEN 'Out'
         WHEN 11 THEN 'Nov'
         WHEN 12 THEN 'Dez'
       END AS Periodo,
       SUM(vl_subtotal_itens) as Valor
  FROM venda_geral
 WHERE YEAR(dt_contabil) = 2018
 GROUP BY MONTH(dt_contabil)
 UNION ALL
SELECT CONVERT(VARCHAR(MAX),YEAR(dt_contabil)) AS Periodo,
       SUM(vl_subtotal_itens) as Valor
  FROM venda_geral
 WHERE YEAR(dt_contabil) = 2018
 GROUP BY YEAR(dt_contabil)

Answer 2

To Stored Procedure below follows the following steps:

1. Get every year between two dates and put in temporary table (#TmpAnos)
2. Creates a temporary table where the orderly and processed records will be kept (#TmpVendas)
3. For each year, create the month, number and name records, with the total value of each
4. At the end of the year create a register with the total of the year concerned
5. Returns a query with columns Ano, Mes and Valor

Valide is for what you want:

CREATE  PROCEDURE TotalVendasMes
        @DataInicial    DATETIME
    ,   @DataFinal      DATETIME
AS
SET NOCOUNT ON 

    DECLARE @Ano        INT
    DECLARE @TotalGeral FLOAT

    CREATE TABLE #TmpVendas
    (
            Ano     INT
        ,   Mes     INT
        ,   MesStr  NVARCHAR(20)
        ,   Valor   FLOAT
    )

    ;WITH CTE AS
    (
        SELECT  DATEPART(YEAR, @DataInicial) AS Ano
        UNION ALL
        SELECT  Ano + 1
        FROM    CTE
        WHERE   Ano < DATEPART(YEAR, @DataFinal)
    )
    SELECT  Ano
    INTO    #TmpAnos
    FROM    CTE

    DECLARE Cur_Vendas CURSOR FOR
    SELECT  Ano
    FROM    #TmpAnos

    OPEN Cur_Vendas

    FETCH NEXT FROM Cur_Vendas INTO @Ano

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO #TmpVendas
        SELECT      @Ano
                ,   MONTH(dt_contabil)
                ,   DATENAME(MONTH, dt_contabil)
                ,   SUM(vl_subtotal_itens)
        FROM        venda_geral
        WHERE       YEAR(dt_contabil) = @Ano
        GROUP BY    @Ano
                ,   MONTH(dt_contabil)
                ,   DATENAME(MONTH, dt_contabil)

        SET @TotalGeral =   ISNULL((
                                SELECT  SUM(Valor)
                                FROM    #TmpVendas
                                WHERE   Ano = @Ano
                            ), 0)

        INSERT INTO #TmpVendas
        SELECT      @Ano
                ,   13
                ,   'Total anual (' + CAST(@Ano AS NVARCHAR(4)) + ')'
                ,   @TotalGeral

        FETCH NEXT FROM Cur_Vendas INTO @Ano
    END

    CLOSE Cur_Vendas
    DEALLOCATE Cur_Vendas

    SELECT      Ano
            ,   MesStr
            ,   Valor
    FROM        #TmpVendas
    ORDER BY    Ano
            ,   Mes
GO

Answer 3

Here’s another SQL option. I don’t know if you need the result in rows or columns.

Functioning

I’ve separated the months, with case so each column stays a month, and at the end I created the column of the year. The way I implemented it, if the month has no sale, it will be listed in SQL.

SQL

select 
  sum(case when MONTH(dt_contabil) = 1 then vl_subtotal_itens else 0 end) Mes01,
  sum(case when MONTH(dt_contabil) = 2 then vl_subtotal_itens else 0 end) Mes02,
  sum(case when MONTH(dt_contabil) = 3 then vl_subtotal_itens else 0 end) Mes03,
  sum(case when MONTH(dt_contabil) = 4 then vl_subtotal_itens else 0 end) Mes04,
  sum(case when MONTH(dt_contabil) = 5 then vl_subtotal_itens else 0 end) Mes05,
  sum(case when MONTH(dt_contabil) = 6 then vl_subtotal_itens else 0 end) Mes06,
  sum(case when MONTH(dt_contabil) = 7 then vl_subtotal_itens else 0 end) Mes07,
  sum(case when MONTH(dt_contabil) = 8 then vl_subtotal_itens else 0 end) Mes08,
  sum(case when MONTH(dt_contabil) = 9 then vl_subtotal_itens else 0 end) Mes09,
  sum(case when MONTH(dt_contabil) = 10 then vl_subtotal_itens else 0 end) Mes10,
  sum(case when MONTH(dt_contabil) = 11 then vl_subtotal_itens else 0 end) Mes11,
  sum(case when MONTH(dt_contabil) = 12 then vl_subtotal_itens else 0 end) Mes12,
  sum(vl_subtotal_itens) as ValorAno
from 
  venda_geral
where
  dt_contabil between '2020-01-01' and '2020-12-31'

Upshot

| Mes01 | Mes02 | Mes03 | Mes04 | Mes05 |  Mes06 | Mes07 | Mes08 | Mes09 | Mes10 | Mes11 |   Mes12 | ValorAno |
|-------|-------|-------|-------|-------|--------|-------|-------|-------|-------|-------|---------|----------|
|  50.5 |   191 | 105.5 |  10.5 |     0 | 105.99 |   5.5 | 105.5 |   0.5 |  17.5 |   1.5 | 12501.5 | 13095.49 |

The SQL implementation is here http://sqlfiddle.com/#! 18/bd77fb/1

Note: I only put in the example the columns you wanted in the result, to facilitate the answer.