（針對 SQL 語言的問題，用這個標題。請用 Ctrl+Y 砍掉這行）
資料庫名稱： SQL SERVER
資料庫版本：SQL SERVER 2008
內容/問題描述：因為周報告是以ISO WEEK為主 常需要對周加加減減
目前想到的方法是用一個calendar table 相對應所有的ISO WEEK
再用function去取得ISO日期範圍
如下
1.請問function裡不能用CTE有其他比較好的解法嗎? (因為是2008不能用FIRST_VALUE)
2.還是有其他更簡單的解法@@
Table
Date, ISOWeek, ISOYear
Constraint PK_date PRMARY KEY CLUSTERED([Date])
Create function fnGetISOCalendarWeek(@input_date datetime2)
Returns @DateRange Table (
week_start_date date,
week_end_date date
)
As
Begin
Declare @week_start_date date;
Declare @week_end_date date;
set @week_start_date =
(select [Date] from (select
[Date],
ISOWeek,
rank()over(PARTITION BY ISOWeek Order by [Date]) As rn
from GM.DimCalendar where ISOWeek =(
select ISOWeek from DimCalendar where [Date] = Cast(@input_date As Date)))
t where rn = 1);
set @week_end_date =
(select [Date] from (select
[Date],
ISOWeek,
rank()over(PARTITION BY ISOWeek Order by [Date]) As rn
from GM.DimCalendar where ISOWeek =(
select ISOWeek from DimCalendar where [Date] = Cast(@input_date As Date)))
t where rn = 7);
insert @DateRange
Values(@week_start_date, @week_end_date)
Return
End