At times, especially when working with cubes with date/time dimensions, it's necessary for me to dynamically generate a list of date values for use in those dimensions. That single date dimension often services a number of cubes, and the range of date values can be determined from the min/max date values from serveral tables.
I've written the following SQL that will retrieve the min/max date values, and create a single record for every day in that min/max range. It's useful to turn this sql into a view which will refresh itself automatically every time the cube calling it is processed.
select
date,
datename(dw, date)
+ ', ' + datename(mm, date)
+ ' ' + datename(dd, date)
+ ' ' + datename(yy, date) [datename],
'Fiscal ' + datename(yy, date) fiscalyearname,
datename(mm, date)
+ ', ' + datename(yy, date) fiscalmonthname,
datepart(day, date) as dayNumber,
datepart(month, date) as monthNumber,
datepart(year, date) as yearNumber
from
(
select
dateadd(
day,
n1.val * 10000 +
n2.val * 1000 +
n3.val * 100 +
n4.val * 10 +
n5.val,
cast(floor(cast(mindate as decimal(12,5))) as datetime)) date
from
(
select 0 as val
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) n1,
(
select 0 as val
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) n2,
(
select 0 as val
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) n3,
(
select 0 as val
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) n4,
(
select 0 as val
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) n5,
(
select
min(minDate) as minDate,
max(maxDate) as maxDate
from
(select
min(date) minDate,
max(date) maxdate
from
<Data table 1>
union all
select
min(date),
max(date)
from
<Data table 2>) dateVals
where
year(mindate) >= 1980
and year(maxdate) <= dateadd(year, 1, getdate())
) c
where
dateadd(
day,
n1.val * 10000 +
n2.val * 1000 +
n3.val * 100 +
n4.val * 10 +
n5.val,
cast(floor(cast(mindate as decimal(12,5))) as datetime)) <= maxDate
) dateVals