Business rule: Calculate new date respecting the working day calendar

2

Hi everyone,

I had the requirement to calculate a reminder date based on a due date. This should be x days before while respecting the working day calendar.


SQL Command for calculating a date _after_ the base date, attachment (1):
select Top 1 [CAL_WorkingDate]
from (
-- Top: number of working days before the base date
SELECT TOP {BRP:13} [CAL_WorkingDate],[CAL_IsWorkingDay]
FROM [dbo].[Calendars]
where [CAL_WorkingDate] > '{BRP:14}' and CAL_IsWorkingDay = 1
order by CAL_WorkingDate asc
) as workingdays
order by CAL_WorkingDate desc

SQL Command for calculating a date _before_ the base date, attachment (2):
select Top 1 [CAL_WorkingDate]
from (
-- Top: number of working days before the base date
SELECT TOP {BRP:11} [CAL_WorkingDate],[CAL_IsWorkingDay]
FROM [dbo].[Calendars]
where [CAL_WorkingDate] < '{BRP:12}' and CAL_IsWorkingDay = 1
order by CAL_WorkingDate desc
) as workingdays
order by CAL_WorkingDate asc


The business rule can be used inside a form rule (3). The red highlighted days in the calendar are the non working days as they are defined in the calendar.

Just make sure that the calendar for the next year(s) are already defined. :)

Best regards,
Daniel