修改SQL Server中的todate维度

Question

我需要一个关于我在SQL Server的Date维度中创建的一列的建议，基本上是滚动周。

我的数据仓库中有一个表dimDate。

我想在dimdate表中创建一个列，该列在任何一年中都有周编号，并且每周应该有7天。

例如: 2015年有53周，但第53周只有5天(我猜是因为在SQL Server中，一周从周日开始)。

我希望从2016年( 2016年1月1日和2日)再增加2天，以完成第53周的7天，而且2016年的第1周应从2016年1月3日开始，以此类推。

如果有任何建议，那将是很好的开始。

Answer 1

假设您已经填充了几周(但不会扩展到下一年)，并对列名进行了一些假设

此查询查找一年中的最后一周(几乎总是53，但不要指望它：)及其结束日期

SELECT YearNo, MAX(Week) As Week, MAX(DateKey) As DateKey 
FROM dimDate
GROUP BY YearNo

此查询查找所有小于7天的周，以及需要多少天才能使它们成为7天。

SELECT 
YearNo, 
Week, 
7-COUNT(DISTINCT DateKey) As ExtraDaysRequired
FROM dimDate
GROUP BY YearNo, Week
HAVING COUNT(DISTINCT DateKey) < 7

这可能永远是一年中的最后一周，但让我们不要做假设。

让我们将它们结合起来，找出所有少于7天的最后一周，并添加所需的天数：

SELECT 
Under7Days.YearNo, Under7Days.Week, Under7Days.ExtraDaysRequired,
FinalWeeks.DateKey StartDate, 
DATEADD(d,Under7Days.ExtraDaysRequired,FinalWeeks.DateKey) EndDate
FROM
(
SELECT YearNo, MAX(Week) As Week, MAX(DateKey) As DateKey 
FROM dimDate
GROUP BY YearNo
) As FinalWeeks
INNER JOIN
(
SELECT YearNo, Week, 7-COUNT(DISTINCT DateKey) As ExtraDaysRequired
FROM dimDate
GROUP BY YearNo, Week
HAVING COUNT(DISTINCT DateKey) < 7
) As Under7Days
ON  FinalWeeks.Week = Under7Days.Week
AND FinalWeeks.YearNo = Under7Days.YearNo

因此，我们有一个查询，它标识了它需要更新到的开始日期、结束日期和周数。所以现在我们运行一个更新：

UPDATE TGT
SET Week = SRC.Week
FROM dimDate TGT
INNER JOIN
(
SELECT 
Under7Days.YearNo, Under7Days.Week, Under7Days.ExtraDaysRequired,
FinalWeeks.DateKey StartDate, 
DATEADD(d,Under7Days.ExtraDaysRequired,FinalWeeks.DateKey) EndDate
FROM
(
SELECT YearNo, MAX(Week) As Week, MAX(DateKey) As DateKey 
FROM dimDate
GROUP BY YearNo
) As FinalWeeks
INNER JOIN
(
SELECT YearNo, Week, 7-COUNT(DISTINCT DateKey) As ExtraDaysRequired
FROM dimDate
GROUP BY YearNo, Week
HAVING COUNT(DISTINCT DateKey) < 7
) As Under7Days
ON  FinalWeeks.Week = Under7Days.Week
AND FinalWeeks.YearNo = Under7Days.YearNo
) SRC
ON TGT.DateID BETWEEN SRC.StartDate AND SRC.EndDate

看起来很复杂？有六种方法可以写出同样的东西，但这种方法是循序渐进的。您可能会编写一个窗口函数来做同样的事情，但我将其留给其他人作为练习。