Date dimension (Monday is the first day of each week)

Dim _ Date:

Create Table [DBO]. [dim _ date] ([date id] [varchar] (8) Not null, [year] [int] Null, [half year] [varchar] (6) null, [season] [varchar] (2) null, [month] [varchar] (4) null, [week] [varchar] (6) null, [week] [varchar] (6) null, [weekend?] [varchar] (4) null, [Day] [int] Null, constraint [pk_dim _ date] primary key clustered ([date id] ASC) with (pad_index = OFF, expiration = OFF, ignore_dup_key = OFF, allow_row_locks = on, allow_page_locks = on) on [primary]) on [primary] Go

Generate data:

Set datefirst 1 -- set the first day of every week on Monday. The default value is 7 declare @ begindate date; select @ begindate = '2017-1-2 '; while @ begindate <= '2014-12-31 'begin insert into [dim _ date] Select convert (varchar (8), @ begindate, 2016) as [date id], year (@ begindate) as [year], case when datepart (month, @ begindate) <= 6 then 'else' second half 'end as [half a year], case when datename (QQ, @ begindate) = '1' then 'q1' when datename (QQ, @ begindate) = '2' then 'q2 'When datename (QQ, @ begindate) = '3' then' Q3 'else' q4' end as [quarter], case when month (@ begindate) = 1 then' February 'when month (@ begindate) = 2 then 'february 'when month (@ begindate) = 3 then' February 'when month (@ begindate) = 4 then' February 'when month (@ begindate) = 5 then 'february 'when month (@ begindate) = 6 then' February 'when month (@ begindate) = 7 then' February 'when month (@ begindate) = 8 then 'february 'when month (@ begindate) = 9 then' February 'when month (@ begindate) = 10 then' February 'when month (@ begindate) = 11 then 'September 11' else' end as [month], case when datepart (Week, @ begindate) <10 then '000000' + convert (varchar (2 ), datepart (Week, @ begindate) + 'Week 'else' nth + convert (varchar (2), datepart (Week, @ begindate )) + 'Week' end as [week], datename (weekday, @ begindate) as [week], case when datepart (DW, @ begindate) = '6' then' weekend 'when datepart (DW, @ begindate) = '7' then' weekend 'else' usual 'end as 'Whether to weekend', Day (@ begindate) as [Day] Set @ begindate = dateadd (day, 1, @ begindate); end;

 

Date dimension (Monday is the first day of each week)