function - 根据周开始和周结束日期生成周历

Question

我正在尝试创建一个脚本，该脚本将根据开始周和结束周的参考日期为我提供财政周

例如，假设我有 StartWeek 和 EndWeek 的表列，这样：

StartWeek | EndWeek
----------|---------
20190603  | 20190609
20190610  | 20190616
20190617  | 20190623
20190624  | 20190630
20190701  | 20190707
...

我想要一个脚本来生成以下内容：

FiscalDateStart | FiscalDateEnd  |  NumOfDays | WeekDayStart | WeekDayEnd
----------------|----------------|------------|--------------|-----------
2019-06-05      | 2019-06-09     |  5         | Wednesday    | Sunday
2019-06-10      | 2019-06-16     |  7         | Monday       | Sunday
2019-06-17      | 2019-06-23     |  7         | Monday       | Sunday
2019-06-24      | 2019-06-30     |  7         | Monday       | Sunday
2019-07-01      | 2019-07-02     |  2         | Monday       | Tuesday

我在 Stack 上找到了以下逻辑：

DECLARE   @StartDate DATE = '2019-06-05'
        , @EndDate DATE = '2019-07-02'

SET DATEFIRST 1
SET LANGUAGE French;
SET NOCOUNT ON;

select *
    from
(Select 
       WeekNum   = Dense_Rank() over (Partition By FY,FM Order By FW)
      ,StartDate = Min(D) over (Partition By FY,FM,FW )
      ,EndDate   = Max(D) over (Partition By FY,FM,FW )
      ,NumOfDays = DATEDIFF(DAY, Min(D) over (Partition By FY,FM,FW ), Max(D) over (Partition By FY,FM,FW )) + 1
 From 
      (
         /* Establish calendar rules/functions for Fiscal Year (FY), Fiscal Month (FM), Fiscal Week (FW), & Fiscal Day (D) */
        Select FY = DatePart(Year,@StartDate)-1+sum(case when convert(varchar(5),@StartDate,101)=convert(varchar(5),D,101) then 1 else 0 end) over (Order By D)
              ,FM = sum(case when DatePart(Day,D)=DatePart(Day,@StartDate) then 1 else 0 end) over (Order By D)
              ,FW = sum(case when DatePart(WeekDay,D)=1 then 1 else 0 end) over (Order By D)
              ,D
         From (
                /* Generate Fiscal Day (D) rule based on @StartDate & @EndDate  */
                Select Top (DateDiff(DAY,@StartDate,@EndDate)+1) 
                       D  = DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@StartDate) 
                 From  master..spt_values n1,master..spt_values n2
              ) A1
      ) A

  --Order By D
 ) a
 group by 
 WeekNum, StartDate, EndDate, NumOfDays

问题是我们强制上述查询中的 @StartDate 为 2019-06-05。但是，我需要上面的脚本来生成相同的结果，但基于截至 2019 年 6 月 3 日的@StartDate。

任何帮助，将不胜感激。

标签： functioncalendarsql-server-2017dayofweek