Junte uma tabela de datas a uma tabela de dados para obter contagens por ano/mês

O truque é começar pela tabela de datas. Use isso para criar uma projeção dos anos/meses desejados. Em seguida, faça um JOIN da projeção para a tabela de arrendamentos. Como a tabela de datas é considerada completa, podemos usar um INNER JOIN e não precisamos de LEFT ou CROSS, embora um LEFT funcione.

Também é bom pensar cuidadosamente sobre a expressão condicional usada para o JOIN. Queremos evitar a mutação dos dados na Leasetabela, pois isso precisaria acontecer em todas as linhas ... mesmo nas linhas fora do intervalo. De que outra forma o banco de dados saberia se o resultado corresponde? Isso também impediria o uso do índice, provavelmente causando uma varredura completa da tabela quando uma operação mais eficiente estaria disponível.

Então temos isto:

WITH Months AS (
    -- create the projection
    select DATETRUNC(month, d.[Date]) MonthStart, dateadd(month, 1, datetrunc(month, d.[Date])) MonthEnd
    from [DatesTable] d
    where d.[Date] >= '20190101' and d.[Date] < '20200101'
    group by datetrunc(month, d.[Date])
)
SELECT YEAR(m.MonthStart) [Year], MONTH(m.MonthStart) [Month], COUNT(l.*) [Count]
FROM Months m -- start from the projection
-- and then add the Lease data
INNER JOIN Lease l ON l.move_out_date >= m.MonthStart AND l.move_out_date < m.MonthEnd
GROUP BY m.MonthStart

Assim como o Joel , eu começaria na tabela Calendário (se você não tiver uma, crie uma, há muitos recursos disponíveis sobre como fazer isso), mas, em vez disso, não usaria um CTE para filtrar as datas de início/término do mês. Em vez disso, eu pegaria todas as datas, LEFT JOINas colocaria na sua tabela e depois as agruparia. Isso resulta no seguinte:

SELECT C.CalendarYear,
       C.CalendarMonth,
       COUNT(l.move_out_date) AS MoveOutCount
FROM dbo.CalendarTable C
     LEFT JOIN dbo.lease l ON C.CalendarDate = l.move_out_date
WHERE C.CalendarDate >= '20190101'
  AND C.CalendarDate < '20200101'
GROUP BY C.CalendarYear,
         C.CalendarMonth
ORDER BY  C.CalendarYear,
         C.CalendarMonth;

Se você "preferir" filtrar por ano, filtre pela coluna de ano da tabela de calendário ( C.CalendarYear = 2019). Apenas certifique-se de que sua Tabela de Calendário esteja bem indexada.

Sem uma tabela de datas

Como alternativa, você pode construir sua tabela de meses programaticamente (com uma Common Table Expression recursiva ), sem precisar de uma tabela de datas (… e, portanto, sem ter que mantê-la e garantindo que você não deixou um buraco nela).

WITH months AS
(
    SELECT 2010 y, 1 m -- Start month
    UNION ALL
    SELECT CASE WHEN m = 12 THEN y + 1 ELSE y END, m % 12 + 1 FROM months
    WHERE y + m < 2050 + 12 -- End month
)
SELECT y MOYear, m MOMonth, COUNT(move_out_date) AS Count
FROM months LEFT JOIN lease ON YEAR(move_out_date) = y AND MONTH(move_out_date) = m
GROUP BY y, m
ORDER BY y, m
OPTION(MAXRECURSION 500); -- We're working on 40 years = 480 months, so we need to reinsure SQL Server about limiting ourselves (else the default 100 will be applied, and truncate our report).

Você pode vê-lo funcionando em um violino (... limitado a 2019!).

O SQL adaptado do Snowflake produziu exatamente os resultados necessários.

Por curiosidade, implementarei esse método em uma consulta muito maior com muitos sindicatos e, em seguida, agruparei esses resultados em uma resposta para um KPI de fluxo de caixa líquido.

Aqui ele é adaptado para Snowflake.

WITH Months AS (
  SELECT YEAR(date) AS Year, MONTH(date) AS Month
  FROM Date d
  WHERE d.Date >= '2019-01-01' AND d.Date < '2020-01-01'
  GROUP BY YEAR(date), MONTH(date)
)
SELECT YEAR, MONTH, COUNT(move_out_date) AS Count
FROM Months m
LEFT OUTER JOIN Lease l ON 
  l.move_out_date >= date_from_parts(Year, Month, 1) AND 
  l.move_out_date <= last_day(date_from_parts(year, month, 1))
GROUP BY year, month