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'
THEN DATEADD(dd,5,POHeader.OrderDate)
WHEN DATENAME(WEEKDAY,POHeader.OrderDate) = 'Saturday'
THEN DATEADD(dd,6,POHeader.OrderDate)
ELSE DATEADD(dd,7,POHeader.OrderDate)
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'
THEN DATEADD(dd,18, @StartDate)
WHEN DATENAME(WEEKDAY, @StartDate) = 'Saturday'
THEN DATEADD(dd,19, @StartDate)
ELSE DATEADD(dd,20, @StartDate)
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'
THEN DATEADD(dd,28, @StartDate)
WHEN DATENAME(WEEKDAY, @StartDate) = 'Saturday'
THEN DATEADD(dd,29, @StartDate)
ELSE DATEADD(dd,30, @StartDate)
END
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'
THEN DATEADD(dd,28, @StartDate)
WHEN DATENAME(WEEKDAY, @StartDate) = 'Saturday'
THEN DATEADD(dd,29, @StartDate)
ELSE DATEADD(dd,30, @StartDate)
END
CASE WHEN DATENAME(WEEKDAY, @StartDate ) = 'Sunday'
THEN DATEADD(dd,26, @StartDate)
WHEN DATENAME(WEEKDAY, @StartDate) = 'Saturday'
THEN DATEADD(dd,27, @StartDate)
ELSE DATEADD(dd,28, @StartDate)
END
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.
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) {
dictionaryTable.Add(holiday.ModifiedDay, holiday.WorkingDay);
}
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
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
DATEADD(
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.
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…
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.
