SQL Server: cómo seleccionar todos los días en un rango de fechas incluso si no existen datos durante algunos días

Tengo una aplicación que necesita mostrar un gráfico de barras para ver la actividad en los últimos 30 días. El gráfico debe mostrar todos los días, incluso si no hay actividad durante el día.

por ejemplo:

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

Podría hacer un postprocesamiento después de la consulta para averiguar qué fechas faltan y agregarlas, pero me preguntaba si existe una forma más fácil de hacerlo en SQL Server. Muchas gracias

Puedes usar un CTE recursivo para construir tu lista de 30 días, luego unir eso a tus datos

 --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] 

Esto supone que todo es una Fecha; si es DateTime, tendrá que truncar (con DATEADD(dd, 0, DATEDIFF(dd, 0, [DATE])) ).

La respuesta de @Alex K. es completamente correcta, pero no funciona para las versiones que no admiten expresiones de tabla común recursivas (como la versión con la que estoy trabajando). En este caso, lo siguiente haría el trabajo.

 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 una tabla estática que contenga fechas o cree una tabla temporal \ table variable sobre la marcha para almacenar cada fecha entre (e incluyendo) las fechas mínimas y máximas en la tabla de actividades con la que está trabajando.

Use una combinación externa entre las dos tablas para asegurarse de que cada fecha en su tabla de fechas se refleje en la salida.

Si usa una tabla de fechas estáticas, es probable que desee limitar el rango de fechas que se genera solo al rango necesario en el gráfico.

Sin Transact-SQL: MS SQL 2005: obtenga una lista de todos los días de un mes:

En mi caso, ‘20121201’ es un 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 

crea una tabla de números y úsala 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 

SALIDA:

 ----------------------- ----------- 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) 

Tal vez algo como esto: Crear DaysTable que contenga los 30 días. Y DataTable que contiene la columna “día” y la columna “contar”. Y luego los dejé unir a ellos.

 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 aquellos con alergia a la recidiva

 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 

Intentalo.

 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 

Mi escenario era un poco más complejo que el ejemplo OP, así que pensé en compartir para ayudar a otros que tienen problemas similares. Necesitaba agrupar las órdenes de venta por toma de fecha, mientras que las órdenes se almacenan con fecha y hora.

Entonces en la tabla de búsqueda de “días” realmente no pude almacenar como una fecha con el tiempo ’00: 00: 00.000 ‘y obtener cualquier coincidencia. Por lo tanto, almacené como una cadena e intenté unirme al valor convertido directamente.

Eso no devolvió ninguna fila cero, y la solución fue hacer una subconsulta que devuelva la fecha ya convertida en una cadena.

Código de muestra de la siguiente manera:

 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 de muestra:

 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