Date Add Working Days

I have seen several other posts here about making calculated fields using working days instead of all days. For example, I want to look ahead 20 working days from my start day. I only want to consider weekdays and can ignore holidays for now. In a previous post I found this code:

``````CASE WHEN DATENAME(WEEKDAY,POHeader.OrderDate) = 'Sunday'

WHEN DATENAME(WEEKDAY,POHeader.OrderDate) = 'Saturday'

END
``````

I want to take my start date, and look ahead 20 working days, so I changed the numbers inside to:

``````CASE WHEN DATENAME(WEEKDAY, @StartDate ) = 'Sunday'

WHEN DATENAME(WEEKDAY, @StartDate) = 'Saturday'

END
``````

Obviously this wonâ€™t work because it ignores weekends as it adds days on. If I input 4/1/23, it should return 4/28/23 (20 weekdays from 4/1). Instead it outputs 4/20/23.

Does this request make sense? Can I still use this calculation with some changes?
Thanks!
Nate

Confused as to why if you are aiming to add 20 working days that you are adding less than 20 regular days. I would think you would actually add more regular days to make up for the non workdays. Something like this:

``````
CASE WHEN DATENAME(WEEKDAY, @StartDate ) = 'Sunday'

WHEN DATENAME(WEEKDAY, @StartDate) = 'Saturday'

END
``````
1 Like

I suppose I donâ€™t understand the logic path here. If the start date is arbitrary, it could be a weekday or weekend, and I add on 20 days, at least some of those days will be weekends unless it knows how to skip that day, or it knows how many weekend days are in the 20 day range.

So Confused!

With this input of 4/1, the output is 4/30.

``````
CASE WHEN DATENAME(WEEKDAY, @StartDate ) = 'Sunday'

WHEN DATENAME(WEEKDAY, @StartDate) = 'Saturday'

END
``````

Sorry, screwed up my math:

``````CASE WHEN DATENAME(WEEKDAY, @StartDate ) = 'Sunday'

WHEN DATENAME(WEEKDAY, @StartDate) = 'Saturday'

END
``````
1 Like

The logic being we are adding 4 weeks, but if we are starting on Sunday, two of those donâ€™t count and Saturday one wouldnâ€™t countâ€¦ 4 weeks from Sunday would be Sunday, removing two days puts us back to Friday. Saturday, removing one day takes us back to Friday.

This will not take into account any holidays or non-weekend non-working days.

1 Like

This is great! It took me some trials to convince myself this returns the right data. I believe this is working correctly now.
Thank you so much!

The fun one is Date Add Working Days per your prod calâ€¦

1 Like

Agreedâ€¦ this requires â€śloopingâ€ť through the production calendar. I once created a routine that would do that against a calendarâ€¦ but it assumed that all saturdays and sundays were already non-working days, and so I just needed to know the special holidays.

I wrote this code a very long time agoâ€¦ if you can figure out how it works, have at it. No guarantees here: Note that it uses C# Functions. This is just a section of the code. It ran very fast.

``````
// Function to load a dictionary Table with a list of holidays
//
Func<string, System.Collections.Generic.Dictionary<DateTime?, bool>> getHolidayDictionaryFUN = (CalendarID) => {
var holidays = (from h in Db.ProdCalDay where h.Company == companyID && h.CalendarID == CalendarID select new { h.ModifiedDay, h.WorkingDay });
var dictionaryTable = new System.Collections.Generic.Dictionary<DateTime?, bool>();

foreach (var holiday in holidays) {
}
return dictionaryTable;
};

// Function to calculate the next lead date based on shop calendar... This will add the number of shop days to the starting date, skipping holidays
//  This function is called from multiple places below
//
Func<DateTime, int, string, DateTime> addLeadTimeUseCalendarFUN = (startingDate, shopDays, CalendarID) => {
var holidayDictionary = getHolidayDictionaryFUN(CalendarID);

DateTime shipDate = startingDate.AddDays(-1); //make it yesterday
int RemainingDays = shopDays + 1; //add one to the leadtime

while (RemainingDays > 0) {
if (shipDate.DayOfWeek == DayOfWeek.Friday) shipDate = shipDate.AddDays(3);
else if (shipDate.DayOfWeek == DayOfWeek.Saturday) shipDate = shipDate.AddDays(2);
else shipDate = shipDate.AddDays(1);

bool WorkDay = (holidayDictionary.ContainsKey(shipDate)) ? (bool) holidayDictionary[shipDate] : true;
if (WorkDay) RemainingDays -= 1;

}
return shipDate;
};

endDate = addLeadTimeUseCalendarFUN(startDate, 0, CalendarID); //need to make sure that the date is not a holiday or weekend

``````
2 Likes

Iâ€™ve done that, the nice thing is that if itâ€™s done well it solves weekend handling too. First, you need a calendar table. Iâ€™m working from SaaS so donâ€™t have access to the database to do the right thing and make a proper tally or calendar table, so I hack it out at runtime with CTEâ€™s.

``````with a as ( /*just making rows, we don't care what's in them, the less the better so null it is*/
select null as whatever
union all
select null
),tally as ( /*now we make a series of integers*/
select row_number() over(order by (select null)) as increment
from a a1, a a2, a a3, a a4 /*each cross join doubles the rowcount, expand as required*/
),calendar as(
select
day
,-1 * tally.increment
/*Special note about the next line: I'm a SaaS customer, and the server's timezone
is wherever the server is. Such is life, never blindly trust a datetime on services
you don't control anyway. So I bounce the timezone off of UTC on my way to my local
timezone so I never have to care what timezone the server is set to.*/
,cast(current_timestamp at time zone 'UTC' at time zone 'your timezone goes here' as date)
) as _date
from tally
)

select
calendar._date
,cast(isnull(
ProdCalDay.WorkingDay
/*this next line assumes @@DATEFIRST is 7, probably is but bears mentioning*/
,case when datepart(weekday, dateadd(day, -1, calendar._date)) <= 5 then 1 else 0 end
) as bit) as is_workday
from calendar
left outer join ProdCalDay
on ProdCalDay.ModifiedDay = calendar._date
and ProdCalDay.WorkingDay = 0
``````

From there youâ€™ve got all sorts of ways to count back from date A to some count of is_workday.

2 Likes

couple legends right here posting the code for all! @timshuwy @kananga

I also wrote some nasty stuff based on some code that @hkeric.wci shared with me.

1 Like

Itâ€™s how I learned! In this case from clever examples shared by Jeff Moden that I baling wired together in my pre Epicor days. Least I can do is pass it on.

Speaking as a SaaS user, doing that in BAQ instead of a civilized text editor is a little tricky and arcane. So I kept a base BAQ as a copy source for later. Now that Iâ€™m back at my work computerâ€¦

CalendarBase.baq (34.5 KB)

Next level fun challenge is working time! What time is 33 hours and 18 minutes ago, in work time, accounting for days off, partial days, breaks, etc.? I havenâ€™t needed to write that one yet.

2 Likes

Nice John!