如何使用同一列中的先前值增加日期并跳过周末日期?

Question

>我试图根据其他日期列增加日期并跳过周末日期。

SELECT DATENAME(dw,GETDATE()) -- Friday
SELECT DATEPART(dw,GETDATE()) -- 6

我知道如何检查周末以及如何跳过日期，但我想得到如下所示的结果

前任。如果我在一列中输入日期，例如

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

那么我希望在新列中产生结果，例如

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

注意：不要跳过输入日期中的任何行

Answer 1

这似乎很棘手。 我认为我最好的建议是重建数据。 一种相对简单的方法使用递归 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;

这是一个数据库<>小提琴。

Answer 2

SELECT  FORMAT(OriginalDate, 'dd MMMM yyyy')
FROM    dbo.TableName WITH ( NOLOCK )
WHERE   DATENAME(DW, OriginalDate) NOT IN ( 'Sunday', 'Saturday' )

Answer 3

我创建了一个日历表来保存日期和一个"isweekend"标志，然后通过排除周末日期并对两个表进行排序，您可以将它们连接在一起以获取所需的数据

use tempdb
GO
-- create a calendar table
drop table if exists #calendar
GO
CREATE TABLE #calendar(
[date] date
, isweekend AS (IiF((datename(weekday,[date])) IN ('Saturday', 'Sunday'), 1, 0))
)
--populate with data from 2000 onwards (100000 days)
; with numbers as (
select ROW_NUMBER() over(order by (select null)) as n from sys.columns o1
cross join sys.columns o2
)
insert into #calendar ([date]) 
select dateadd(day, numbers.n -1, '20000101') from numbers
where numbers.n < 100000
go
drop table if exists #originaldate
GO
create table #originaldate (
original date
)
GO
insert into #originaldate (original)
values ('20191004'), ('20191005'), ('20191006'), ('20191007'), ('20191008'), ('20191009'), ('20191010'), ('20191011'), ('20191012'), ('20191013')
select * from #originaldate
GO

; with weekdays as ( -- we only want weekdays, in date order
select [date], row_number() over (order by [date] asc) as rn
from #calendar c
where  c.isweekend = 0
and date >= '20191004' -- IMPORTANT, you must start row number at the same date as the table you want to match againt
)
, originals as ( -- order the dates we are matching
select original, ROW_NUMBER() over (order by [original] asc) as rn
from #originaldate
)
select o.original, w.date as updateddate
from weekdays w
inner join originals o
on o.rn = w.rn
order by o.original ASC