（针对 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