计算每个月的第八个工作日

Question

如何获得第八个业务日，开始检查数据是否存在。如果确实存在并表示贷款的50％以上 - 将验证日期设置为当天

谢谢Shehroz

Answer 1

Declare @D date = '2012-12-01'   -- Supply 1st of Month
Select D=max(D) 
 From (
        Select Top 8 D=DateAdd(DD,N,@D) 
         From (Select N From (Values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)) N(N) ) A
         Where DatePart(DW,DateAdd(DD,N,@D)) between 2 and 6 
       ) A

返回

2012-12-12

Answer 2

这将为您带来结果，但是我的答案列出了特定日期以来的所有第8个工作日。

它不考虑假期。随着微小的更改，您也可以考虑假期。

您可以在最后一个select上使用top 1获取一个日期。

DECLARE @start date
SELECT @start = '20160101'
;WITH n AS (
    SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
    FROM sys.all_objects
), dates AS (
    SELECT DATEADD(DAY, n - 1, @start) Dt
    FROM n
), dayNum AS (
    SELECT Dt, DATENAME(WEEKDAY, Dt) WeekDayName
           , ROW_NUMBER() OVER (ORDER BY Dt) DayNumber
    FROM dates
    WHERE DATENAME(WEEKDAY, Dt) NOT IN ('Saturday', 'Sunday')
)
SELECT Dt, DATENAME(WEEKDAY, Dt) WeekDayName
FROM dayNum
WHERE DayNumber % 8 = 0
ORDER BY Dt