How to bring null query Sql server value

Asked

Viewed 380 times

2

I have an sql query where brings the total of a value inserted in a date, but I want to demonstrate in the result the dates that generated zero, even not containing information in the database, always searching 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

Upshot:

DATA       | VALOR
02/10/2015 | 36312
05/10/2015 | 25382
06/10/2015 | 3655
sql sql-server-2008

  • There is the record of that date with the value zeroed or there is no date in the table?

  • the dates that do not appear in the result do not have in the database, would have to demonstrate in the query the days that do not have with the value zero, in order with the others that contain the values

1 answer

1

You need to implement some functionality that lists days between 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: https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function

Browser other questions tagged sql sql-server-2008

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