Query Result to be Zero

Question

Query Result to be Zero

Navigation

#1 by (1 votes)

0

Good afternoon,

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

What happens is:

I have a sql query that brings me the total amount of sms sent per month and year, but I wish when I did not get that month it shows zero in the field. because it currently disappears and does not show.

EX: CURRENTLY

SQL:

SELECTCount(*)QtdSMSEnviadoPorMes,MONTH(DataAgendada)ASMES,YEAR(DataAgendada)ANOFROMtblLoteTESTEWHERECodCampanha=1GROUPBYMONTH(DataAgendada),YEAR(DataAgendada)

Iwouldliketoshowitex:month110

Thank you!

sql sql-server query

asked by anonymous 26.07.2018 / 22:33

1 answer

Django Rest Framework with Images How to make a panel with double information with tr

score 1 · Accepted Answer

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

The months you can generate numeric 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

Result:

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 you did not enter the year because you would have to generate the sequence of years, with a cross join in the months, and then count the records. Which seems to me unnecessary. This type of information is usually seen year by year, so you just have to enter the desired year as a filter.

I put it in SQLFiddle