CODEWITHSUNDEEP
sql-servercountgroup-by
HUGO POLANCO
HUGO POLANCO

Reputation: 81

Count group by date zero result

I have this SQL to get a report:

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

It works fine excepts for the DAYS that have no records when count(id_reg) = 0, this days doesn't appear on my result.

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

There is no result for (2012 12 15 and for 2012 12 16). How can I modify my script to get this days with total = 0 ?

Upvotes: 2

Views: 259

Answers (1)

Lamak
Lamak

Reputation: 70678

Here is a way, assuming SQL Server 2008 (though you can easily modify it for 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)

Upvotes: 5

Related Questions