CODEWITHSUNDEEP
loopsdateteradataprocedure
yuckeye
yuckeye

Reputation: 41

How to add column A (date column) to Column B ( number of business days) in teradata to get the new date?

Here's my data;

table A.pickup_date is a date column

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

table B.date

table B.is_weekend (Y or N)

table B. is_holiday (Y or N)

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.

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.

Upvotes: 0

Views: 635

Answers (2)

dnoeth
dnoeth

Reputation: 60482

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#

Then you need two joins:

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'

Upvotes: 0

Andrew
Andrew

Reputation: 8758

This is pretty darned ugly, but I think it should get you started.

First I created a volatile table to represent your table a:

CREATE VOLATILE TABLE vt_pickup AS
(SELECT CURRENT_DATE AS pickup_date,
8 AS Biz_Days) WITH DATA PRIMARY INDEX(pickup_date)
ON COMMIT PRESERVE ROWS;

INSERT INTO vt_pickup VALUES ('2015-02-24',5);

Then I joined that with sys_calendar.calendar to get the days of the week:

CREATE VOLATILE TABLE VT_Days AS 
(
SELECT
            p.pickup_date,
            day_of_week
            FROM

            vt_pickup p
            INNER JOIN sys_calendar.CALENDAR c
            ON c.calendar_date >= p.pickup_date 
            AND c.calendar_date < (p.pickup_date + Biz_Days)
) WITH DATA 
PRIMARY INDEX(pickup_date)
ON COMMIT PRESERVE ROWS

Then I can use all that to generate the actual delivery date:

SELECT 
p.pickup_date,
p.biz_days,
biz_days + COUNT(sundays.day_of_week) + COUNT (saturdays.day_of_week) AS TotalDays,
COUNT (sundays.day_of_week) AS Suns,
COUNT (saturdays.day_of_week) AS Sats,
p.pickup_date + totaldays AS Delivery_Date,
FROM 
    vt_pickup p
    LEFT JOIN vt_days AS Sundays ON
         p.pickup_date = sundays.pickup_date
         AND sundays.day_of_week = 1
        LEFT JOIN vt_days AS saturdays ON
            p.pickup_date = saturdays.pickup_date
            AND saturdays.day_of_week = 7
GROUP BY 1,2

You should be able to use the logic with another alias for your holidays.

Upvotes: 0

Related Questions