Convert TSQL function to Teradata

Database

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_ExculdingWeekendsAndHolidays_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
Tags (2)
2 REPLIES
Junior Contributor

Re: Convert TSQL function to Teradata

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...

Re: Convert TSQL function to Teradata

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

Chris