CODEWITHSUNDEEP
sqlsql-servert-sql
rahularyansharma
rahularyansharma

Reputation: 10777

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

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

Upvotes: 5

Views: 22652

Answers (6)

paparazzo
paparazzo

Reputation: 45106

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

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107806

;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.

Upvotes: 12

t-clausen.dk
t-clausen.dk

Reputation: 44336

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'

Upvotes: 3

Niladri Biswas
Niladri Biswas

Reputation: 4171

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

Upvotes: 1

Gidil
Gidil

Reputation: 4137

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

Upvotes: 2

roman
roman

Reputation: 117540

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

Upvotes: 2

Related Questions