USP_POPULATEDATEDIM
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @StartDate | datetime | IN | |
| @EndDate | datetime | IN | |
| @QuarterStartMonth | int | IN |
Definition
Copy
CREATE procedure [BBDW].[USP_POPULATEDATEDIM]
@StartDate datetime,
@EndDate datetime,
@QuarterStartMonth int = 1
-- @StartDate use in 'MM/DD/YYYY' format
-- @EndDate use in 'MM/DD/YYYY' format
-- @QuarterStartMonth - month when fiscal year starts, e.g. 7 is July 1st, 10 is Oct 1st, etc. Set 0 to insert default member
as
begin
--declare variables
declare @DT datetime
declare @i int
declare @DateKey int
declare @CalendarYear smallint
declare @CalendarHalf tinyint
declare @CalendarHalfName char(2)
declare @CalendarQuarter tinyint
declare @CalendarQuarterName char(2)
declare @CalendarMonth tinyint
declare @CalendarMonthName char(10)
declare @CalendarWeek tinyint
declare @CalendarDayofYear smallint
declare @FiscalYear smallint
declare @FiscalHalf tinyint
declare @FiscalHalfName char(2)
declare @FiscalQuarter tinyint
declare @FiscalQuarterName char(2)
declare @FiscalMonth tinyint
declare @FiscalMonthName char(10)
declare @FiscalWeek tinyint
declare @FiscalDayofYear smallint
declare @DayofMonth int
declare @DayofWeek int
declare @DayName varchar(12)
declare @IsWeekend bit
declare @IsWeekday bit
declare @IsHoliday bit
declare @IsClosed bit
declare @IsLastClosedDate bit
declare @HolidayName varchar(50)
declare @MonthName varchar(20)
declare @IsLeapYear bit
declare @CurrentDateDimID int
declare @CurrentDate datetime
set @CurrentDate = getdate()
set @CurrentDateDimID = year(@CurrentDate)
* 10000
+ month(@CurrentDate)
* 100
+ day(@CurrentDate)
--initialize variables
select @IsHoliday = 0
select @IsWeekend = 0
select @FiscalWeek = 1
select @CalendarWeek = 1
select @IsLeapYear = 0
select @IsClosed = 0
select @IsLastClosedDate = 0
--the starting date for the date dimension
select @DT = @StartDate
set @i = 1
--start looping, stop at ending date
while (@DT <= @EndDate)
begin
--get information about the data
select @IsWeekend = 0
select @IsWeekDay = 1
select @CalendarYear = datepart(YYYY,@DT)
select @CalendarHalf = case datepart(Q,@DT)
when 1 then 1
when 2 then 1
when 3 then 2
when 4 then 2
end
select @CalendarHalfName = 'H'
+ cast(@CalendarHalf as char)
select @CalendarQuarter = datepart(Q,@DT)
select @CalendarQuarterName = 'Q'
+ cast(@CalendarQuarter as char)
select @CalendarMonth = datepart(month,@DT)
select @CalendarMonthName = datename(MM,@DT)
select @CalendarWeek = datepart(WW,@DT)
select @CalendarDayofYear = datepart(DY,@DT)
select @DayofMonth = datepart(DD,@DT)
select @DayofWeek = datepart(DW,@DT)
select @DayName = datename(DW,@DT)
--note if weekend or not
if (@DayofWeek = 1
or @DayofWeek = 7)
begin
select @IsWeekend = 1
select @IsWeekDay = 0
end
--add business rule (need to know complete weeks in a year, so a partial week in new year set to 0)
if (@DayofWeek != 1
and @FiscalDayofYear = 1)
begin
select @FiscalWeek = 0
end
if (@DayofWeek = 1)
begin
select @FiscalWeek = @FiscalWeek
+ 1
end
--add business rule (start counting business weeks with first complete week)
if (@FiscalWeek = 53)
begin
select @FiscalWeek = 1
end
--check for leap year
if ((@CalendarYear%4 = 0)
and (@CalendarYear%100 != 0
or @CalendarYear%400 = 0))
select @IsLeapYear = 1
else
select @IsLeapYear = 0
--insert values into Date Dimension table
insert DIM_DATE
(DATEDIMID,
--DateKey,
CALENDARYEAR,
CALENDARHALF,
CALENDARHALFNAME,
CALENDARQUARTER,
CALENDARQUARTERNAME,
CALENDARMONTH,
CALENDARMONTHNAME,
CALENDARWEEK,
CALENDARDAYOFYEAR,
FISCALYEAR,
FISCALHALF,
FISCALHALFNAME,
FISCALQUARTER,
FISCALQUARTERNAME,
FISCALMONTH,
FISCALMONTHNAME,
FISCALWEEK,
FISCALDAYOFYEAR,
DAYOFMONTH,
DAYOFWEEK,
DAYNAME,
ISWEEKEND,
ISWEEKDAY,
ISHOLIDAY,
ISLEAPYEAR,
ISCLOSED,
ISLASTCLOSEDDATE,
HOLIDAYNAME,
ACTUALDATE,
ISINCLUDED,
ETLCONTROLID,
SOURCEDIMID
)
values(--@i,
(@CalendarYear
* 10000)
+ (@CalendarMonth
* 100)
+ @DayofMonth,
@CalendarYear,
@CalendarHalf,
@CalendarHalfName,
@CalendarQuarter,
@CalendarQuarterName,
@CalendarMonth,
@CalendarMonthName,
@CalendarWeek,
@CalendarDayofYear,
null,
null,
null,
null,
null,
null,
null,
null,
null,
@DayofMonth,
@DayofWeek,
@DayName,
@IsWeekend,
@IsWeekDay,
@IsHoliday,
@IsLeapYear,
@IsClosed,
@IsLastClosedDate,
'', -- Holiday name (only one per date)
convert(datetime,cast(@CalendarMonth as varchar)
+ '/'
+ cast(@DayofMonth as varchar)
+ '/'
+ cast(@CalendarYear as varchar),
101),
1,
0,
1
)
--increment the date one day
select @DT = dateadd(day,1,@DT)
set @i = @i
+ 1
end
--*********************** Fiscal Stuff *****************************************************************
update DIM_DATE
set FISCALQUARTER = convert(int,BBDW.UFN_CALCQUARTERDATE(ACTUALDATE,@QuarterStartMonth,10)),
FISCALQUARTERNAME = BBDW.UFN_CALCQUARTERDATE(ACTUALDATE,@QuarterStartMonth,1),
FISCALYEAR = BBDW.UFN_RETURNFISCALYEAR(ACTUALDATE,@QuarterStartMonth)
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
update DIM_DATE
set FISCALHALF = case
when FISCALQUARTER in (1,2) then 1
else 2
end,
FISCALHALFNAME = case
when FISCALQUARTER in (1,2) then 'H1'
else 'H2'
end
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
if @QuarterStartMonth > 1
begin
-- Fiscal Year Start
update DIM_DATE
set FISCALYEARSTARTDATE = convert(datetime,convert(varchar(4),BBDW.UFN_RETURNFISCALYEAR(ACTUALDATE,@QuarterStartMonth)
- 1)
+ '/'
+ convert(varchar(2),@QuarterStartMonth)
+ '/01',101),
FISCALYEARENDDATE = convert(datetime,convert(varchar(4),BBDW.UFN_RETURNFISCALYEAR(ACTUALDATE,@QuarterStartMonth))
+ '/'
+ convert(varchar(2),@QuarterStartMonth)
+ '/01',101)
- 1
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
update DIM_DATE
set FISCALDAYOFYEAR = datediff([DAY],FISCALYEARSTARTDATE,ACTUALDATE)
+ 1,
FISCALWEEK = datediff([WEEK],FISCALYEARSTARTDATE,ACTUALDATE)
+ 1,
FISCALMONTH = datediff([MONTH],FISCALYEARSTARTDATE,ACTUALDATE)
+ 1,
FISCALMONTHNAME = datename(MM,ACTUALDATE)
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
end -- IF @QuarterStartMonth > 1
else
if @QuarterStartMonth = 1
begin
update DIM_DATE
set FISCALDAYOFYEAR = CALENDARDAYOFYEAR,
FISCALWEEK = CALENDARWEEK,
FISCALMONTH = CALENDARMONTH,
FISCALMONTHNAME = datename(MM,ACTUALDATE)
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
update DIM_DATE
set FISCALYEARSTARTDATE = convert(datetime,convert(varchar(4),BBDW.UFN_RETURNFISCALYEAR(ACTUALDATE,@QuarterStartMonth))
+ '/'
+ convert(varchar(2),@QuarterStartMonth)
+ '/01',101),
FISCALYEARENDDATE = convert(datetime,convert(varchar(4),BBDW.UFN_RETURNFISCALYEAR(ACTUALDATE,@QuarterStartMonth)
+ 1)
+ '/'
+ convert(varchar(2),@QuarterStartMonth)
+ '/01',101)
- 1
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
end -- IF @QuarterStartMonth = 1
-- Period Start & End
update DIM_DATE
set PERIODSTARTDATE = convert(datetime,convert(varchar(4),CALENDARYEAR)
+ '-'
+ convert(varchar(2),CALENDARMONTH)
+ '-1',101),
PERIODENDDATE = dateadd(MM,1,convert(datetime,convert(varchar(4),CALENDARYEAR)
+ '-'
+ convert(varchar(2),CALENDARMONTH)
+ '-1',101))
- 1
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
-- Fiscal Quarter Start & End
update DIM_DATE
set FISCALQUARTERSTARTDATE = T.QUARTERSTARTDATE,
FISCALQUARTERENDDATE = T.QUARTERENDDATE
from (select FISCALYEAR,
FISCALQUARTER,
min(ACTUALDATE) as QUARTERSTARTDATE,
max(ACTUALDATE) as QUARTERENDDATE
from DIM_DATE
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
group by FISCALYEAR,FISCALQUARTER) T
where T.FISCALYEAR = DIM_DATE.FISCALYEAR
and T.FISCALQUARTER = DIM_DATE.FISCALQUARTER
-- Calendar Quarter Start & End
update DIM_DATE
set CALENDARQUARTERSTARTDATE = T.QUARTERSTARTDATE,
CALENDARQUARTERENDDATE = T.QUARTERENDDATE
from (select CALENDARYEAR,
CALENDARQUARTER,
min(ACTUALDATE) as QUARTERSTARTDATE,
max(ACTUALDATE) as QUARTERENDDATE
from DIM_DATE
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
group by CALENDARYEAR,CALENDARQUARTER) T
where T.CALENDARYEAR = DIM_DATE.CALENDARYEAR
and T.CALENDARQUARTER = DIM_DATE.CALENDARQUARTER
-- Fiscal Period
update DIM_DATE
set FISCALPERIOD = FISCALMONTH
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
-- Fiscal Week Start & End
update DIM_DATE
set FISCALWEEKSTARTDATE = T.STARTDATE,
FISCALWEEKENDDATE = T.ENDDATE
from (select FISCALYEAR,
FISCALWEEK,
min(ACTUALDATE) as STARTDATE,
max(ACTUALDATE) as ENDDATE
from DIM_DATE
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
group by FISCALYEAR,FISCALWEEK) T
where T.FISCALYEAR = DIM_DATE.FISCALYEAR
and T.FISCALWEEK = DIM_DATE.FISCALWEEK
-- Calendar Week Start & End
update DIM_DATE
set CALENDARWEEKSTARTDATE = T.STARTDATE,
CALENDARWEEKENDDATE = T.ENDDATE
from (select CALENDARYEAR,
CALENDARWEEK,
min(ACTUALDATE) as STARTDATE,
max(ACTUALDATE) as ENDDATE
from DIM_DATE
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
group by CALENDARYEAR,CALENDARWEEK) T
where T.CALENDARYEAR = DIM_DATE.CALENDARYEAR
and T.CALENDARWEEK = DIM_DATE.CALENDARWEEK
-- Date Long Descriptions
update DIM_DATE
set ACTUALDATESTRING = convert(varchar(12),ACTUALDATE,102),
ACTUALDATESTRINGMDY = left(CALENDARMONTHNAME,3)
+ ' '
+ convert(varchar(2),DAYOFMONTH)
+ ' '
+ convert(varchar(4),CALENDARYEAR),
ACTUALDATESTRINGDMY = convert(varchar(2),DAYOFMONTH)
+ ' '
+ left(CALENDARMONTHNAME,3)
+ ' '
+ convert(varchar(4),CALENDARYEAR),
CALENDARHALFYEARNAME = CALENDARHALFNAME
+ ' '
+ convert(varchar(4),CALENDARYEAR),
CALENDARFULLHALFYEARNAME = CALENDARHALFNAME
+ ' CY '
+ convert(varchar(4),CALENDARYEAR),
CALENDARQUARTERYEARNAME = CALENDARQUARTERNAME
+ ' '
+ convert(varchar(4),CALENDARYEAR),
CALENDARFULLQUARTERYEARNAME = CALENDARQUARTERNAME
+ ' CY '
+ convert(varchar(4),CALENDARYEAR),
CALENDARMONTHYEARNAME = left(CALENDARMONTHNAME,3)
+ ' '
+ convert(varchar(4),CALENDARYEAR),
CALENDARFULLMONTHYEARNAME = left(CALENDARMONTHNAME,3)
+ ' CY '
+ convert(varchar(4),CALENDARYEAR),
CALENDARWEEKYEARNAME = 'W'
+ case
when CALENDARWEEK < 10 then '0'
+ convert(varchar(2),CALENDARWEEK)
else convert(varchar(2),CALENDARWEEK)
end
+ ' '
+ convert(varchar(4),CALENDARYEAR),
CALENDARFULLWEEKYEARNAME = 'W'
+ case
when CALENDARWEEK < 10 then '0'
+ convert(varchar(2),CALENDARWEEK)
else convert(varchar(2),CALENDARWEEK)
end
+ ' CY '
+ convert(varchar(4),CALENDARYEAR),
FISCALHALFYEARNAME = FISCALHALFNAME
+ ' '
+ convert(varchar(4),FISCALYEAR),
FISCALFULLHALFYEARNAME = FISCALHALFNAME
+ ' FY '
+ convert(varchar(4),FISCALYEAR),
FISCALQUARTERYEARNAME = FISCALQUARTERNAME
+ ' '
+ convert(varchar(4),FISCALYEAR),
FISCALFULLQUARTERYEARNAME = FISCALQUARTERNAME
+ ' FY '
+ convert(varchar(4),FISCALYEAR),
FISCALPERIODYEARNAME = 'Period '
+ convert(varchar(2),FISCALPERIOD)
+ ' '
+ convert(varchar(4),FISCALYEAR),
FISCALMONTHYEARNAME = left(FISCALMONTHNAME,3)
+ ' '
+ convert(varchar(4),FISCALYEAR),
FISCALFULLMONTHYEARNAME = left(FISCALMONTHNAME,3)
+ ' FY '
+ convert(varchar(4),FISCALYEAR),
FISCALWEEKYEARNAME = 'W'
+ case
when FISCALWEEK < 10 then '0'
+ convert(varchar(2),FISCALWEEK)
else convert(varchar(2),FISCALWEEK)
end
+ ' '
+ convert(varchar(4),FISCALYEAR),
FISCALFULLWEEKYEARNAME = 'W'
+ case
when FISCALWEEK < 10 then '0'
+ convert(varchar(2),FISCALWEEK)
else convert(varchar(2),FISCALWEEK)
end
+ ' FY '
+ convert(varchar(4),FISCALYEAR)
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
-- Set defaults for nth day of week
update DIM_DATE
set MONSEQUENCE = 0,
TUESEQUENCE = 0,
WEDSEQUENCE = 0,
THUSEQUENCE = 0,
FRISEQUENCE = 0,
SATSEQUENCE = 0,
SUNSEQUENCE = 0
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
-- nth Day of Week
update DIM_DATE
set MONSEQUENCE = T.sequence
from (select DATEDIMID,
rank()
over(partition by CALENDARMONTH,CALENDARYEAR order by DATEDIMID) as sequence
from DIM_DATE
where DAYNAME = 'Monday'
and ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate) T
where T.DATEDIMID = DIM_DATE.DATEDIMID
update DIM_DATE
set TUESEQUENCE = T.sequence
from (select DATEDIMID,
rank()
over(partition by CALENDARMONTH,CALENDARYEAR order by DATEDIMID) as sequence
from DIM_DATE
where DAYNAME = 'Tuesday'
and ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate) T
where T.DATEDIMID = DIM_DATE.DATEDIMID
update DIM_DATE
set WEDSEQUENCE = T.sequence
from (select DATEDIMID,
rank()
over(partition by CALENDARMONTH,CALENDARYEAR order by DATEDIMID) as sequence
from DIM_DATE
where DAYNAME = 'Wednesday'
and ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate) T
where T.DATEDIMID = DIM_DATE.DATEDIMID
update DIM_DATE
set THUSEQUENCE = T.sequence
from (select DATEDIMID,
rank()
over(partition by CALENDARMONTH,CALENDARYEAR order by DATEDIMID) as sequence
from DIM_DATE
where DAYNAME = 'Thursday'
and ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate) T
where T.DATEDIMID = DIM_DATE.DATEDIMID
update DIM_DATE
set FRISEQUENCE = T.sequence
from (select DATEDIMID,
rank()
over(partition by CALENDARMONTH,CALENDARYEAR order by DATEDIMID) as sequence
from DIM_DATE
where DAYNAME = 'Friday'
and ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate) T
where T.DATEDIMID = DIM_DATE.DATEDIMID
update DIM_DATE
set SATSEQUENCE = T.sequence
from (select DATEDIMID,
rank()
over(partition by CALENDARMONTH,CALENDARYEAR order by DATEDIMID) as sequence
from DIM_DATE
where DAYNAME = 'Saturday'
and ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate) T
where T.DATEDIMID = DIM_DATE.DATEDIMID
update DIM_DATE
set SUNSEQUENCE = T.sequence
from (select DATEDIMID,
rank()
over(partition by CALENDARMONTH,CALENDARYEAR order by DATEDIMID) as sequence
from DIM_DATE
where DAYNAME = 'Sunday'
and ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate) T
where T.DATEDIMID = DIM_DATE.DATEDIMID
------------------------------------------------------------------------------------------
-- Keys for Fiscal/Calendar Weeks, Months and Quarters
------------------------------------------------------------------------------------------
update DIM_DATE
set CALENDARYEARHALF = CALENDARYEAR
* 100
+ CALENDARHALF,
CALENDARYEARQUARTER = CALENDARYEAR
* 100
+ CALENDARQUARTER,
CALENDARYEARMONTH = CALENDARYEAR
* 100
+ CALENDARMONTH,
CALENDARYEARWEEK = CALENDARYEAR
* 100
+ CALENDARWEEK,
FISCALYEARHALF = FISCALYEAR
* 100
+ FISCALHALF,
FISCALYEARQUARTER = FISCALYEAR
* 100
+ FISCALQUARTER,
FISCALYEARPERIOD = FISCALYEAR
* 100
+ FISCALPERIOD,
FISCALYEARMONTH = FISCALYEAR
* 100
+ FISCALMONTH,
FISCALYEARWEEK = FISCALYEAR
* 100
+ FISCALWEEK
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
------------------------------------------------------------------------------------------
-- Current Date & Filter
------------------------------------------------------------------------------------------
update DIM_DATE
set FILTER = 1
where ACTUALDATE >= @StartDate
and ACTUALDATE <= @EndDate
update DIM_DATE
set ISCURRENTDATE = 0
update DIM_DATE
set ISCURRENTDATE = 1
where DATEDIMID = @CurrentDateDimID
-- Since & Recency calcs
update BBDW.DIM_DATE
set
DAYSSINCE = DATEDIFF(dd, ACTUALDATE, @CurrentDate),
WEEKSSINCE = DATEDIFF(ww, ACTUALDATE, @CurrentDate),
MONTHSSINCE = DATEDIFF(mm, ACTUALDATE, @CurrentDate),
YEARSSINCE = ROUND(DATEDIFF(mm, ACTUALDATE, @CurrentDate) / 12, 0, 1)
where DATEDIMID > 0
end --- Of Stored Proc