SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns

Question

I want to show all dates between two dates when there is any date data missing then its should show zero in val column .

declare @temp table (
id int identity(1,1) not null,
CDate smalldatetime ,
val int
)

INSERT STATEMENT FOR DATA TO CHECK

insert into @temp select '10/2/2012',1
insert into @temp select '10/3/2012',1
insert into @temp select '10/5/2012',1
insert into @temp select '10/7/2012',2
insert into @temp select '10/9/2012',2
insert into @temp select '10/10/2012',2
insert into @temp select '10/13/2012',2
insert into @temp select '10/15/2012',2

Retrieve records between first day of month and today

select * from @temp where CDate between '10/01/2012' AND '10/15/2012'

As i run this query its show me all data between these two dates but i want to also include missing dates with val=0

SQL FIDDLE WITH SAMPLE DATA

RichardTheKiwi · Accepted Answer · 2012-10-15T17:46:11.187

12

;with d(date) as (
  select cast('10/01/2012' as datetime)
  union all
  select date+1
  from d
  where date < '10/15/2012'
  )
select t.ID, d.date CDate, isnull(t.val, 0) val
from d
left join temp t
       on t.CDate = d.date
order by d.date
OPTION (MAXRECURSION 0) -- use this if your dates are >99 days apart

You need to make up the dates, so I've use a recursive common table expression here. SQL Fiddle

MAXRECURSION number

Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.

When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

edited Oct 15 '12 at 17:46

answered Oct 15 '12 at 07:32

RichardTheKiwi

105,798
26
196
262

instead of making dates can we do something in where clause ... i think we can also use ! (NOT )operator to find missing dates and can union two - one for dates and other for missing dates – rahularyansharma Oct 15 '12 at 07:37
1

You're going to need to get dates from SOMEWHERE, so why not make them? Having the date range in the 2 places in the CTE is no different to a WHERE clause. – RichardTheKiwi Oct 15 '12 at 07:39
@rahularyansharma, if you have an answer, include it in the question? – Jodrell Oct 15 '12 at 07:39
I dont have that idea before this answer post.... but now i am thinking union can be use to dates + missing dates with not operator – rahularyansharma Oct 15 '12 at 07:41
declare temp table (ddate datetime) insert temp select DATEDIFF(d,0,GETDATE() -Number) from master..spt_values where type='p' and number < DatePart(d,GETDATE()) order by 1 SELECT * FROM temp – rahularyansharma Oct 15 '12 at 08:49
@rahularyansharma You could do that. But this answer is better. – Tobsey Oct 15 '12 at 08:51
1

@RichardTheKiwi One possible issue with this is that SQL Server by default sets the maximum recursion of a CTE to 100, so if the date range exceeds 100 days this will fail with an error. This can be avoided by using the query hint OPTION(MAXRECURSION 0). 0 means unlimited, any integer can be placed in there and the query will loop that many times, it's useful to set it to a large value while investigating, but set to 0 once you know the query is correct – Tobsey Oct 15 '12 at 12:34
Thanks Tobsey, added note on Maxrecursion – RichardTheKiwi Oct 15 '12 at 17:46

t-clausen.dk · Answer 2 · 2012-10-15T08:46:15.587

3

This will work as long as there are less than 2047 days between from and to dates

declare @from smalldatetime = '10/01/2012'
declare @to smalldatetime = '10/15/2012'

select t.id, dateadd(day, number,@from), isnull(val, 0) val from @temp t
right join master..spt_values s
on dateadd(d, s.number, @from) = t.CDate
where
datediff(day, @from, @to ) > s.number
and s.type = 'P'

edited Oct 15 '12 at 08:46

answered Oct 15 '12 at 08:23

t-clausen.dk

43,517
12
59
92

1

I would advise against this solution. spt_values is in master, so there may be security concerns in a production environment. Also spt_values is not documented or supported so there is no guarantee it will remain unchanged in future releases. – Tobsey Oct 15 '12 at 08:33
@Tobsey are you trolling ? This is used everywhere. It will never be removed from ms-sql and there are not security problems accessing the table – t-clausen.dk Oct 15 '12 at 08:41
1

No I'm no trolling. spt_values is used by internal SQL Server procedures and functions and is not documented or supported for custom code. There are other supported methods, such as Recursive CTEs or even plain old static data in tables to achieve this goal. I'd rather go with the documented and supported solutions. Edit see here for security configuration that will break your solution http://support.microsoft.com/kb/75291 – Tobsey Oct 15 '12 at 08:50
@Tobsey even if there is a problem regarding permissions to the system table, this solution illustrate how to use a tally table to solve the problem. So it the system table is unavailable, it is quite easy to make a new table with the same data. – t-clausen.dk Oct 15 '12 at 12:26
1

