CODEWITHSUNDEEP
sqlt-sqldate
beta
beta

Reputation: 5666

subtract two days from date ignoring weekends with the help of a view

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

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:

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:

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 ...)

Upvotes: 0

Views: 104

Answers (2)

Orchidoris
Orchidoris

Reputation: 188

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:

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.

Upvotes: 0

Sid
Sid

Reputation: 172

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);

Upvotes: 1

Related Questions