先感叹一句!好长时间没有更新博客了!偶尔看到一句话,觉得被电击了 - 庸人败于懒,能人败于傲!
-- 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 --
SELECT DATENAME(WEEK,'2014-01-01') AS WeekName --
SELECT DATENAME(WEEK,'2014-01-05') AS WeekName -- -- Change the DATEFIRST to 1, Monday will be the first day of week.
SET DATEFIRST 1 SELECT @@DATEFIRST -- -- After change the DATEFIRST to Monday
SELECT DATENAME(WEEK,'2013-12-31') AS WeekName --
SELECT DATENAME(WEEK,'2014-01-01') AS WeekName --
SELECT DATENAME(WEEK,'2014-01-05') AS WeekName --
要注意的是 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 <= @EndDate)
BEGIN
INSERT INTO DimDateStartWithMonday
(
DateKey,
FullDate,
[DateName],
DayNumberOfWeek,
DayNameOfWeek,
DayNumberOfMonth,
DayNumberOfYear,
WeekNumberOfYear,
EnglishMonthName,
MonthNumberOfYear,
CalendarQuarter,
CalendarSemester,
CalendarYear
)
SELECT CAST(CONVERT(VARCHAR(8),@StartDate,112) AS INT) AS DateKey,
CONVERT(VARCHAR(10), @StartDate,20) AS FullDate,
CONVERT(VARCHAR(20), @StartDate,106) AS [DateName],
DATEPART(DW,@StartDate) AS DayNumberOfWeek,
DATENAME(DW,@StartDate) AS DayNameOfWeek,
DATENAME(DD,@StartDate) AS [DayOfMonth],
DATENAME(DY,@StartDate) AS [DayOfYear],
DATEPART(WW,@StartDate) AS WeekNumberOfYear,
DATENAME(MM,@StartDate) AS EnglishMonthName,
DATEPART(MM,@StartDate) AS MonthNumberOfYear,
DATEPART(QQ,@StartDate) AS CalendarQuarter,
CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6
THEN 1
ELSE 2
END AS CalendarSemester,
DATEPART(YY,@StartDate) AS CalendarYear SET @StartDate = @StartDate + 1
END
GO
最后是函数,这个函数麻烦的地方就是要考虑周日的情况。默认情况下,周日是每个星期的第一天,但是这里改成了周一是每周的第一天,逻辑上就会复杂很多。
比如,2012-01-01 是周日,2012-01-02 是周一。按默认情况,这两天的 Week Number 都是 1,但是这里需要把 2012-01-02 的 Week Number 变成 2。
比如,2011-01-01 是周六,2012-01-02 是周日。按默认情况,周六的 Week Number 是 1, 周日的是 2。但是这里需要把周六和周日的都变成 1, 周一的变成 2。
除此之外,还要考虑之后的每一个周日与周一的交替情况。
USE BIWORK_SSIS
GO IF OBJECT_ID('ETLWORK_GETWEEKNUMBER','FN') IS NOT NULL
DROP FUNCTION ETLWORK_GETWEEKNUMBER
GO CREATE FUNCTION ETLWORK_GETWEEKNUMBER(@DATE DATETIME)
RETURNS INTEGER
AS
BEGIN DECLARE @FIRST_DATE_OF_YEAR DATETIME = DATEADD(YYYY,DATEDIFF(YYYY,0,@DATE),0)
-- DECLARE @MONDAY_OF_WEEK DATETIME = DATEADD(WK,DATEDIFF(WK,0,@DATE),0)
-- DECLARE @PREVIOUS_DATE DATETIME = DATEADD(DAY,-1,@DATE)
DECLARE @WEEK_NUMBER INTEGER -- 如果当前时间是当前年的第一天
IF @DATE = @FIRST_DATE_OF_YEAR
SET @WEEK_NUMBER = 1
-- 星期天是年第一天的情况
ELSE IF (DATEPART(WEEKDAY,@DATE) = 1 AND DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,@DATE)/7 + 1 = DATEPART(WEEK,@DATE))
SET @WEEK_NUMBER = DATEPART(WEEK,@DATE)
-- 星期天不是年第一天的情况
ELSE IF (DATEPART(WEEKDAY,@DATE) = 1 AND DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,@DATE)/7 + 1 <> DATEPART(WEEK,@DATE))
SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) - 1
-- 如果当前天的上一个周日小于年第一天
ELSE IF DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0)) < @FIRST_DATE_OF_YEAR
SET @WEEK_NUMBER = 1
-- 当前天前面的一个周日正好是以周日为开始年的 7 倍的天数
ELSE IF DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0) ))/7 + 1 = DATEPART(WEEK,@DATE)
SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) + 1
ELSE
SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) RETURN @WEEK_NUMBER
END
GO
为了方便理解,可以查看下面的查询。
DECLARE @DATE DATETIME = '2012-01-29'
DECLARE @FIRST_DATE_OF_YEAR DATETIME = DATEADD(YYYY,DATEDIFF(YYYY,0,@DATE),0) SELECT DATEPART(WEEK,@DATE), -- 一年中的周数,默认以周日开始
DATEADD(WK,DATEDIFF(WK,0,@DATE),0), -- 当前周的周一,默认从周日开始,但是仍然找周一
DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0)), -- 当前周先找周一,然后往前一天找到周日
DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0))), -- 当前天离年第一天的间隔
DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0) ))/7 + 1 -- 按天计算的周数
测试一下,查找不匹配的 Week Number - 30多年的数据结果都匹配,记得要新开一个页面,以免之前 SET DATEFIRST 的影响。
查询部分数据的 WeekNumber。
当然,我感觉写的还是有点复杂,谁解决过类似问题的,期望有人能提出更简洁的写法。
更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)
如果觉得这篇文章看了对您有帮助,请帮助推荐,以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。