SQL Server: como selecionar todos os dias em um intervalo de datas, mesmo se não houver dados para alguns dias

Eu tenho um aplicativo que precisa mostrar um gráfico de barras para a atividade nos últimos 30 dias. O gráfico precisa ser exibido todos os dias, mesmo que não haja atividade para o dia.

por exemplo:

DATE COUNT ================== 1/1/2011 5 1/2/2011 3 1/3/2011 0 1/4/2011 4 1/5/2011 0 etc.... 

Eu poderia fazer pós-processamento após a consulta para descobrir quais datas estão faltando e adicioná-los, mas queria saber se há uma maneira mais fácil de fazê-lo no SQL Server. Muito obrigado

Você pode usar um CTE recursivo para construir sua lista de 30 dias e, em seguida, associá-lo aos seus dados

 --test select cast('05 jan 2011' as datetime) as DT, 1 as val into #t union all select CAST('05 jan 2011' as datetime), 1 union all select CAST('29 jan 2011' as datetime), 1 declare @start datetime = '01 jan 2011' declare @end datetime = dateadd(day, 29, @start) ;with amonth(day) as ( select @start as day union all select day + 1 from amonth where day < @end ) select amonth.day, count(val) from amonth left join #t on #t.DT = amonth.day group by amonth.day >> 2011-01-04 00:00:00.000 0 2011-01-05 00:00:00.000 2 2011-01-06 00:00:00.000 0 2011-01-07 00:00:00.000 0 2011-01-08 00:00:00.000 0 2011-01-09 00:00:00.000 0 ... 

Usando CTE:

 WITH DateTable AS ( SELECT CAST('20110101' AS Date) AS [DATE] UNION ALL SELECT DATEADD(dd, 1, [DATE]) FROM DateTable WHERE DATEADD(dd, 1, [DATE]) < cast('20110201' as Date) ) SELECT dt.[DATE], ISNULL(md.[COUNT], 0) as [COUNT] FROM [DateTable] dt LEFT JOIN [MyData] md ON md.[DATE] = dt.[DATE] 

Isto está assumindo que tudo é uma data; se for DateTime, você terá que truncar (com DATEADD(dd, 0, DATEDIFF(dd, 0, [DATE])) ).

A resposta do @Alex K. está completamente correta, mas não funciona para versões que não suportam expressões recursivas de tabelas comuns (como a versão com a qual estou trabalhando). Neste caso, o seguinte faria o trabalho.

 DECLARE @StartDate datetime = '2015-01-01' DECLARE @EndDate datetime = SYSDATETIME() ;WITH days AS ( SELECT DATEADD(DAY, n, DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate), 0)) as d FROM ( SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 FROM sys.all_objects ORDER BY [object_id] ) AS n ) select days.d, count(t.val) FROM days LEFT OUTER JOIN yourTable as t ON t.dateColumn >= days.d AND t.dateColumn < DATEADD(DAY, 1, days.d) GROUP BY days.d ORDER BY days.d; 

Defina uma tabela estática contendo datas ou crie uma variável temporária table \ table dinamicamente para armazenar cada data entre (e incluindo) as datas mínimas e máximas na tabela de atividades com a qual você está trabalhando.

Use uma associação externa entre as duas tabelas para garantir que cada data na tabela de datas seja refletida na saída.

Se você usar uma tabela de datas estáticas, provavelmente desejará limitar o intervalo de datas que é gerado apenas para o intervalo necessário no gráfico.

Sem Transact-SQL: MS SQL 2005 – Obtenha uma lista de todos os dias de um mês:

No meu caso, ‘20121201’ é um valor predefinido.


  SELECT TOp (Select Day(DateAdd(day, -Day(DateAdd(month, 1, '20121201')), DateAdd(month, 1, '20121201')))) DayDate FROM ( SELECT DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,'20121201') as DayDate FROM sys.objects s1 CROSS JOIN sys.objects s2 ) q 

crie uma tabela de números e use-a como:

 declare @DataTable table (DateColumn datetime) insert @DataTable values ('2011-01-09') insert @DataTable values ('2011-01-10') insert @DataTable values ('2011-01-10') insert @DataTable values ('2011-01-11') insert @DataTable values ('2011-01-11') insert @DataTable values ('2011-01-11') declare @StartDate datetime SET @StartDate='1/1/2011' select @StartDate+Number,SUM(CASE WHEN DateColumn IS NULL THEN 0 ELSE 1 END) FROM Numbers LEFT OUTER JOIN @DataTable ON DateColumn=@StartDate+Number WHERE Number>=1 AND Number<=15 GROUP BY @StartDate+Number 

