0

I have a table as follows,

enter image description here

here I want to re-generate the StartDate and EndDate data based on the NumberOfDaystoAdd values, and the StartDate for subsequent rows based on previous row's EndDate + 1day and in this sequence I need to exclude the weekend dates.

For Example, Here the 1st row values correct, second row startdate should be 2021-03-28 and EndDate should be 2021-03-10.

I want to apply the logic and select the data in same select query using sql server.

Expected result as follows,

enter image description here

Example data:

Dennis Xavier
  • 101
  • 1
  • 14
  • Post the input data, desired and current results as *text*. Images can't be copied and used to test queries. Since you already used the `lag` and `lead` tags, have you tried using those methods? – Panagiotis Kanavos Jan 11 '21 at 10:30
  • How are you defining next/previous? Is it based purely on the `StartDate` values? And there are no duplicate values currently in that column? – Damien_The_Unbeliever Jan 11 '21 at 10:45
  • Yes @Damien_The_Unbeliever I have a table with wrong data as in the first Image, I want to regenerate as the 2nd Image. – Dennis Xavier Jan 11 '21 at 10:49
  • @PanagiotisKanavos I have tried with `lag` methods but I couldn't get the exact result I want. – Dennis Xavier Jan 11 '21 at 10:51

1 Answers1

1
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;
lptr
  • 1
  • 2
  • 6
  • 16
  • Thank you @lptr this solution is working fine. But we need to exclude the weekends and holidays (from holiday table). Is there any solution? Thanks..! – Dennis Xavier Jan 12 '21 at 07:54