Keep last n business days records from today date in SQL Server

Question

How can we keep last n business days records from today date in this table:

Suppose n = 7

Sample Data:

Table1:

Date
----------
2021-11-29
2021-11-30
2021-12-01
2021-12-02
2021-12-03
2021-12-04
2021-12-05
2021-12-06
2021-12-07
2021-12-08
2021-12-09
2021-12-10
2021-12-11
2021-12-12
2021-12-13

Based on this table data we want output like below. It should delete all the rows before the 03-Dec or data for last 7 business days.

Date
-------
2021-12-03
2021-12-06
2021-12-07
2021-12-08
2021-12-09
2021-12-10
2021-12-13

Note: It's fine if we keep data for Saturday, Sunday in between business days.

I tried this query

DECLARE @n INT = 7

SELECT * FROM Table1
WHERE  [date] < Dateadd(day, -((@n + (@n / 5) * 2)), Getdate())

but Saturday, Sunday logic doesn't fit here with my logic. Please suggest better approach.

Answer 1

You can get the 7th working day from today as

  select top(1) cast(dateadd(d, -n + 1, getdate()) as date) d
  from (
    select  n
       , sum (case when datename(dw, dateadd(d, -n + 1, getdate())) not in ('Sunday',  'Saturday') then 1 end) over(order by n) wdn
    from (
       values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)
    )t0(n)
  ) t
  where wdn = 7
  order by n;

Generally using on-the-fly tally for a @n -th day

declare @n int = 24;

with t0(n) as (
  select n 
  from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
  ) t(n)
), tally as (
     select top(@n + (@n/5 +1)*2) row_number() over(order by t1.n)  n
     from t0 t1, t0 t2, t0 t3
)
select top(1) cast(dateadd(d, -n + 1, getdate()) as date) d
from (
  select  n
     , sum (case when datename(dw, dateadd(d, -n + 1, getdate())) not in ('Sunday',  'Saturday') then 1 end) over(order by n) wdn
  from  tally
) t
where wdn = @n
order by n;