Stored Proc In Teradata For Calculated Field

General
Enthusiast

Stored Proc In Teradata For Calculated Field

Team,

Could you please help in writing a stored proc for the below requirement.

I need to add integer to a data column by excluding weekends & custom holidays.

Example :- OE_Date ( Date ) + Days ( Integer ) = Calculated_Date Column ( Date )

                     02/27/2015      +      1                   =   03/02/15 ( 02/28 & 03/01 are weekends )

Tags (1)
9 REPLIES
Enthusiast

Re: Stored Proc In Teradata For Calculated Field

start by looking at the sys_calendar.calendar view.  You should be able to fashion a case statement using the dates in your table along with the day_of_week column in the calendar view.

Enthusiast

Re: Stored Proc In Teradata For Calculated Field

Besides sys_calendar you may need to look at your company calendar to get the holidays covered.

Enthusiast

Re: Stored Proc In Teradata For Calculated Field

Could you please give an example of a procedure for the above requirement.

Junior Contributor

Re: Stored Proc In Teradata For Calculated Field

There was a similar question on StackOverflow:

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

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'
Enthusiast

Re: Stored Proc In Teradata For Calculated Field

Thank you Dieter. My requirement is different where i need to add custom holidays for each country/region.

Could you please share a proc where it checks for weekends & holidays in OE_Date and keep adding days like a do while or do until loop.

Enthusiast

Re: Stored Proc In Teradata For Calculated Field

In the above code which you have shared could you please tell me how to modify it such that if i add biz days to date column and the final calculated date should be a business day and not holiday.

Example :- 03/02/15 + 1 ( day ) + 1 ( holiday - 3/3 is holiday ) = 3/4/15 is the output.

If 3/4/15 is also a holiday then output should be 3/5/15.

Please advise.

Junior Contributor

Re: Stored Proc In Teradata For Calculated Field

How do you currently implement the calendar for different countries/regions?

The sequential number of business days can be easily calculated multiple times, one per country.

Enthusiast

Re: Stored Proc In Teradata For Calculated Field

I have built the logic in SAS and trying to transition to TD. Please see the sample loop below. Kindly let me know how can i create a loop in TD & replicate for different countries with different holidays.

The below logic keeps adding business days to OE_DATE by excluding weekends & custom holidays. It will also ensure the calculated date output does not fall on holiday or weekend. I am needing to bulid loops for different scenarios for each country ( 3 loops per country and total countries are 25 ) which means data of 80K rows have to loop about 75 times for getting the final output.

Please provide a procedure in teradata to replicate the below or any other way to get desired results.

%let AMERHOL = ('01JAN2014'D,

                '26MAY2014'D,

                '31DEC2014'D,

                '01JAN2015'D )

;

data get4;

format CALCULATED_DATE mmddyy10.;

set LIB.STC;

IF REGION = 'Americas'  THEN DO;

IF  Days   > 0 AND WEEKDAY(OE_DATE) NOT IN (1,7)  then do;  

CALCULATED_DATE = OE_DATE;

datecount =1;

do until(datecount > Days );

     CALCULATED_DATE=CALCULATED_DATE+1;

     if weekday(CALCULATED_DATE) not in (1,7) AND CALCULATED_DATE not in (&AMERHOL.) then datecount=datecount+1;

end;

END;

ELSE DO;

%let AUSHOL = ('01JAN2014'D,

                '27JAN2014'D,

                '26DEC2014'D )

;

data get28;

format CALCULATED_DATE mmddyy10.;

set get27;

IF REGION = 'APJ' AND COUNTRY = 'Australia' THEN DO;

IF  Days   > 0 AND WEEKDAY(OE_DATE) NOT IN (1,7) then do;   

CALCULATED_DATE = OE_DATE;

datecount =1;

do until(datecount > Days );

     CALCULATED_DATE=CALCULATED_DATE+1;

     if weekday(CALCULATED_DATE) not in (1,7) AND CALCULATED_DATE not in (&AUSHOL.) then datecount=datecount+1;

end;

END ;         

ELSE DO;

%let JAPANHOL = ('01JAN2014'D,

                 '02JAN2014'D,

                 '31DEC2014'D )

;

data get30;

format CALCULATED_DATE mmddyy10.;

set get29;

IF REGION = 'APJ' AND COUNTRY = 'Japan' THEN DO;

IF  Days   > 0 AND WEEKDAY(OE_DATE) NOT IN (1,7) then do;  

CALCULATED_DATE = OE_DATE;

datecount =1;

do until(datecount > Days );

     CALCULATED_DATE=CALCULATED_DATE+1;

     if weekday(CALCULATED_DATE) not in (1,7) AND CALCULATED_DATE not in (&JAPANHOL.) then datecount=datecount+1;

end;

END ;         

ELSE DO;

%let MALAYHOL = ('01JAN2014'D,

                 '14JAN2014'D,

                 '12DEC2014'D )

;

data get32;

format CALCULATED_DATE mmddyy10.;

set get31;

IF REGION = 'APJ' AND COUNTRY = 'Malaysia' THEN DO;

IF  Days   > 0 AND WEEKDAY(OE_DATE) NOT IN (1,7) then do;  

CALCULATED_DATE = OE_DATE;

datecount =1;

do until(datecount > Days );

     CALCULATED_DATE=CALCULATED_DATE+1;

     if weekday(CALCULATED_DATE) not in (1,7) AND CALCULATED_DATE not in (&MALAYHOL.) then datecount=datecount+1;

end;

END ;

Enthusiast

Re: Stored Proc In Teradata For Calculated Field

Correction. I need a function to be created in Teradata for the above requirement as i need an output as return.

I created the function in SQL Server 2012 and i need the same replicated in TeraData.

Please help.

CREATE FUNCTION [dbo].[ADD_BUSINESS_DAYS]

(  

@OE_DATE       datetime,

@Days int,

@BUID int

)

RETURNS datetime

AS

BEGIN  

DECLARE @returndate datetime

DECLARE @i int

set @i = 1

set @returndate = @OE_DATE

While @i <= @Days

Begin

    set @returndate = DateAdd(day, 1, @returndate)

                  If (DatePart(dw, @returndate) <> 1) and (DatePart(dw, @returndate) <> 7) and @returndate not in ('02/02/2015','02/03/2015','02/04/2015','02/05/2015') and @BUID = 11

                     Begin

                        set @i = @i + 1

                     End

      END

      RETURN @returndate

      END;