Result of the come zeroed query

Asked

Viewed 58 times

0

Good Afternoon,

I have a question, could you help me? ...

What happens is:

I have an sql query that brings me the total amount of sms sent per month and year, but I would like when I had not worked in that month shows up zero in the field. because currently it disappears and does not show.

EX: CURRENTLY inserir a descrição da imagem aqui

SQL :

    SELECT   Count(*) QtdSMSEnviadoPorMes,                 
             MONTH(DataAgendada) AS MES, 
             YEAR(DataAgendada) ANO
        FROM tblLoteTESTE 
            WHERE CodCampanha = 1
        GROUP BY MONTH(DataAgendada),YEAR(DataAgendada )

I would like to show it like this ex: mes 11 0

inserir a descrição da imagem aqui

Thank you!

sql database sql-server query

  • sorry but did not understand, they seem to me equal the results, taking out the value of line 11 blurred

  • So this red smudge I made as an example a zero ! Rs EX:if you do not have registration in November show me 0 @Ricardopunctual

  • you have to give the select in the months... with left Join with your records. See: https://answall.com/a/300026/69359 may even be a duplicate question, except for sgdb which is different

  • @Rovannlinhalis But my records are on a single table

  • See the answer to the other question, there is no table of months there too

  • @Rovannlinhalis Forgive my ignorance, but I’m layman in the area I haven’t been able to solve yet.

  • @Rovannlinhalis got otherwise thank you.

  • See if it helps, Union with "zero" data, artificial sequence https://social.msdn.microsoft.com/Forums/sqlserver/pt-BR/99190dd6-17a7-4c70-8d6a-b30a8910b8ac/gerar-sequenceof datas?forum=520

Show 3 more comments

1 answer

1


As I said, you need to select the months, and then select your data.

The months you can generate numerical sequences from 1 to 12, or go giving select Union.

Considering the following scenario:

create table tblLoteTESTE 
(
  id integer,
  DataAgendada datetime,
);


insert into tblLoteTESTE values (1, '01/01/2018');
insert into tblLoteTESTE values (2, '02/01/2018');
insert into tblLoteTESTE values (3, '03/01/2018');
insert into tblLoteTESTE values (4, '04/01/2018');
insert into tblLoteTESTE values (5, '04/01/2018');
insert into tblLoteTESTE values (6, '06/01/2018');
insert into tblLoteTESTE values (7, '07/01/2018');
insert into tblLoteTESTE values (8, '07/01/2018');
insert into tblLoteTESTE values (9, '07/01/2018');
insert into tblLoteTESTE values (10, '10/01/2018');
insert into tblLoteTESTE values (11, '11/01/2018');
insert into tblLoteTESTE values (12, '12/01/2018');
insert into tblLoteTESTE values (13, '12/01/2018');
insert into tblLoteTESTE values (14, '10/01/2018');
insert into tblLoteTESTE values (15, '06/01/2018');

Your query should look like this:

SELECT 
    DATENAME(month, DATEADD(month, m.mes-1, CAST('2008-01-01' AS datetime))) as 
    mes_nome,
    m.mes,
    count(t.id) as qtd
FROM
  (SELECT 1 AS mes
   UNION SELECT 2
   UNION SELECT 3
   UNION SELECT 4
   UNION SELECT 5
   UNION SELECT 6
   UNION SELECT 7
   UNION SELECT 8
   UNION SELECT 9
   UNION SELECT 10
   UNION SELECT 11
   UNION SELECT 12 ) AS m
LEFT OUTER JOIN tblLoteTESTE t ON MONTH(t.DataAgendada) = m.mes and YEAR(t.DataAgendada) = 2018
group by m.mes

Upshot:

mes_nome    mes qtd
January     1   1
February    2   1
March       3   1
April       4   2
May         5   0
June        6   2
July        7   3
August      8   0
September   9   0
October     10  2
November    11  1
December    12  2

Note that it did not enter the year, because it would have to generate the sequence of the years, with a cross Join in the months, to then count the records. Which seems unnecessary to me. This type of information is usually seen year by year, so just inform the desired year as filter.

I put in the Sqlfiddle

Browser other questions tagged sql database sql-server query

You are not signed in. Login or sign up in order to post.