Subtracting/adding a number of business days ??

Question

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.

Dan_Bracuk · Answer

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

select date2 from

(

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded