SHOW ALL Dates data between two dates; if no row e

问题:

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

回答1:

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

回答2:

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'

回答3:

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

回答4:

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 

回答5:

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

回答6:

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