I'm not saying it won't work, that's why I didn't downvote your answer. I just wanted to point out the possible pitfalls of using it. There is another weakness to the CTE solution given here, but I think that is the better method to achieve the result. – Tobsey Oct 15 '12 at 12:32
3

I'm here with @Tobsey - I've never liked using some table which I can't control. It's ok if it's some student's problem, but in our system I've just created special numerator table. I also can control indexes on it so so I can be sure that queries will be fast. – Roman Pekar Oct 20 '12 at 05:08

score 2 · Answer 3 · answered Oct 15 '12 at 07:34

I think the best way to do this is to create your own table with dates (you can also use master.dbo.spt_values, but I personally don't like that solution)

declare @Temp_Dates table (CDate datetime)
declare @Date datetime
select @Date = (select min(CDate) from temp)

while @Date <= (select max(CDate) from temp)
begin
    insert into @Temp_Dates (CDate)
    select @Date

    select @Date = dateadd(dd, 1, @Date)
end

select D.CDate, isnull(T.id, 0) as id
from @Temp_Dates as D
    left outer join temp as T on T.CDate = D.CDate

you can also use recursive solution with CTE

score 2 · Answer 4 · answered Oct 15 '12 at 07:36

DECLARE @min DATETIME, 
        @max DATETIME, 
        @val INT 

SELECT @min = Min(CDATE), 
       @max = Max(CDATE) 
FROM   TEMP 

DECLARE @temp TABLE 
  ( 
     CDATE SMALLDATETIME, 
     VAL   INT 
  ) 

WHILE @min < @max 
  BEGIN 
      SELECT @val = VAL 
      FROM   TEMP 
      WHERE  CDATE = @min 

      INSERT @temp 
      VALUES (@min, 
              @val) 

      SET @min = Dateadd(D, 1, @min) 
      SET @val = 0 
  END 

SELECT * 
FROM   @temp

score 1 · Answer 5 · answered Oct 15 '12 at 08:42

Declare @temp Table(id int identity(1,1) not null,CDate smalldatetime ,val int)
insert into @temp select '10/2/2012',1
insert into @temp select '10/3/2012',1
insert into @temp select '10/5/2012',1
insert into @temp select '10/7/2012',2
insert into @temp select '10/9/2012',2
insert into @temp select '10/10/2012',2
insert into @temp select '10/13/2012',2
insert into @temp select '10/15/2012',2

DECLARE @startDate DATE= '10/01/2012'
DECLARE @endDate DATE= '10/15/2012'

SELECT t.Id, X.[Date],Val = COALESCE(t.val,0)
FROM 
    (SELECT [Date] = DATEADD(Day,Number,@startDate)  
    FROM  master..spt_values  
    WHERE Type='P' 
    AND DATEADD(day,Number,@startDate) <= @endDate)X
LEFT JOIN  @temp t 
ON X.[Date] = t.CDate

enter image description here

score 0 · Answer 6 · answered Apr 11 '18 at 13:48

using a recursive cte with min and max

declare @T table (id int identity(1,1) primary key, dt date not null, val int not null);
insert into @T (dt, val) values 
       ('10/2/2012',1)
     , ('10/3/2012',1)
     , ('10/5/2012',1)
     , ('10/7/2012',2)
     , ('10/9/2012',2)
     , ('10/10/2012',2)
     , ('10/13/2012',2)
     , ('10/15/2012',2);
--select * from @T;
with cte as 
( select min(dt) as dt, max(dt) as mx 
  from @T 
  union all 
  select dateadd(dd, 1, dt), mx 
  from CTE 
  where dt < mx
)
select c.dt, isnull(t.val, 0) as val 
from cte c
left join @T t
on c.dt = t.dt
order by c.dt
option (maxrecursion 0);

dt         val
---------- -----------
2012-10-02 1
2012-10-03 1
2012-10-04 0
2012-10-05 1
2012-10-06 0
2012-10-07 2
2012-10-08 0
2012-10-09 2
2012-10-10 2
2012-10-11 0
2012-10-12 0
2012-10-13 2
2012-10-14 0
2012-10-15 2

SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns

6 Answers6

Linked

Related