CteDate View

dbo.DimDate.sql

alter view dbo.DimDate
as
    with DateSeq(DateValue) as (
        select cast('2015-01-01' as date) as DateValue
        union all
        select dateadd(day, 1, DateValue) as DateValue
        from DateSeq
        where year(DateValue) < year(getdate()) + 2
    )
    select *,
        iif(IsWeekend = 1, 'Weekend', 'Weekday') as WeekDayType
    from (
        select
            DateValue,
            day(DateValue) as CalendarDayOfMonth,
            month(DateValue) as CalendarMonthOfYear,
            year(DateValue) as CalendarYear,
            datename(month, DateValue) as CalendarMonth,
            datepart(dayofyear, DateValue) as CalendarDayOfYear,
            datepart(quarter, DateValue) as CalendarQuarter,
            datename(weekday, DateValue) as WeekDay,
            datepart(weekday, DateValue) as DayOfWeek,
            iif(datepart(weekday, DateValue) between 2 and 6, 0, 1) as IsWeekend,
            datename(isowk, DateValue) as IsoWeekNumber
        from DateSeq
        where year(DateValue) < year(getdate()) + 2
    ) x
go

select *
from dbo.DimDate
option (maxrecursion 0)