Skip to content
Advertisement

How to increment date using previous value from same column and skip weekend date?

I have tried to increment date based on other date column and skip weekend date.

SELECT DATENAME(dw,GETDATE()) -- Friday
SELECT DATEPART(dw,GETDATE()) -- 6

I know how to check weekend and how to skip date but I want to result as look like below

EX. If I will input date in one column like

OriginalDate    
4 October 2019 
5 October 2019
6 October 2019
7 October 2019
8 October 2019
9 October 2019
10 October 2019
11 October 2019
12 October 2019

then I want result in new column like

OriginalDate        UpdatedDate
4 October 2019      4 October 2019 
5 October 2019      7 October 2019 (skip weekend)
6 October 2019      8 October 2019
7 October 2019      9 October 2019
8 October 2019      10 October 2019
9 October 2019      11 October 2019
10 October 2019     14 October 2019 (skip weekend)
11 October 2019     15 October 2019
12 October 2019     16 October 2019

NOTE: don’t skip any row from input date

Advertisement

Answer

This seems quite tricky. I think my best advice is to reconstruct the data. A relatively simple method uses a recursive CTE:

with cte as (
      select min(originaldate) as dte, count(*) as cnt, min(originaldate) as originaldate
      from t
      union all
      select (case when datename(weekday, v.nextdate) = 'Saturday' then dateadd(day, 2, nextdate)
                   when datename(weekday, v.nextdate) = 'Sunday' then dateadd(day, 1, nextdate)
                   else v.nextdate
              end),
             cnt - 1, dateadd(day, 1, originaldate)

      from cte cross apply
           (values (dateadd(day, 1, dte))) v(nextdate)
      where cnt > 0
     )
select originaldate, dte
from cte
order by originaldate;

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement