How to bring date without Sql server 2008 record

Question

I have an SQL query adding up values of each day, returning only the days that have values. But I would also like to present the days that had no records.

Consultation:

SELECT DATA, 
       SUM(VALOR)
FROM TABELA1
WHERE CONTA = '176087'
GROUP BY DATA

Exit:

02/10/2015  36312
05/10/2015  25382
06/10/2015  3655

Answer 1

Would that be?

SELECT DATA, COALESCE(SUM(VALOR),0) FROM TABELA1 WHERE CONTA = '176087' GROUP BY DATA

Answer 2

A solution is to create an auxiliary structure with the date range for which you want to generate the results.

If you want to use this solution, you have several alternatives (the list is not exhaustive):

• Create a physical table in the database that contains, for example, all dates since the year 2000;
• Create a temporary table with a date range;
• Use a CTE;

I’ll leave a solution here using a CTE.

create table tabela1
(
  conta     nvarchar(05),
  [data]    datetime,
  valor     float
);
insert into tabela1(conta, [data], valor)values
('12345', '2016-05-25', 1),
('12345', '2016-05-26', 3),
('12345', '2016-05-26', 1),
('12345', '2016-05-28', 2),
('12345', '2016-05-28', 3);

with Datas as (
     select cast('2016-05-25' as date) [data]
     union all
     select dateadd(dd, 1, t.[data]) 
       from Datas t
      where dateadd(dd, 1, t.[data]) <= '2016-05-31'
) -- gera uma view descartável com o intervalo de datas entre 25 a 31 de Maio
select d.[data],
       isnull(sum(valor), 0) sum_valor
  from Datas d
left join tabela1 t
   on t.[data] = d.[data]
  and t.conta = '12345'
group by d.[data]
order by d.[data]

The previous statement will generate the following output:

data         sum_valor
2016-05-25   1
2016-05-26   4
2016-05-27   0
2016-05-28   5
2016-05-29   0
2016-05-30   0
2016-05-31   0

Stay here the Sqlfiddle