SQL Server

ycvodzf

　　先感叹一句！好长时间没有更新博客了！偶尔看到一句话，觉得被电击了 - 庸人败于懒，能人败于傲！


因此，不能再懒惰了！



今天想写一个有关计算 Week Number 的函数，刚开始觉得应该很简单，凭着感觉七写八写到最后发现越写越乱，到最后搞了快两个小时以为解决了，结果一测还有好多数据不正确。非常有挫败感！感觉很不服气，觉得很丢人，跑出去站了会，冷静下来，重新拿起纸笔认真的分析了一下，连写到测试快半个小时还是解决了。



在 SQL Server 中默认情况下，每周的开始都是从周日开始算起的。但是在国内也有不一样的要求，比如按照习惯往往要求每周从周一算起。这样一来之前在数据仓库中的 Week Number 可能就不准确了，因为可能很多时候都是按默认方式来生成 Week Number 的。



这里有三种方式可以解决这个问题：

第一种方式是直接通过 SET DATEFIRST VALUE 来更改重新生成新的 DimDate，然后每次需要单独计算 Week Number 的时候根据 Date Key 关联一下就可以了，但这样就需要不断 JOIN DimDate，每一条记录都要 LookUp 一遍，不太好。

第二种方式就是在存储过程中需要使用到  Week Number 的时候，就先设置一下 SET DATEFIRST 然后在使用 DATEPART() 函数来获取 Week Number。这种方式需要每次都显示的  SET DATEFIRST ，不太方便。

第三种方式就是直接写一个函数，每次调用一下就可以了。



关于使用 SET DATEFIRST  -  的值从 1 到 7，即周一到周日，默认值是 7。








-- The default first date in a week is Sunday, the value is 7
SELECT @@DATEFIRST  
-- Default DATEFIRST is Sunday
SELECT DATENAME(WEEK,'2013-12-31') AS WeekName  -- 53
SELECT DATENAME(WEEK,'2014-01-01') AS WeekName  -- 1
SELECT DATENAME(WEEK,'2014-01-05') AS WeekName  -- 2
-- Change the DATEFIRST to 1, Monday will be the first day of week.
SET DATEFIRST 1
SELECT @@DATEFIRST  -- 1
-- After change the DATEFIRST to Monday
SELECT DATENAME(WEEK,'2013-12-31') AS WeekName  -- 53
SELECT DATENAME(WEEK,'2014-01-01') AS WeekName  -- 1
SELECT DATENAME(WEEK,'2014-01-05') AS WeekName  -- 1
　　要注意的是 SET DATEFIRST 只在当前执行中有效，也就说比如新开一个查询页面继续查询 SELECT @@DATEFIRST 则还是显示默认值 7。
　　在创建时间维度的代码中添加 SET DATEFIRST 1，表示每周以周一开始。





USE BIWORK_SSIS
GO  
SET NOCOUNT ON
-- 设置每周的起始天为周一
SET DATEFIRST 1
IF OBJECT_ID('DimDateStartWithMonday','U') IS NOT NULL
DROP TABLE DimDateStartWithMonday
GO
CREATE TABLE DimDateStartWithMonday
(
DateKey INT PRIMARY KEY,
FullDate DATE NOT NULL,
[DateName] NVARCHAR(20),
DayNumberOfWeek TINYINT NOT NULL,
DayNameOfWeek NVARCHAR(10) NOT NULL,
DayNumberOfMonth TINYINT NOT NULL,
DayNumberOfYear SMALLINT NOT NULL,
WeekNumberOfYear TINYINT NOT NULL,
EnglishMonthName NVARCHAR(10) NOT NULL,
MonthNumberOfYear TINYINT NOT NULL,
CalendarQuarter TINYINT NOT NULL,
CalendarSemester TINYINT NOT NULL,
CalendarYear SMALLINT NOT NULL
)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SELECT @StartDate = '2001-01-01',
@EndDate = '2035-12-31'
WHILE (@StartDate