All Forums Database
CWest10 2 posts Joined 09/12
12 Apr 2016
Convert TSQL function to Teradata

Hi,
 
I have the following SQL function and want to recreate it Teradata. Is this possible? Any suggestions welcome.
The function calcualtes the working days, excluding weekends and business holidays.
Thanks in advance,
Chris

FUNCTION [dbo] .[fnWIPTracking_FindWorkingDays_ExculdingWeekendsAndHo lidays_SSISPackage]

(
      -- Add the parameters for the function here
      @StartDate  DateTime, 
      @EndDate    DateTime

)
RETURNS NVARCHAR(MAX)
AS
BEGIN
      -- Declare the return variable here
    DECLARE @CURRENTDATE DateTime;
    DECLARE @DateDiffInSec FLOAT(4);
    DECLARE @TotalTime FLOAT(4);
	DECLARE @SecondsInADay FLOAT(4);
	DECLARE @SecondsInAHour FLOAT(4);
	DECLARE @START_MIDNIGHT DATETIME;
	DECLARE @END_MIDNIGHT DATETIME;
	DECLARE @TimeOffset FLOAT(4);
    
    SET @CURRENTDATE = @StartDate
    SET @DateDiffInSec = 0
	SET @SecondsInADay = 86400.0
	SET @SecondsInAHour = 3600.0
	SET @TotalTime = 0.0
	SET @TimeOffset = 0.0

    --For each date in the range, starting with the start date, and going to the end date
	WHILE CONVERT(varchar(8), @CURRENTDATE, 112) <= CONVERT(varchar(8), @EndDate, 112)
		BEGIN
			--If the current date is a weekend day, skip if (that is the NOT IN part)
			IF UPPER(DATENAME(weekday, @CURRENTDATE)) NOT IN ('SATURDAY', 'SUNDAY')
				BEGIN
					--If the current date is also not in the holiday table
					IF NOT EXISTS (SELECT * FROM QTPM_tblWIPTracking_Holidays WHERE CONVERT(varchar(8), [Holiday], 112) = CONVERT(varchar(8), @CURRENTDATE, 112))
						BEGIN
							--To get the total time, we are going to store a running total
							-- of the time based on the input range


							IF CONVERT(varchar(8), @StartDate, 112) = CONVERT(varchar(8), @EndDate, 112)
								BEGIN
									--If start date and end date is the same, just get difference between the two
									SET @DateDiffInSec = @DateDiffInSec + DATEDIFF(second, @StartDate, @EndDate)
								END
							ELSE IF CONVERT(varchar(8), @StartDate, 112) = CONVERT(varchar(8), @CURRENTDATE, 112)
								BEGIN
									--If it is the start date get the precise time span from 11:59 PM of start date
									SET @START_MIDNIGHT = DATEADD(hh, 23, DATEADD(mi, 59, DATEADD(ss, 59, CONVERT(datetime, Convert(varchar(8), @StartDate, 112), 111))))
									SET @TimeOffset = DATEDIFF(second, @StartDate,  @START_MIDNIGHT)
									SET @DateDiffInSec = @DateDiffInSec + @TimeOffset
								END
							ELSE IF CONVERT(varchar(8), @CURRENTDATE, 112) = CONVERT(varchar(8), @EndDate, 112)
								BEGIN
									--If it is the end date get the precise time span from 12 AM of the end date
									SET @END_MIDNIGHT = CONVERT(datetime, Convert(varchar(8), @EndDate, 112), 111)
									SET @TimeOffset = DATEDIFF(second, @END_MIDNIGHT, @EndDate)
									SET @DateDiffInSec = @DateDiffInSec + @TimeOffset
								END
							ELSE
								--for all dates in between (not start and not end), add a 24 hr span
								--BEGIN
									SET @DateDiffInSec = @DateDiffInSec + @SecondsInADay
								--END
						END
				END
				SET @CURRENTDATE = DATEADD(day,1,@CURRENTDATE)  --step through one day at a time 
		END

	--Total time is running total in seconds divided by seconds in a hour, this gets total in hours
	SET @TotalTime = @DateDiffInSec / @SecondsInAHour

	RETURN @TotalTime
END

 

dnoeth 4628 posts Joined 11/04
12 Apr 2016

There's no way to rewrite that function as a SQL-UDF.
For a single calculation you might rewrite it as a Stored Procedure, but you shouldn't run this logic on a larger number of rows because it's very inefficient (WHILE-loop & SELECT within loop).
 
The only efficient solution for calculating a duration based on business days utilizes a calendar where you pre-calculate a sequential number of business days, see:
http://forums.teradata.com/forum/database/stored-procedure-performance-improvement#comment-14128
 
Then you need two joins:

SELECT ..., (c2.biz_day#-c1.biz_day#-1) as number_of_business_days 
FROM tableA AS a 
JOIN yourCalendar AS c1
  ON a.StartDate = c1.calendar_date
JOIN yourCalendar AS c2
  ON a.EndDate = c1.calendar_date 

 
Of course this is based on DATEs only, so you need to join on CAST(StartDate AS DATE) and add some logic to include the TIME part in your calculation...
 
 

Dieter

CWest10 2 posts Joined 09/12
14 Apr 2016

Thanks Dieter. I'll check into your suggested solution.
 
Chris

You must sign in to leave a comment.