SQL Server: Cómo seleccionar todos los días en un intervalo de fechas incluso si no existen datos para algunos días

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 es asumiendo que todo es una Fecha; si es DateTime, tendrás que truncar (con DATEADD(dd, 0, DATEDIFF(dd, 0, [DATE]))).

La respuesta de@Alex K. es completamente correcta, pero no funciona para versiones que no admiten expresiones recursivas de tabla común (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 variable temp table \ table sobre la marcha para almacenar cada fecha entre (e incluyendo) las fechas min y max en la tabla de actividad con la que está trabajando.

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

Si utiliza una tabla de fechas estática, es probable que desee limitar el rango de fechas que se muestra solo al rango necesario en el gráfico.

Sin Transact-SQL: MS SQL 2005-Obtener 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 contando los 30 días. Y DataTable que contiene la columna "día" y la columna "recuento". Y luego a la izquierda unirse 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 recursión

select SubQ.TheDate
from 
(
    select DATEADD(day, a.a + (10 * b.a) + (100 * c.a), 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 a.a + (10 * b.a) + (100 * c.a) < 30
) AS SubQ
ORDER BY TheDate

Pruébalo.

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 de OP, así que pensé en compartir para ayudar a otros que tienen problemas similares. Necesitaba agrupar las órdenes de venta por fecha de toma, mientras que las órdenes se almacenan con datetime.

Así que en la tabla de búsqueda de "días" realmente no podía almacenar como una fecha y hora con la hora '00:00:00.000' y obtener cualquier coincidencia. Por lo tanto, almacené como una cadena y traté de unir el valor convertido directamente.

Que no devolvió ninguna fila cero, y el la solución era hacer una sub-consulta devolviendo la fecha ya convertida a una cadena.

Código de muestra como sigue:

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 la 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(t.a) as COUNT from calender c left join test t on c.date = t.a group by c.date