如何从WeekNumber和WeekDay获取数据

Question

我有这个数据集与WeekNo (年份和周)- WeekDayName，这是有数据的日子。我要知道具体日期是什么时候。

WeekNo  WeekDayName
------  -----------
201905  Mandag
201905  Mandag
201905  Mandag
201905  Tirsdag
201905  Tirsdag
201905  Tirsdag
201905  Onsdag
201905  Onsdag
201905  Onsdag
201905  Torsdag
201905  Torsdag
201905  Torsdag
201905  Fredag
201905  Fredag
201905  Fredag

预期结果：

WeekNo  WeekDayName Date
------  ----------- -----
201905  Mandag      2019-01-28
201905  Mandag      2019-01-28
201905  Mandag      2019-01-28
201905  Tirsdag     2019-01-29
201905  Tirsdag     2019-01-29
201905  Tirsdag     2019-01-29
201905  Onsdag      2019-01-30
201905  Onsdag      2019-01-30
201905  Onsdag      2019-01-30
201905  Torsdag     2019-01-31
201905  Torsdag     2019-01-31
201905  Torsdag     2019-01-31
201905  Fredag      2019-02-01
201905  Fredag      2019-02-01
201905  Fredag      2019-02-01

我试过了，但它只给了我一个星期的开始：

declare @IntWeek as varchar(20) SET @IntWeek = '201905'

SELECT DATEADD(wk, DATEDIFF(wk, @@DATEFIRST, LEFT(@IntWeek,4)+'-01-01') + (cast(RIGHT(@IntWeek,2) as int)-1), @@DATEFIRST) AS StartOfWeek

Answer 1

你可以考虑让Calendar Table

在那之后，你可以很容易地得到结果。

select date 
from Auxiliary.Calendar 
where Year = CAST(LEFT(@IntWeek,4) as int)
  and Week = CAST(RIGHT(@IntWeek,2) as int)
  and WeekDay = 'Mondag'

Answer 2

创建一个接收两个参数的函数：

1.-你一周的第一天 2.-日期名称

The function must do IF

IF dayName = 'Sunday'
  Return same date
Else If dayName = 'Monday
  Return Data + 1 day

……