计数group by日期零结果

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时没有记录的日子之外，它工作得很好，这些天不会出现在我的结果中。

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

没有针对(2012 12 15和2012 12 16)的结果。如何修改我的脚本以获得total =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)