Number of Working Days

  • zulmanclock

    SSC Eights!

    Points: 968

    Comments posted to this topic are about the item Number of Working Days

  • Dave Vroman

    SSC Eights!

    Points: 821

    Couldn't get yours to work. Too many errors in the post. We're using this one. I wanted to see the difference. The table of holidays contains only holidays that fall on business days.

    CREATE FUNCTION [dbo].[CalculateBusinessDays]

    (

    @StartDate DATE,

    @EndDate DATE

    )

    RETURNS INTEGER

    AS

    BEGIN

    IF @EndDate IS NULL

    SELECT @EndDate = GETDATE()

    RETURN CONVERT(INTEGER,

    (DATEDIFF(dd, @StartDate, @EndDate) + 1)

    - (DATEDIFF(wk, @StartDate, @EndDate) * 2)

    - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

    - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

    - ISNULL((SELECT COUNT(*) FROM tblHoliDays

    WHERE @StartDate <= Holiday AND @EndDate >= Holiday), 0))

    END

  • zulmanclock

    SSC Eights!

    Points: 968

    Dave Vroman (2/8/2013)


    Couldn't get yours to work. Too many errors in the post. We're using this one. I wanted to see the difference. The table of holidays contains only holidays that fall on business days.

    CREATE FUNCTION [dbo].[CalculateBusinessDays]

    (

    @StartDate DATE,

    @EndDate DATE

    )

    RETURNS INTEGER

    AS

    BEGIN

    IF @EndDate IS NULL

    SELECT @EndDate = GETDATE()

    RETURN CONVERT(INTEGER,

    (DATEDIFF(dd, @StartDate, @EndDate) + 1)

    - (DATEDIFF(wk, @StartDate, @EndDate) * 2)

    - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

    - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

    - ISNULL((SELECT COUNT(*) FROM tblHoliDays

    WHERE @StartDate <= Holiday AND @EndDate >= Holiday), 0))

    END

    What sorts of errors are you getting when you try to run the function?

  • Dave Vroman

    SSC Eights!

    Points: 821

    Your answer told me that I was having problems with the browser (Chrome). Tried it with Firefox - works well.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply