# Get Next and Previous “Load Day”

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.

CREATE FUNCTION [dbo].[IsLoadDay](@Dt DATE)
RETURNS BIT
AS
BEGIN
IF DATENAME(DW, @Dt) IN ('SUNDAY', 'MONDAY')
BEGIN
END
ELSE
BEGIN
END
ELSE
IF EXISTS(SELECT 1 FROM [master].[dbo].[HOLDY_WA_STATE] WHERE [HOLDY_DATE] = @Dt)
BEGIN
END
END
GO


CREATE FUNCTION [dbo].[GetNextLoadDay](@Dt DATE)
RETURNS DATE
AS
BEGIN
DECLARE @NxtDt DATE
SET @NxtDt = @Dt
SET @NxtDt = DATEADD(D, 1, @NxtDt)
END
RETURN @NxtDt
END
GO


CREATE FUNCTION [dbo].[GetPreviousLoadDay](@Dt DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @PrvDt DATE
SET @PrvDt = DATEADD(D, -1, @Dt)
SET @PrvDt = DATEADD(D, -1, @PrvDt)
END
RETURN @PrvDt
END
GO


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
),

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