I keep looking at this trying to figure if there's a better way. All three functions work, but I wanted to get some insights and ideas from others.
IsLoadDayIsLoadDay determines if the date parameter is a "Load Day". Load Days are Tuesday through Saturday, but we skip holidays and designated No Load Days. There are two existing tables of Holidays (HOLDY_WA_STATEHOLDY_WA_STATE) and No Load Days (afrs_daily_load_no_load_dateafrs_daily_load_no_load_date).
GetNextLoadDayGetNextLoadDay and GetPreviousLoadDayGetPreviousLoadDay are intended to be used. They each use IsLoadDayIsLoadDay to determine if the date parameter is a Load Day, and then move forward or backward through time until a Load Day is found.
GetNextLoadDayGetNextLoadDay will check the date parameter and determine if it is a Load Day whereas GetPreviousLoadDayGetPreviousLoadDay will first go one day back and then start checking if the date is a Load Day.
IsLoadDay Function
CREATE FUNCTION [dbo].[IsLoadDay](@Dt DATE)
RETURNS BIT
AS
BEGIN
DECLARE @IsLoadDay BIT = 1
IF DATENAME(DW, @Dt) IN ('SUNDAY', 'MONDAY')
BEGIN
SET @IsLoadDay = 0
END
ELSE
IF EXISTS(SELECT 1 FROM [DBMGMT].[dbo].[afrs_daily_load_no_load_date] WHERE [load_date] = @Dt AND [load_ind] = 'N')
BEGIN
SET @IsLoadDay = 0
END
ELSE
IF EXISTS(SELECT 1 FROM [master].[dbo].[HOLDY_WA_STATE] WHERE [HOLDY_DATE] = @Dt)
BEGIN
SET @IsLoadDay = 0
END
RETURN @IsLoadDay
END
GO
GetNextLoadDay Function
CREATE FUNCTION [dbo].[GetNextLoadDay](@Dt DATE)
RETURNS DATE
AS
BEGIN
DECLARE @NxtDt DATE
SET @NxtDt = @Dt
WHILE [dbo].[IsLoadDay](@NxtDt) != 1 BEGIN
SET @NxtDt = DATEADD(D, 1, @NxtDt)
END
RETURN @NxtDt
END
GO
GetPreviousLoadDay Function
CREATE FUNCTION [dbo].[GetPreviousLoadDay](@Dt DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @PrvDt DATE
SET @PrvDt = DATEADD(D, -1, @Dt)
WHILE [dbo].[IsLoadDay](@PrvDt) != 1 BEGIN
SET @PrvDt = DATEADD(D, -1, @PrvDt)
END
RETURN @PrvDt
END
GO
