All Forums General
sharath_rk 7 posts Joined 03/15
03 Mar 2015
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:
VandeBergB 182 posts Joined 09/06
03 Mar 2015

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.

Some drink from the fountain of knowledge, others just gargle.

Glass 225 posts Joined 04/10
03 Mar 2015

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

sharath_rk 7 posts Joined 03/15
03 Mar 2015

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

dnoeth 4628 posts Joined 11/04
04 Mar 2015

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 date?
 
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'

 

Dieter

sharath_rk 7 posts Joined 03/15
04 Mar 2015

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.
 

sharath_rk 7 posts Joined 03/15
04 Mar 2015

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.
 

dnoeth 4628 posts Joined 11/04
04 Mar 2015

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.

Dieter

sharath_rk 7 posts Joined 03/15
04 Mar 2015

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 ;
 
 

sharath_rk 7 posts Joined 03/15
05 Mar 2015

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;

You must sign in to leave a comment.