DateTime Tally Table
Five ways to generate Date Tally Table:
-- 1. way
;create table tc..dt
(id int identity(1,1)
,dt datetime)
insert into dt(dt)
select
a.dt+b.number as dt
from
(select cast('2010/12/31' as datetime) as dt
) as a
cross join master..spt_values as b
where
type = 'P' and number between 1 and 365
-- 2. way
;create table tc..dt
(id int identity(1,1)
,dt datetime)
;with tab as
(
select cast('2011-1-1' as datetime) as dt, 1 as rn
union all
select dateadd(day,1,dt) as dt,rn + 1 as rn
from tab
where rn < 365
)
insert into dt(dt)
select dt
from tab
option (maxrecursion 365)
-- 3. way
;create table tc..dt
(id int identity(1,1)
,dt datetime)
insert into dt(dt)
;select
cast('2010/12/31' as datetime)+number as dt
from master..spt_values as b
where
type = 'P' and number between 1 and 365
-- 4. way
;create table tc..dt
(id int identity(1,1)
,dt datetime)
insert into dt(dt)
select
dateadd(day, isnull((select scope_identity()),2),'2010/12/31')
go 365
-- 5. way
;create table tc..dt
(id int identity(1,1)
,dt datetime)
while isnull(@@identity,1) <= 365
begin
insert into dt(dt)
select
dateadd(day, isnull(@@identity,1),'2010/12/31') as dt
end