我想在日期维度中添加一列，其中包含该周第一天的日期。这是它看起来的一部分
CalendarDate DayOfWeekNumber DayKey DayOfWeekName WeekNumber
1996-01-01 2 19960101 MONDAY 1
1996-01-02 3 19960102 TUESDAY 1
1996-01-03 4 19960103 WEDNESDAY 1
1996-01-04 5 19960104 THURSDAY 1
1996-01-05 6 19960105 FRIDAY 1
1996-01-06 7 19960106 SATURDAY 1
1996-01-07 1 19960107 SUNDAY 1
1996-01-08 2 19960108 MONDAY 2
1996-01-09 3 19960109 TUESDAY 2
1996-01-10 4 19960110 WEDNESDAY 2
1996-01-11 5 19960111 THURSDAY 2
1996-01-12 6 19960112 FRIDAY 2
1996-01-13 7 19960113 SATURDAY 2
1996-01-14 1 19960114 SUNDAY 2
所以基本上我想要一个列WeekStartDate，每个列WeekNumber都具有CalendarDate第一个dayOfWeekNumber（dayOfWeekNumber = 1）
看起来像
CalendarDate DayOfWeekNumber DayKey DayOfWeekName WeekNumber WeekStart
1996-01-01 2 19960101 MONDAY 1 1996-01-01
1996-01-02 3 19960102 TUESDAY 1 1996-01-01
1996-01-03 4 19960103 WEDNESDAY 1 1996-01-01
1996-01-04 5 19960104 THURSDAY 1 "
1996-01-05 6 19960105 FRIDAY 1 "
1996-01-06 7 19960106 SATURDAY 1 "
1996-01-07 1 19960107 SUNDAY 1 "
1996-01-08 2 19960108 MONDAY 2 1996-01-08
1996-01-09 3 19960109 TUESDAY 2 "
1996-01-10 4 19960110 WEDNESDAY 2 "
1996-01-11 5 19960111 THURSDAY 2 "
1996-01-12 6 19960112 FRIDAY 2
1996-01-13 7 19960113 SATURDAY 2
1996-01-14 1 19960114 SUNDAY 2
所以像
update myTable set WeekStartDate = CalendarDate where dayofweeknumber=2 (monday) for each weeknumber （伪代码，我知道这不是我想要它做的事情）。
感谢您的建议。可以肯定，我需要查看周号。
UPDATE C
SET WeekStartDate = week_start
FROM
(
select *, week_start = min(CalendarDate)
over (partition by year(CalendarDate), WeekNumber)
from Calendar
) AS C
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句