如何在每个月的列中获取最多周六日期，而无需硬编码 [英] How to get max Saturday dates in a column of each month, without hardcoding

问题描述

如何在SQL Server的每个月的列中获取最大星期六日期。有人能帮帮我吗？

现在我只需要上月最后一个星期六的日期。 例如，

该表具有

07-08-2021 - Saturday
14-08-2021 - Saturday
21-08-2021 - Saturday
28-08-2021 - Saturday

04-09-2021 - Saturday
11-09-2021 - Saturday
18-09-2021 - Saturday
25-09-2021 - Saturday

假设我们在8月份，我需要选择该月的最后一个星期六(只有28-08-2021) 假设我们在9月份，我需要选择该月的最后一个星期六(仅25-09-2021)

输出：

28-08-2021
25-09-2021

推荐答案

以下是计算第N个工作日的函数-可以从月初或月底计算。

  Alter Function dbo.fnGetNthWeekDay (
        @theDate datetime
      , @theWeekday int
      , @theNthDay int
        )
Returns Table
     As
 Return 

/* 
   Adapted from a version published by Peter Larrson - with minor modifications for performance
   and restructured to eliminate usage of a derived table.

   Inputs:
        @theDate        any date in the month
        @theWeekday     the weekday to calculate: 1 = Monday
                                                  2 = Tuesday
                                                  3 = Wednesday
                                                  4 = Thursday
                                                  5 = Friday
                                                  6 = Saturday
                                                  7 = Sunday
        @theNthDay      the week count where positive is from beginning of the month
                        and negative is from end of month

   Outputs:
        @theDate        the date entered
        @theNthDate     the Nth date of the month
*/

 Select theDate = @theDate
      , dt.nthDate
   From (Values (dateadd(month, datediff(month, @theNthDay, @theDate), 0)))                             As mm(FirstOfMonth)
  Cross Apply (Values (dateadd(day, 7 * @theNthDay - 7 * sign(@theNthDay + 1)
            + (@theWeekday + 6 - datediff(day, -53690, mm.FirstOfMonth) % 7) % 7, mm.FirstOfMonth)))    As dt(nthDate)
  Where @theWeekday Between 1 And 7
    And datediff(month, dt.nthDate, @theDate) = 0
    And @theNthDay In (-5, -4, -3, -2, -1, 1, 2, 3, 4, 5);
Go

然后您可以这样称呼它：

Select * From dbo.fnGetNthWeekDay('2021-08-15', 6, -1) nwd

这篇关于如何在每个月的列中获取最多周六日期，而无需硬编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！