sql-server - 按日期零结果计数组

Question

我有这个 SQL 来获取报告:

select 
    count(id_reg) total, 
    YEAR(fechaingreso) ano, 
    MONTH(fechaingreso) mes, 
    DAY(fechaingreso) Dia 
from 
    vw_casos_tc 
where 
    fechaingreso between '2012-12-05' and '2013-5-8' 
group by  
    DAY(fechaingreso), MONTH(fechaingreso), YEAR(fechaingreso) 
order by 
    Ano, Mes, dia

它工作正常，除了当 count(id_reg) = 0 时没有记录的 DAYS，这几天没有出现在我的结果中。

total   ano     mes     dia
-----------------------------
66  2012    12  13
22  2012    12  14
23  2012    12  17

(2012 12 15 和 2012 12 16) 没有结果。我如何修改我的脚本以获得总计 = 0 的这一天？

Answer 1

这是一种方法，假设使用 SQL Server 2008(尽管您可以针对 SQL Server 2005 轻松修改它):

DECLARE @StartDate DATE, @EndDate DATE
SET @StartDate = '20121205'
SET @EndDate = '20130508';

WITH AllDays AS
(
    SELECT @StartDate YourDate
    UNION ALL
    SELECT DATEADD(DAY,1,YourDate)
    FROM AllDays
    WHERE DATEADD(DAY,1,YourDate) <= @EndDate
)
SELECT  A.YourDate,
        ISNULL(B.Total,0) Total
FROM AllDays A
LEFT JOIN ( SELECT  CONVERT(DATE,fechaingreso) YourDate,
                    COUNT(id_reg) Total
            FROM vw_casos_tc
            WHERE fechaingreso >= @StartDate 
              AND fechaingreso < DATEADD(DAY, 1, @EndDate)
            GROUP BY CONVERT(DATE,fechaingreso)) B
    ON A.YourDate = B.YourDate
OPTION(MAXRECURSION 0)