在雪花

Question

我想添加否。到日期领域的工作日。

以下是我的逻辑，如果一天在星期一和没有的话，这是不起作用的。天数超过5

dateadd(DAY, 
        (iff(dayofweek(to_date(Start_Date_Column) ) = 1, 0 ,
(TRUNCATE(((dayofweek(to_date(Start_Date_Column)) + No_DAYS - 1)/5)) * 2)) + No_DAYS) , to_date(Start_Date_Column)); 

。对于以下情况

 select
         
          dateadd(DAY, 
        (iff(dayofweek(to_date('2021-01-04') ) = 1, 0 ,
(TRUNCATE(((dayofweek(to_date('2021-01-04')) + 5 - 1)/5)) * 2)) + 5) , to_date('2021-01-04'))
         

Answer 1

在将特定的工作日添加到日期之后，递归CTE将获得下一个工作日。

WITH RECURSIVE
biz_day (rown, dt) AS
(
SELECT 1,'2022-05-07'::date
union all
SELECT
case when dayname(dateadd(day,1,dt)) not in ('Sat','Sun')
then rown+1
else rown end ,
dateadd(day,1,dt)
from biz_day where rown <= $No_DAYS
)
SELECT min(dt) orig_dt,max(dt) nxt_biz_date FROM biz_day;

orig_dt	nxt_biz_date
2022-05-07	2022-05-20

原始表 -
列next_dy将根据特定天数（例如10）

select * from dates;

DY	NEXT_DY
2022-05-05	NULL
2022-05-09	NULL
2022-05-11	NULL
2022-14	2022-05-14 NAULL
NULL 2022-05-05-05-05-05-05-05-05-05-05- 15	NULL
2022-05-17	null
2022-05-27	NULL

（UDF）函数创建，包括递归CTE，用于获取下一个BIZ Day-

CREATE OR REPLACE FUNCTION next_b_date(dt date,no_day number)
RETURNS date
LANGUAGE SQL
AS
$
WITH RECURSIVE
biz_day (rown, cte_dt) AS
(
SELECT 1,dt
union all
SELECT
case when dayname(dateadd(day,1,cte_dt)) not in ('Sat','Sun')
then rown+1
else rown end ,
dateadd(day,1,cte_dt)
from biz_day where rown <= no_day
)
SELECT max(cte_dt) nxt_biz_date FROM biz_day
$;

匿名块来调用函数和更新源表 -

EXECUTE IMMEDIATE $
DECLARE
  p_dt date;
  n_dt date;
  no_days number;
  c1 CURSOR FOR SELECT dy FROM dates;
BEGIN
 
  for record in c1 do
  p_dt:=record.dy;
  no_days:=10;
  n_dt:=(select next_b_date(:p_dt,:no_days));
  update dates set next_dy = :n_dt where dy=:p_dt;
  end for;
  RETURN 0;
END;
$
;

运行更新后表 -

select * from dates;

DY	NEXT_DY NEXT_DY
2022-05-- 05	2022-05-19
2022-05-09	2022-05-23
2022-05-11	2022-05-25
2022-05-14	2022-05-27 2022-27
2022-05-15 20222-2022-27	2022-27
2022-27 2022-05-- 也可以使用以下17	2022-05-31
2022-05-27	2022-06-10

，也可以使用，它几乎没有硬编码
即乘以乘以3的天数，三天在几天内添加额外的时间
（弥补中级SAT/SUN）以获取工作日。

set no_days=11;

update dates dt1 set next_dy = dt2.n_dy from
(
with cte1 as
(select row_number() over (partition by dy order by seq4()) rn,dy,
dateadd(day,rn,dy) day1 from dates, 
table(generator(rowcount=>$no_days*3))), cte2 as
(select row_number() over (partition by dy order by seq4()) rn,dy,day1 
from cte1 where dayname(day1) 
not in ('Sat','Sun') )
select dy,max(day1) n_dy from cte2 where rn<=$no_days 
group by dy order by dy
) dt2
where dt1.dy = dt2.dy;

更新后表 -

select * from dates;

dy	next_dy
2022-05-05	2022-05-20
2022-05-09	2022-05-24
2022-05-11	2022-05-26
2022-05-05-14	2022-05-05-05-05-30
2022-05-05-05--05--05--05--05--05--05-- 15	2022-05-30
2022-05-17	2022-06-01
2022-05-27	2022-06-13

旧 - 解决方案（无效）添加天数并搬到下周一 -

要获得下一个工作日，请检查是否添加后的第二天是星期六或阳光，如果是这样，请使用函数next_day

与某些test-data查询：

with date_cte(bizday) as
(select * from values
('2022-05-05'::date),
('2022-05-11'::date),
('2022-05-14'::date),
('2022-05-15'::date),
('2022-05-21'::date),
('2022-05-27'::date)
)
select bizday orig_date,dayname(bizday) orig_day,
$No_DAYS No_days,
dateadd(day,$No_DAYS,bizday) next_day,
case when dayname(dateadd(day,$No_DAYS,bizday)) = 'Sat'
then next_day(dateadd(day,$No_DAYS,bizday),'Monday')
when dayname(dateadd(day,$No_DAYS,bizday)) = 'Sun'
then next_day(dateadd(day,$No_DAYS,bizday),'Monday')
else dateadd(day,$No_DAYS,bizday) end next_b_day,
dayname(next_b_day) next_b_dayname
from date_cte;

orig_date	orig_day	no_days	nos_day	next_day next_b_b_day	next_b_day next_b_dayname
2022-05-05	thu	10	2022-05-15-15-15-15-15-15-15	2022-05-16	MON
2022-05-11	WED	10	2022-05-21	2022-05-23	MON
2022-05-14	SAT	10	2022-05-24	2022-05-24	SAT
​	​	​	​-05-25	2022-05-25	WED
2022-05-21	SAT	10	2022-05-31	2022-05-31	TUE
2022-05-27	FRI	10	2022-06-06-06 2022-06	2022-06-06	MON

参考href =“ https://docs.snowflake.com/en/sql-reference/functions-date time.html#supported-date-date-and-time-parts“ rel =“ nofollow noreferrer”> date noreferrer“> date time

此外，您可能需要在此处添加更多信息，因为可以改变工作日的定义。