Finding if a date is missing from a list of dates and if so add it to the list

Question

I have a table with date(Date) and count(Int) fields as below.

13-09-2013     100
14-09-2013     200
16-09-2013     100
17-09-2013     100
20-09-2013     200
/** and so on **/

What I need to do is find all the missing dates between the first and last date in the date column and insert them to the table with count '0'.

I found a way to do this using C# and linq but would prefer if I could find a sql solution.

Any help is sincerely appreciated..

Thanks

I just answered a similar question: http://stackoverflow.com/questions/27211833/display-counts-for-missing-dates-in-mysql-query — Salman A, Nov 30 '14 at 16:03

score 0 · Answer 1 · answered Nov 30 '14 at 16:07

0

Use a Recursive CTE to generate list of dates between your min and max date.

DECLARE @mindate DATE =(SELECT Min(CONVERT(DATE, '13-09-2013', 105))
          FROM   tablename),
        @maxdate DATE =(SELECT Max(CONVERT(DATE, '13-09-2013', 105))
          FROM   tablename);

WITH cte
     AS (SELECT @mindate AS dates
         UNION ALL
         SELECT Dateadd(Day, 1, dates)
         FROM   cte
         WHERE  dates < @maxdate)
SELECT a.dates,
       Isnull([count], 0)
FROM   cte a
       LEFT JOIN tablename b
              ON b.[date] = a.dates

answered Nov 30 '14 at 16:07

Pரதீப்

91,748
19
131
172

@Arnab - Then why your question is tagged with `SQL SERVER`. – Pரதீப் Nov 30 '14 at 16:22
My bad.. Changed the Tag.. Normally I add SQL Server..I thought I will get a simple sql query, did not expect a recursive CTE.. – Arnab Nov 30 '14 at 16:47

score 0 · Answer 2 · answered Nov 30 '14 at 16:08

create table mytab(mdate date,cnt int)

insert into mytab
select '2013-09-13'    ,  100 union all
select '2013-09-14'  ,   200 union all
select '2013-09-16' ,    100 union all
select '2013-09-17',     100 union all
select '2013-09-17',     200

Try this using Recursive CTE

with
 cte
 as
 (
 select n=CAST('2013-09-13' as DATe)  
 union all
 select DATEADD(dd,1,CTE.n) from CTE
   where n<='2013-09-18'
 )
 select n,ISNULL(cnt,0) from CTE
 LEFT JOIN mytab
 on mdate=CTE.n

OUTPUT

mDate       cnt
2013-09-13  100
2013-09-14  200
2013-09-15  0
2013-09-16  100
2013-09-17  100
2013-09-17  200
2013-09-18  0
2013-09-19  0

Finding if a date is missing from a list of dates and if so add it to the list

2 Answers2