Get Next and Previous "Load Day"

Question

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.

IsLoadDay 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_STATE) and No Load Days (afrs_daily_load_no_load_date).

GetNextLoadDay and GetPreviousLoadDay are intended to be used. They each use IsLoadDay to determine if the date parameter is a Load Day, and then move forward or backward through time until a Load Day is found.

GetNextLoadDay will check the date parameter and determine if it is a Load Day whereas GetPreviousLoadDay 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

Answer 1

Is Load Day?

Your IsLoadDay function seems a little overcomplicated. Might I suggest trying something along the lines of this, which brings all the checks you want to do pretty nicely into a single coalesce:

CREATE FUNCTION [dbo].[IsLoadDay](@Dt DATE)
RETURNS BIT
AS
BEGIN

RETURN COALESCE
(
    --If it is a monday or sunday, it's not a load day
    CASE WHEN DATENAME(DW, @Dt) IN ('SUNDAY', 'MONDAY') THEN 0 END,

    --If it is a designated not load day, it's not a load day
    (
        SELECT 0 
        FROM [DBMGMT].[dbo].[afrs_daily_load_no_load_date]
        WHERE [load_date] = @Dt AND [load_ind] = 'N'
    ),
    
    --If it is a holiday, it's not a load day
    (
        SELECT 0
        FROM [master].[dbo].[HOLDY_WA_STATE]
        WHERE [HOLDY_DATE] = @Dt
    ),

    --Otherwise, it's a load day
    1
)
END
GO