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.