如何在teradata中将A列(日期列)添加到B列(工作日数)以获取新日期?

问题描述

这是我的数据；

表 A.pickup_date 是日期列

table A.pickup_date is a date column

表 A.biz_days 是我想加起来到 A.pickup_date 的工作日

table A.biz_days is the business days I want to add up to A.pickup_date

表 B.date

表 B.is_weekend(是或否)

table B.is_weekend (Y or N)

表 B. is_holiday(是或否)

table B. is_holiday (Y or N)

基本上从表 B，我知道每个日期，如果任何日期是工作日.现在，在将 A.business_days 添加到 A.pickup_date 之后，我想在表 A 中为确切日期添加第三列.

Basically from table B, I know for each date, if any date is a business day or not. Now I want to have a third column in table A for the exact date after I add A.business_days to A.pickup_date.

任何人都可以为此提供一个 case when 语句或过程语句吗?很遗憾，我们不允许在 Teradata 中编写自己的函数.

Can anyone provide me with either a case when statement or procedure statement for this? Unfortunately we are not allowed to write our own functions in Teradata.

推荐答案

最简单的方法是计算连续的工作日数(如果是重复操作，则将其作为新列添加到日历表中，否则使用与):

The easiest way to do this is calculating a sequential number of business days (add it as a new column to your calendar table if it's a recurring operation, otherwise using WITH):

SUM(CASE WHEN is_weekend = 'Y' OR is_holiday = 'Y' THEN 0 ELSE 1 END)
OVER (ORDER BY calendar_date
      ROWS UNBOUNDED PRECEDING) AS biz_day#

那么你需要两个连接:

SELECT ..., c2.calendar_date
FROM tableA AS a
JOIN tableB AS c1
  ON a.pickup_date = c1.calendar_date
JOIN tableB AS c2
  ON c2.biz_day# = c1.biz_day# + a.biz_days
 AND is_weekend = 'N'
 AND is_holiday = 'N'

这篇关于如何在teradata中将A列(日期列)添加到B列(工作日数)以获取新日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！