2 Replies Latest reply on Jul 20, 2009 10:45 AM by GaryNY

    Subtracting/adding a number of business days ??

    GaryNY

      Hi everyone !

      I have a calendar question: I have a date, number of days (either business or calendar) and Before/After indicator.

      I need to calculate the future/past date depending on business/calendar nn days.

      Ideally, I will also read Holidays table to fix the result with Holidays.

       

      Any suggestions ?

      CF allows me to know a weekday, add/subtract units from dates, compare dates, but nothing in the direction I mentioned...

       

      Any suggestions will be greatly appreciated !

       

      Gary.

        • 1. Re: Subtracting/adding a number of business days ??
          Dan Bracuk Level 5

          put a table in your db that has the date as the primary key, a business day field, and anything else that's relevent to your organization.  Give the business day field a value of 0 or 1.

           

          Then, the number of business days between 2 dates is

          select sum(business_day)

          from your table

          where the date between date1 and date2

           

          to find the date which is x business days from date 1

          <cfset y = 2 * x>

           

          select date2 from

          (

          <cfloop from = "1" to ="y" index ="ii">

          select dateadd(day, ii, date1) date2, sum(business_day) busdays

          from your table

          where the date between date1 and date2

          group by date2

          union

          </cfloop>

          select distinct date1 date2, 0 busdays

          from yourtable

          ) sq

          where busdays = x

           

          syntax is db specific of course.

          • 2. Re: Subtracting/adding a number of business days ??
            GaryNY Level 1

            Hi !

            Thank you very much, but I would rather not support business days table

            every year...

            Even Holidays table will be created as a temporary measure: I'll need to

            find a way to automatically download the Holidays.

             

            But for the business days, I would rather rely on an algorithm, which

            calculates the resulting date with:

            start date,

            After/Before indicator,

            Business/Calendar days indicator,

            Number of days.

             

            Best regards,

            Gary.