SAÍDA:

 ----------------------- ----------- 2011-01-02 00:00:00.000 0 2011-01-03 00:00:00.000 0 2011-01-04 00:00:00.000 0 2011-01-05 00:00:00.000 0 2011-01-06 00:00:00.000 0 2011-01-07 00:00:00.000 0 2011-01-08 00:00:00.000 0 2011-01-09 00:00:00.000 1 2011-01-10 00:00:00.000 2 2011-01-11 00:00:00.000 3 2011-01-12 00:00:00.000 0 2011-01-13 00:00:00.000 0 2011-01-14 00:00:00.000 0 2011-01-15 00:00:00.000 0 2011-01-16 00:00:00.000 0 (15 row(s) affected) 

Talvez algo como isto: Crie DaysTable countaining os 30 dias. E DataTable contendo coluna “day” e coluna “count”. E depois saiu para se juntar a eles.

 WITH DaysTable (name) AS ( SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 -- .. And so on to 30 ), DataTable (name, value) AS ( SELECT DATEPART(DAY, [Date]), [Count] FROM YourExampleTable WHERE [Date] < DATEADD (day , -30 , getdate()) ) SELECT DaysTable.name, DataTable.value FROM DaysTable LEFT JOIN DataTable ON DaysTable.name = DataTable.name ORDER BY DaysTable.name 

Para aqueles com alergia recursiva

 select SubQ.TheDate from ( select DATEADD(day, aa + (10 * ba) + (100 * ca), DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) - 30) AS TheDate from ( (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c ) WHERE aa + (10 * ba) + (100 * ca) < 30 ) AS SubQ ORDER BY TheDate 

Tente.

 DECLARE @currentDate DATETIME = CONVERT(DATE, GetDate()) DECLARE @startDate DATETIME = DATEADD(DAY, -DAY(@currentDate)+1, @currentDate) ;WITH fnDateNow(DayOfDate) AS ( SELECT @startDate AS DayOfDate UNION ALL SELECT DayOfDate + 1 FROM fnDateNow WHERE DayOfDate < @currentDate ) SELECT fnDateNow.DayOfDate FROM fnDateNow 

Meu cenário era um pouco mais complexo que o do OP, então pensei em compartilhar para ajudar outras pessoas que têm problemas semelhantes. Eu precisava agrupar pedidos de vendas por data, enquanto os pedidos são armazenados com data e hora.

Assim, na tabela de pesquisa “dias”, não consegui armazenar como data e hora de ’00: 00: 00.000′ e obter correspondências. Por isso, armazenei como uma string e tentei unir diretamente o valor convertido.

Isso não retornou nenhuma linha zero, e a solução foi fazer uma subconsulta retornando a data já convertida em uma string.

Exemplo de código da seguinte forma:

 declare @startDate datetime = convert(datetime,'09/02/2016') declare @curDate datetime = @startDate declare @endDate datetime = convert(datetime,'09/09/2016') declare @dtFormat int = 102; DECLARE @null_Date varchar(24) = '1970-01-01 00:00:00.000' /* Initialize #days table */ select CONVERT(VARCHAR(24),@curDate, @dtFormat) as [Period] into #days /* Populate dates into #days table */ while (@curDate < @endDate ) begin set @curDate = dateadd(d, 1, @curDate) insert into #days values (CONVERT(VARCHAR(24),@curDate, @dtFormat)) end /* Outer aggregation query to group by order numbers */ select [Period], count(c)-case when sum(c)=0 then 1 else 0 end as [Orders], sum(c) as [Lines] from ( /* Inner aggregation query to sum by order lines */ select [Period], sol.t_orno, count(*)-1 as c from ( /* Inner query against source table with date converted */ select convert(varchar(24),t_dldt, @dtFormat) as [shipdt], t_orno from salesorderlines where t_dldt > @startDate ) sol right join #days on shipdt = #days.[Period] group by [Period], sol.t_orno ) as t group by Period order by Period desc drop table #days 

Resultados da Amostra:

 Period Orders Lines 2016.09.09 388 422 2016.09.08 169 229 2016.09.07 1 1 2016.09.06 0 0 2016.09.05 0 0 2016.09.04 165 241 2016.09.03 0 0 2016.09.02 0 0 
 DECLARE @StartDate DATE = '20110101', @NumberOfYears INT = 1; DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate); CREATE TABLE Calender ( [date] DATE ); INSERT Calender([date]) SELECT d FROM ( SELECT d = DATEADD(DAY, rn - 1, @StartDate) FROM ( SELECT TOP (DATEDIFF(DAY, '2011-01-01', '2011-12-31')) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 ORDER BY s1.[object_id] ) AS x ) AS y; create table test(a date) insert into test values('1/1/2011') insert into test values('1/1/2011') insert into test values('1/1/2011') insert into test values('1/1/2011') insert into test values('1/1/2011') insert into test values('1/2/2011') insert into test values('1/2/2011') insert into test values('1/2/2011') insert into test values('1/4/2011') insert into test values('1/4/2011') insert into test values('1/4/2011') insert into test values('1/4/2011') select c.date as DATE,count(ta) as COUNT from calender c left join test t on c.date = ta group by c.date