I have tried to increment date based on other date column and skip weekend date.
x
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.