sql - 根据日期和月份的出现生成日期列表

Question

我想根据星期几和日期在月份中出现的次数生成两个日期之间每个月的日期列表。假设我有一个 @StartDate = 2016/04/01 和 @EndDate = 2016/09/01，我检查 @StartDate 是否开启四月的第一个星期五，然后到 @EndDate 将为每个月的所有第一个星期五创建日期:

2016/05/06
2016/06/03
2016/07/01
2016/08/05

如果 @StartDate = 2016/04/12 和 @EndDate = 2016/09/01，我注意到 @StartDate是四月的第二个星期二，然后去获取每个月的每个第二个星期二星期二:

2016/05/10
2016/06/14
2016/07/12
2016/08/09

如果@StartDate = 2016/04/28 和@EndDate = 2016/09/01，我注意到@StartDate是四月的最后一个星期四:

2016/05/26
2016/06/30
2016/07/28
2016/08/25

在最后一种情况下，我需要验证每个月的周数，因为存在只有 4 周或 5 周的月份，我想要最后一次出现。

我做了什么？我找到了一个代码，该代码在每个月的第三周的每个星期一给我，我采用了一些代码来获得 @StartDate 和 @EndDate:

;with  
 filler as (select row_number() over (order by a) a from (select top 100 1 as a from syscolumns) a cross join (select top 100 1 as b from syscolumns) b),
 dates as (select dateadd(month, a-1, @StartDate ) date from filler where a <= 1000 and dateadd(month, a-1, @StartDate) < @EndDate),
 FirstMonday as (
 select dateadd(day, case   datepart(weekday,Date) /*this is the case where verify the week day*/
                            when 1 then 1 
                            when 2 then 0
                            when 3 then 6
                            when 4 then 5 
                            when 5 then 4 
                            when 6 then 3
                            when 7 then 2
                        end, Date) as Date 
        ,case when datepart(weekday, @StartDate) = 1 then 3 else 2 end as Weeks /*here i verify the number of weeks to sum in the next date*/
 from   dates
 )
 select dateadd(week, Weeks, Date) as ThirdMonday
 from FirstMonday

Answer 1

所以，它是:

set @NumSemana = datepart(day, datediff(day, DATEADD(mm, DATEDIFF(mm,0,@StartDate), 0), @StartDate)/7 * 7)/7 + 1;
WITH    AllDays
  AS ( SELECT  @StartDate  AS [Date], DATEPART(month, @StartDate) as validMonth
       UNION ALL
       SELECT   DATEADD(week, 1, [Date]), 
                iif(DATEPART(month,DATEADD(week, 1, [Date])) < validMonth + @PeriodicityRepeat, validMonth, validMonth + @PeriodicityRepeat)
       FROM     AllDays
       WHERE    
            DATEPART(month,[Date]) <= DATEPART(month,@EndDate)
        and DATEPART(year,[Date]) <= DATEPART(year,@EndDate)
            ),
rankedDays 
  AS(     
    SELECT [Date], validMonth, 
           row_number() over ( partition by DATEPART( month, [Date]) order by [Date]) ascOrder,
           row_number() over ( partition by DATEPART( month, [Date]) order by [Date] desc) descOrder
    FROM   AllDays 
    WHERE DATEPART(month, [Date]) = validMonth
)
select [Date]
from rankedDays
where ((ascOrder = @NumSemana and @NumSemana <=4 )  
        or (descOrder = 1 and @NumSemana = 5) 
        or [Date] = @StartDate )
   and [Date] < @EndDate 
OPTION (MAXRECURSION 0)