1
\$\begingroup\$

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
\$\endgroup\$
1
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ I had to look at it and think on it, but that is much clearer to read. Many thanks. \$\endgroup\$ – Kennah Aug 18 '15 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.