none
Creating A function that skips certain days RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I Need to create a SQL function for the TEST database that takes two dates as parameters and returns the number of business days between them.  This calculation will skip over weekends and Company Holidays.  Holidays can be found in the Adviser database's CompanyHoliday table.
    Thursday, November 14, 2019 7:21 PM
    |

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Assuming you have a Calendar table with all dates and IsWeekend column as one of the columns, then

    select count(*) as NumberOfBusinessDates from dbo.Calendar c where

    c.[Date] between @StartDate and @EndDate and c.IsWeekend = 0 and not exists (select 1 from dbo.Holidays h

    where h.[Date] = c.[Date])

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    My TechNet articles

    • Marked as answer by KingRBG2009 Thursday, November 14, 2019 9:20 PM
    Thursday, November 14, 2019 7:40 PM
    |
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Assuming you have a Calendar table with all dates and IsWeekend column as one of the columns, then

    select count(*) as NumberOfBusinessDates from dbo.Calendar c where

    c.[Date] between @StartDate and @EndDate and c.IsWeekend = 0 and not exists (select 1 from dbo.Holidays h

    where h.[Date] = c.[Date])

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    My TechNet articles

    • Marked as answer by KingRBG2009 Thursday, November 14, 2019 9:20 PM
    Thursday, November 14, 2019 7:40 PM
    |
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello, Thanks for the reply but this will throw a error because we are creating a function and we can't have a select statement in a function that will give the error select statement included in a function can not return data to a client 
    Thursday, November 14, 2019 8:03 PM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    You need to declare a integer variable to get the count and then return that variable:

    DECLARE @NumberOfBusinessDates int;
    SELECT @NumberOfBusinessDates = COUNT(*)
    FROM dbo.Calendar
    WHERE ......;

    A Fan of SSIS, SSRS and SSAS

    Thursday, November 14, 2019 8:13 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Please see "dbo.udf_AddBusinessDays" in:

    https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

    Thursday, November 14, 2019 8:46 PM
    |
    Moderator