How to get next row value based on previous row excluding week ends in SQL Server?

Question

I have a table as follows: (Expected result without weekend exclude logic)

Start Date	End Date(Expected Date)	No of Days(input)
01-01-2021	02-01-2021	2
03-01-2021	08-01-2021	5
09-01-2021	10-01-2021	2
11-01-2021	20-01-2021	10
21-01-2021	09-02-2021	20
10-02-2021	10-02-2021	1

I want to re-generate the StartDate and EndDate data based on the NumberOfDays values, and the StartDate for subsequent rows based on previous row's EndDate + 1 day and in this sequence, I need to exclude the weekend dates as well, and I have another scenario to include weekend dates based on condition.

I want to apply this logic and select the data in same select query using SQL Server.

This is what I have tried

declare @t table ( StartDate date, EndDate date, DaysToAdd int );

insert into @t(StartDate, EndDate, DaysToAdd)
 values('20210217', '20210227', 10), ('20210312', '20210310', 10), ('20210326', '20210401', 10), ('20210409', '20210401', 10), ('20210507', '20210401', 10), ('20210606', '20210529', 10), ('20210618', '20210417', 3), ('20210620', '20210309', 2), ('20300913', '20210227', 2), (null, '20300914', 4);

select * from @t

select dateadd(day, -DaysToAdd-1+count(*) over(order by isnull(StartDate, EndDate), EndDate) + sum(DaysToAdd) over(order by isnull(StartDate, EndDate), EndDate), min(StartDate) over()) as NewStartDate, dateadd(day, -1+count(*) over(order by isnull(StartDate, EndDate), EndDate) + sum(DaysToAdd) over(order by isnull(StartDate, EndDate), EndDate), min(StartDate) over()) as NewEndDate, * from @t;

My Expected result:

Start Date	End Date(Expected Date)	No of Days(input)
01-01-2021	04-01-2021	2
05-01-2021	11-01-2021	5
12-01-2021	13-01-2021	2
14-01-2021	27-01-2021	10
28-01-2021	24-02-2021	20
25-02-2021	25-02-2021	1

Is there another column that we can Order By? Like a primary key? You're asking to change values of both `StartDate` and `EndDate`. How will you then determine the order in which to process the rows? — navigator, Jan 18 '21 at 09:17
I have another column in Sort order based on this column do the order by. — meena narayanasamy, Jan 18 '21 at 09:26
why there is a row with StartDate = NULL `(null, '20300914', 4)` — Squirrel, Jan 19 '21 at 05:04
@Squirrel, Even the value is Null or not null `startDate` should generate from previous row's `EndDate+1`. We have scenarios where there can be null values in startDate/endDate in the middle, but the data should be generated based on first row's `startDate`. — meena narayanasamy, Jan 19 '21 at 05:16

score 0 · Answer 1 · answered Jan 19 '21 at 05:40

it is best if you have a calendar table

for the solution, i create a simple calendar table

create table calendar
(
    CalDate      date,
    isWeekEnd    bit
);

then populate it with dates

with rcte as
(
    select CalDate = convert(date, '2021-01-01')
    union all
    select CalDate = dateadd(day, 1, CalDate)
    from   rcte
    where  CalDate <= '2021-12-30'
)
insert into calendar (CalDate, isWeekEnd)
select CalDate, 
       case when left(datename(weekday, CalDate), 3) in ('Sat', 'Sun') then 1 else 0 end
from   rcte
option (maxrecursion 0)

your sample table & data

declare @t table (id int identity, StartDate date, EndDate date, DaysToAdd int );

insert into @t(StartDate, EndDate, DaysToAdd)
values('2021-01-01', '2021-01-02', 2), 
      ('2021-01-03', '2021-01-08', 5), 
      ('2021-01-09', '2021-01-10', 2), 
      ('2021-01-11', '2021-01-20', 10), 
      ('2021-01-21', '2021-02-09', 20), 
      ('2021-02-10', '2021-02-10', 1);

Since you only interested in the StartDate of first row, I select it into a variable

The actual query

declare @StartDate date;

select @StartDate = StartDate
from   @t
where  id = 1;

with 
cal as
(
    select CalDate, rn = row_number() over (order by CalDate)
    from   Calendar
    where  CalDate  >= @StartDate
    and    isWeekEnd = 0
),
t as
(
    select t.id, t.DaysToAdd,
           s = sum(t.DaysToAdd) over (order by t.id) - t.DaysToAdd + 1,
           e = sum(t.DaysToAdd) over (order by t.id)
    from   @t t
)
select t.id, 
       t.DaysToAdd,
       StartDate = s.CalDate,
       EndDate   = e.CalDate
from   t
       inner join cal s on t.s = s.rn
       inner join cal e on t.e = e.rn
order by t.id

db<>fiddle demo

Thanks @Squirrel, It gives the expected result, But in our case we need to achieve this without `calendar` table, here the start will be dynamic and in some cases we may have contract for 2 years. So We have to check whether Weekend while calculating `StartDate/EndDate`. Is there any direct solution for this? Thanks in advance...! — meena narayanasamy, Jan 19 '21 at 07:49
If you are dealing with dates and weekends, you should seriously consider having a calendar table. It make things much easier. If for whatever reason you really can't have a calendar table, you can generate it on the fly in CTE based on the `min (date)` and `max(date)` — Squirrel, Jan 19 '21 at 08:08

How to get next row value based on previous row excluding week ends in SQL Server?

1 Answers1