I have a report request and one of the requirements is to display the due date for each record. The records that are displayed in the report each have a DateReported value (datetime). The due date is determined by adding 20 business days to the DateReported value. Business days excludes weekends and holidays that our organization observes.
I’ve tried playing around with the DateAdd function but I can’t figure out how to exclude weekends and specified holidays.
Where do you hold the list of holidays - please say in the database
Are any of those holidays Weekend dates (e.g. Xmas)
What you actually need is a list of working days to be held in the database (as opposed to non working days) - You could hold all days and holidays and select from days where date is not in holidays.
you could then do this as a subquery
SELECT MAX(WrkDate) from (SELECT Top 20 WrkDate From WorkingDays where WorkingDays.WrkDate >= Invoice.date order by WorkingDays.WrkDate)
This may be easier to build as a view or function in your database, rather than in the report itself.
if you are doing it inside the database as a function you could also build the days as a temptable and exclude weekends and known holidays.
Thanks obiron! The code in the link you provided worked perfectly. Just to follow up on your questions. My holiday date list was in a formula in report. The database is Oracle…and I don’t have write access to it so I can only work with what is there and unfortunatly there was now holiday or workingday table.
