[ufn_Get_Calculate_Next_WorkingDay]

 USE [CreditDataStore]

GO

/****** Object:  UserDefinedFunction [Daily].[ufn_Get_Calculate_Next_WorkingDay]    Script Date: 2021/09/29 13:33:21 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [Daily].[ufn_Get_Calculate_Next_WorkingDay] ( @pInputDate    DATE )

RETURNS DATE

BEGIN

--SET @pInputDate='2020-04-13'

declare @SUNDAY_date1 DATE

,@SUNDAY_date2 DATE

,@SUNDAY_date3 DATE

,@SUNDAY_date4 DATE

,@SUNDAY_date5 DATE

 ,@CDSBusinessDate DATe

, @CDSBusinessDate1 DATE

, @CDSBusinessDate2 DATE

, @CDSBusinessDate3 DATE

, @CDSBusinessDate4 DATE

, @CDSBusinessDate5 DATE

,@m_date DATE

SET @CDSBusinessDate =  @pInputDate

SET @CDSBusinessDate1 = CASE WHEN @pInputDate = ('9999-12-31') THEN ('9999-12-31') ELSE (DATEADD(d,+1,@CDSBusinessDate)) END

SET @CDSBusinessDate2 =  CASE WHEN @pInputDate = ('9999-12-31') THEN ('9999-12-31') ELSE(DATEADD(d,+2,@CDSBusinessDate)) END

SET @CDSBusinessDate3 =  CASE WHEN @pInputDate = ('9999-12-31') THEN ('9999-12-31') ELSE(DATEADD(d,+3,@CDSBusinessDate)) END

SET @CDSBusinessDate4 =  CASE WHEN @pInputDate = ('9999-12-31') THEN ('9999-12-31') ELSE(DATEADD(d,+4,@CDSBusinessDate)) END

SET @CDSBusinessDate5 =  CASE WHEN @pInputDate = ('9999-12-31') THEN ('9999-12-31') ELSE(DATEADD(d,+5,@CDSBusinessDate)) END

--SUNDAY

SET @SUNDAY_date1 =CASE WHEN DATEPART(DW, @CDSBusinessDate1) IN (1) THEN  @CDSBusinessDate1 END

SET @SUNDAY_date2 =CASE WHEN DATEPART(DW, @CDSBusinessDate2) IN (1) THEN  @CDSBusinessDate2 END

SET @SUNDAY_date3 =CASE WHEN DATEPART(DW, @CDSBusinessDate3) IN (1) THEN  @CDSBusinessDate3 END

SET @SUNDAY_date4 =CASE WHEN DATEPART(DW, @CDSBusinessDate4) IN (1) THEN  @CDSBusinessDate4 END

SET @SUNDAY_date5 =CASE WHEN DATEPART(DW, @CDSBusinessDate5) IN (1) THEN  @CDSBusinessDate5 END

SET @m_date=

--1

        CASE 

WHEN

@CDSBusinessDate1  =

--@CDSBusinessDate1 IN (@SUNDAY_date,

@SUNDAY_date1 OR @CDSBusinessDate1 IN 

(SELECT  holidaydate from CreditDataStore.daily.assetcontrolholidays WHERE (holidaydate BETWEEN ('2020-01-01 00:00:00.000') AND ('2020-12-31 00:00:00.000')) AND region='Johannesburg-ZAR')

--2

THEN CASE

WHEN @CDSBusinessDate2  = @SUNDAY_date2 OR @CDSBusinessDate2 IN (SELECT  distinct holidaydate from CreditDataStore.daily.assetcontrolholidays WHERE (holidaydate BETWEEN ('2020-01-01 00:00:00.000') AND ('2020-12-31 00:00:00.000')) AND region='Johannesburg-ZAR') 

              --3

  THEN CASE

WHEN @CDSBusinessDate3  = @SUNDAY_date3 OR @CDSBusinessDate3 IN (SELECT  distinct holidaydate from CreditDataStore.daily.assetcontrolholidays WHERE (holidaydate BETWEEN ('2020-01-01 00:00:00.000') AND ('2020-12-31 00:00:00.000')) AND region='Johannesburg-ZAR') 

--4

  THEN CASE

WHEN @CDSBusinessDate4  = @SUNDAY_date4 OR @CDSBusinessDate4 IN (SELECT  distinct holidaydate from CreditDataStore.daily.assetcontrolholidays WHERE (holidaydate BETWEEN ('2020-01-01 00:00:00.000') AND ('2020-12-31 00:00:00.000')) AND region='Johannesburg-ZAR') 

THEN @CDSBusinessDate5

--4

ELSE @CDSBusinessDate4

END

--3

ELSE @CDSBusinessDate3

END

--2

ELSE @CDSBusinessDate2

END

  --1

  ELSE @CDSBusinessDate1  

  END

return @m_date 

END