I have a sql query where it brings the total of a value entered into a date, however I want to show in the result the dates that generated zero, even though it does not contain information in the database, I always search in a period and the days are random both for payment, for days that do not contain data.
SELECT DATA,
SUM(VALOR)
FROM TABELA1
WHERE CONTA = '176087'
GROUP BY DATA
Result:
DATA | VALOR
02/10/2015 | 36312
05/10/2015 | 25382
06/10/2015 | 3655
You need to implement some functionality that lists the days between the start and end dates:
CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with
N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);
From there, extract the MIN and MAX values from the dates of your query, and pass them to the function:
SELECT * FROM dbo.ExplodeDates('20151002','20151006') as d;
The result of this statement is as follows:
thedate
October, 02 2015 00:00:00
October, 03 2015 00:00:00
October, 04 2015 00:00:00
October, 05 2015 00:00:00
October, 06 2015 00:00:00
Use it as a grouper in your original query.
Source: link