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

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.

CodePudding user response：

You can use CTE to mark target dates and then delete all the others from the table as follows:

; With CTE As (
Select [Date], Row_number() Over (Order by [Date] Desc) As Num
From tbl 
Where DATEPART(weekday, [Date]) Not In (6,7)
)
Delete From tbl
Where [Date] Not In (Select [Date] From CTE Where Num<=7)

If the number of business days in the table may be less than 7 and you need to bring the total number of days to 7 by adding days off, try this:

; With CTE As (
Select [Date], IIF(DATEPART(weekday, [Date]) In (6,7), 0, 1) As IsBusinessDay  
From tbl
)
Delete From tbl
Where [Date] Not In (Select Top 7 [Date] 
                     From CTE 
                     Order by IsBusinessDay Desc, [Date] Desc)

CodePudding user response：

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;

CodePudding user response：

If there is only one date for each day, you can simply do this:

SELECT TOP 7 [Date] FROM Table1 
WHERE 
  [Date] < GETDATE() AND DATENAME(weekday, [DATE]) NOT IN ('Saturday', 'Sunday')
ORDER BY
  [DATE] DESC