How to select and count records from the last 30 days, if there is no record for a given day, display 0 in the count

Question

I have a table in wich some records are stored according to the moment or date thay are processed.

I need to create a graph for the last 30 days, so if in any given day there where no records I need a 0, so that the graph will paint the 0 and won't ignore that specific day.

So far this is what I've accomplished, but It'll give me only the days that have records on them:

SELECT
    CAST([dbo].[N_Insc_Preg_Control].[FechaInscFn] AS DATE) AS [Fecha],
    COUNT(*) AS [Regs]
FROM [dbo].[N_Insc_Preg_Control]

WHERE
    [dbo].[N_Insc_Preg_Control].[FechaInscFn] >= DATEADD(DAY, DATEDIFF(DAY,0,CURRENT_TIMESTAMP)- 7,0)

GROUP BY
    CAST([dbo].[N_Insc_Preg_Control].[FechaInscFn] AS DATE)

This returns this:

Fecha | Regs

2017-05-04 | 5

2017-05-05 | 2

2017-05-07 | 3

2017-05-08 | 7

So the table is missing all the days scince the last 30 days... I need all those days to appear in that table, but in 0

Any hint on how can I achieve this???

Thanks!

You need a calendar table to join to. something like this http://stackoverflow.com/a/2157776/6167855 — S3S, May 11 '17 at 20:20
One example of how to do this is here: http://stackoverflow.com/questions/19075098/how-to-fill-missing-dates-by-groups-in-a-table-in-sql — khan, May 11 '17 at 20:22

score 1 · Accepted Answer · answered May 12 '17 at 01:07

Assuming that you had already a count from your existing Query:

This is follow like as your Given Sample Data from above:

DECLARE @StartDate datetime
DECLARE @EndDate datetime
set @StartDate = DATEADD(MONTH,-1,GETDATE())
set @EndDate = GETDATE()

--Your Given Query or Sample Data here
declare @tb table (fetcha date,Regs int)
insert into @tb
select '2017-05-04' as Fetcha, 5 as Regs union all
select '2017-05-05' , 2 union all
select '2017-05-07' , 3 union all
select '2017-05-08' , 7
--End of your given Query

;WITH cte1 (S) AS (
SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (S)
),
cte2 (S) AS (SELECT 1 FROM cte1 AS cte1 CROSS JOIN cte1 AS cte2),
cte3 (S) AS (SELECT 1 FROM cte1 AS cte1 CROSS JOIN cte2 AS cte2)

select result as fetcha,CASE WHEN Regs IS NULL THEN 0 ELSE Regs end as Regs from
(select distinct cast(result as date) result from
(SELECT TOP (DATEDIFF(day, @StartDate, @EndDate) + 1)
        result = DATEADD(day, ROW_NUMBER() OVER(ORDER BY S) - 1, @StartDate)
FROM cte3) as res) as list_of_dates

left join

(select fetcha,Regs from @tb where fetcha >=@StartDate and fetcha<=@EndDate) as dt_fromTable
on list_of_dates.result = dt_fromTable.fetcha

Output last 30 days from now:

--Fetcha---Regs--
2017-04-12  0
2017-04-13  0
2017-04-14  0
2017-04-15  0
2017-04-16  0
2017-04-17  0
2017-04-18  0
2017-04-19  0
2017-04-20  0
2017-04-21  0
2017-04-22  0
2017-04-23  0
2017-04-24  0
2017-04-25  0
2017-04-26  0
2017-04-27  0
2017-04-28  0
2017-04-29  0
2017-04-30  0
2017-05-01  0
2017-05-02  0
2017-05-03  0
2017-05-04  5
2017-05-05  2
2017-05-06  0
2017-05-07  3
2017-05-08  7
2017-05-09  0
2017-05-10  0
2017-05-11  0
2017-05-12  0

score 0 · Answer 2 · answered May 11 '17 at 20:38

You can generate dates for last one month and do cross apply as below: I modified your query

SELECT
    CAST([dbo].[N_Insc_Preg_Control].[FechaInscFn] AS DATE) AS [Fecha],
    COUNT(*) AS [Regs] --or sum of values
FROM [dbo].[N_Insc_Preg_Control]
cross apply
(
    select top(datediff(day, dateadd(month,-1,CAST([dbo].[N_Insc_Preg_Control].[FechaInscFn] AS DATE)), CAST([dbo].[N_Insc_Preg_Control].[FechaInscFn] AS DATE))+1) dt = dateadd(day, ( row_number() over(order by (select null)) - 1), dateadd(month,-1,CAST([dbo].[N_Insc_Preg_Control].[FechaInscFn] AS DATE))) from master..spt_values s1, master..spt_values s2
) q
WHERE
    [dbo].[N_Insc_Preg_Control].[FechaInscFn] >= DATEADD(DAY, DATEDIFF(DAY,0,CURRENT_TIMESTAMP)- 7,0)
GROUP BY
    CAST([dbo].[N_Insc_Preg_Control].[FechaInscFn] AS DATE)

score 0 · Answer 3 · answered May 11 '17 at 20:39

You should join your query with calendar table, if you has no such a table you can generate it for the last 30 days like this:

    with nums as 
    (
    select number as n 
    from master..spt_values
    where type = 'p'
    and number between 1 and 30
    )

    ,calendar as
    (
    select dateadd(day, -n, cast(getdate() as date)) as dt
    from nums
    )

    select ...
    from calendar c left join...

How to select and count records from the last 30 days, if there is no record for a given day, display 0 in the count

Fecha | Regs

3 Answers3