使用 SQL 中的日期表将工作日添加到另一个表

尼丁·黛布

我有这两个带有值的表：

create table FieldTable (
    FieldId int,
    FieldName varchar(256),
    FieldValue sql_variant);
go

insert into FieldTable values (1, 'ABC', '04/17/2020');
go

create table DatbeTable (
    Dt date,
    DayType char);
go

insert into DatbeTable values ('2020-04-01','B');
insert into DatbeTable values ('2020-04-02','B');
insert into DatbeTable values ('2020-04-03','B');
insert into DatbeTable values ('2020-04-04','W');
insert into DatbeTable values ('2020-04-05','W');
insert into DatbeTable values ('2020-04-06','B');
insert into DatbeTable values ('2020-04-07','B');
insert into DatbeTable values ('2020-04-08','B');
insert into DatbeTable values ('2020-04-09','B');
insert into DatbeTable values ('2020-04-10','B');
insert into DatbeTable values ('2020-04-11','W');
insert into DatbeTable values ('2020-04-12','W');
insert into DatbeTable values ('2020-04-13','B');
insert into DatbeTable values ('2020-04-14','B');
insert into DatbeTable values ('2020-04-15','B');
insert into DatbeTable values ('2020-04-16','B');
insert into DatbeTable values ('2020-04-17','B');
insert into DatbeTable values ('2020-04-18','W');
insert into DatbeTable values ('2020-04-19','W');
insert into DatbeTable values ('2020-04-20','B');
insert into DatbeTable values ('2020-04-21','B');
insert into DatbeTable values ('2020-04-22','B');
insert into DatbeTable values ('2020-04-23','B');
insert into DatbeTable values ('2020-04-24','B');
insert into DatbeTable values ('2020-04-25','W');
insert into DatbeTable values ('2020-04-26','W');
insert into DatbeTable values ('2020-04-27','B');
insert into DatbeTable values ('2020-04-28','B');
insert into DatbeTable values ('2020-04-29','B');
insert into DatbeTable values ('2020-04-30','B');
go

我想从FieldValue日期到新列添加 3 个工作日：NewFieldValue对于该行。

select 
    FieldId, 
    FieldName,
    FieldValue--,
    --NewFieldValue
into #temp
from FieldTable ft
join DatbeTable d
    on format(try_convert(date, ft.FieldValue), 'd', 'en-us') = format(try_convert(date, d.Dt), 'd', 'en-us');
go

我真的不知道如何使用这两个表来获得以下结果：

FieldId FieldName   FieldValue  NewFieldValue
------- ---------   ----------  -------------
1       ABC         04/17/2020  04/22/2020

松鼠

您可以使用从基准日期中row_number()找出nth工作日

select 
        FieldId, 
        FieldName,
        FieldValue, 
        d.Dt as NewFieldValue
from    FieldTable ft
        cross apply 
        (
            select  x.Dt, day_no = row_number() over (ORDER BY x.Dt)
            from    DatbeTable x
            where   x.Dt        > try_convert(date, ft.FieldValue)
            and     x.DayType   = 'B' -- business day only
        ) d
where   d.day_no    = 3 -- add 3 business days

注意：不要将日期转换为字符串进行比较。

PS：你应该解释什么是B和W。认为很明显是 Business 和 Weekend 但是，如果解释表中的相关列会更清楚。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。