借助视图从日期中减去两天，忽略周末

I have a view called DateView that acts as a calendar. The structure is as follows:

我有一个名为DateView的视图，它充当日历。结构如下：

Date     | Date_Previous | Date_Following | Weekday
20170314 | 20170313      | 20170315       | Tu
...      | ...           | ...            | ...

I have a given date and (with the help of this view) need to substract two days from the given date while ignoring Saturday and Sunday.

我有一个给定的日期和（在此视图的帮助下）需要从给定日期减去两天而忽略星期六和星期日。

My current approach for a test-date 20170314 looks like this:

我目前测试日期20170314的方法如下所示：

SELECT TOP 2 Date 
FROM DateView d
WHERE d.Weekday NOT IN ('Sa', 'So')
    AND d.Date < '20170314'
ORDER BY d.Date DESC

The result is this:

结果如下：

Date
2017-03-13
2017-03-10

However, I only need to get the second row, that is, 2017-03-10. Hence, I tried to invert the sort order by changing ORDER BY d.Date DESC to ORDER BY d.date ASC, but this yields the following result, because the View starts at 1990-01-01:

但是，我只需要获得第二行，即2017-03-10。因此，我尝试通过将ORDER BY d.Date DESC更改为ORDER BY d.date ASC来反转排序顺序，但这会产生以下结果，因为View从1990-01-01开始：

Date
1990-01-01
1990-01-02

How can I achieve to only select the second row from the first results shown above? Ideally, the solution does not include ROW_NUMBER() over (order by ...)

如何从上面显示的第一个结果中选择第二行？理想情况下，解决方案不包括ROW_NUMBER（）（按...排序）

2 个解决方案

#1

How about :-

怎么样：-

Select min(Date) from 
(SELECT TOP 2 Date 
FROM DateView d
WHERE d.Weekday NOT IN ('Sa', 'So')
    AND d.Date < '20170314'
ORDER BY d.Date DESC);

#2

It's quite simple for 2 days when you need to take only SUN and SAT into account. You can achieve the goal without the DateView:

当您需要考虑SUN和SAT时，2天非常简单。您可以在没有DateView的情况下实现目标：

DECLARE @Date DATETIME = '2017-03-14'

SELECT DATEADD(day,
    CASE DATENAME(weekday, @Date)
       WHEN 'TUESDAY'    THEN -4
       WHEN 'WEDNESDAY'  THEN -4
       WHEN 'SUNDAY'     THEN -3
       ELSE -2
    END, @Date) AS TwoBusinessDaysSubstractedDate

I also could provide a more generic solution that accepts more than 7 days ranges and considers statutory holidays as well as weekends.

我还可以提供一个更通用的解决方案，接受超过7天的范围，并考虑法定假日和周末。

#1