sql-server - 如何根据前一行获取下一行值，不包括 SQL Server 中的周末？

Question

我有一个如下表：（没有周末排除逻辑的预期结果）

开始日期	结束日期（预计日期）	天数（输入）
01-01-2021	02-01-2021	2
03-01-2021	08-01-2021	5
09-01-2021	2021 年 10 月 1 日	2
2021 年 11 月 1 日	20-01-2021	10
21-01-2021	2021 年 9 月 2 日	20
2021 年 10 月 2 日	2021 年 10 月 2 日	1

我想根据值重新生成StartDateandEndDate数据NumberOfDays，并且StartDate对于基于前一行的后续行EndDate + 1 day，在这个序列中，我还需要排除周末日期，我还有另一种情况来根据条件包括周末日期.

我想应用此逻辑并使用 SQL Server 在同一选择查询中选择数据。

这是我尝试过的

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;

我的预期结果：

开始日期	结束日期（预计日期）	天数（输入）
01-01-2021	04-01-2021	2
05-01-2021	2021 年 11 月 1 日	5
2021 年 12 月 1 日	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

标签： sql-serversortinglead

Answer 1

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