Hi everyone,
If I have two dates in a BAQ, how can I calculate the number of workdays between them in a calculated field? I’m not too bothered about holidays, although it would be good to include Bank holidays. I know it’s not gonna be easy but current results which are including days when no one is here are causing problems.
Any help would be much appreciated.
Adrian
How deep do you want to go?
We have a system which was quite involved to set up but solves the problem once and for all everywhere we need, but if you only need something roughly right then it might be overkill. I can detail it if you want, but I don’t want to clutter the thread unnecessarily!
Hi Daryl,
I must admit that having a solution that worked everywhere and included all non working days would be nice, for now though I have only committed to discounting the weekends. Once I can do that I would be really interested to know how you got your system working.
Well, quick and dirty uses the following components:
DATEDIFF(dd, [fromdate], [todate]) + 1 - for the total number of days (leave out the +1 if you don’t want days inclusive)
DATEDIFF(wk, [fromdate], [todate]) * 2 - for the number of weekend days in the most common cases
CASE WHEN DATEPART(dw, [fromdate]) = 1 THEN 1 ELSE 0 END - to subtract if starting on Sunday
CASE WHEN DATEPART(dw, [todate]) = 7 THEN 1 ELSE 0 END - to subtract if ending on Saturday
Line 1 with all the others subtracted should (I think) give you a weekday count between the two dates, but in any case playing around with those components should get you most of the way there if not.
Most more reliable solutions involve keeping a date table maintained, which we’ve found well worth while but may not be for everybody.
4 Likes
That’s excellent and will give us better than we have at the moment. Thanks very much Daryl!!
1 Like
Just out of interest @dhewi have you ever experienced problems trying to create a calculated field where the result is gained by subtracting one calculated field from another? I am almost finished this dashboard but my system keeps crashing when I try to include such fields.
It usually seems OK for me so far, but I have noticed odd behaviour when including calculated fields in other calculated fields at times I will admit. Nothing as serious as crashing, though. I’m never 100% confident in how Epicor puts BAQ editor pieces together into SQL.
But once I’m confident in the components of a calculation I normally try to put it in one field anyway. That way I know what it’s doing and can re-visit it in one place.
i tested your statement mate and it is working fine, this website is useful for quick tests, however it is based on normal standard working days, and as @Adrian_Mepham said it has not got the exception days like bank holiday, what i think is more efficient to do is to get the list of all days between any given dates ( i have got the code to do that but i need to put them in sql view list then do the comparison to Epicor data ) then compare them to Epicro Database specifically production calendar table to see if it is ticked as a working day and has no exception.
1 Like
Yes, that is better, at the expense of more set-up.
That’s why I asked initially at the top of the thread how serious a solution was required. We use a UD table ourselves, with BPMs that keep it in sync with Production Calendars to save doing the calculation each time it’s needed, and that’s rock-solid.
But a quick way to exclude the weekend days was all that was needed for the moment and this does that. (Assuming the crashes can be overcome).
1 Like
many thanks Daryl, i know mate, i am just trying to find a way to include the exceptions and be databased driven, could you share the main screen shot of this BPM, and your UD table
That’s what I’m aiming for in the future @A.Baeisa
I have it working now @dhewi, it seems a reboot was all that was required. Thanks for getting me this far.
Now, what’s next…
Good!
I will try to detail the other method tomorrow as @A.Baeisa requested, but I’m done for the day now.
Our solution is structured like this:
One UD table dedicated to dates and working days. This is initialised via SQL script to have a row per day for every day within the period we need Epicor to be able to cover for the recorded past and the immediate future. We keep the script to be able to extend the period when it becomes necessary.
Key1 and Date01 are set to the date of each day.
For each Production Calendar in use (we use three), one CheckBox field is set to true if the day is a working day in that calendar.
One Number field is incremented by one over the day before if the current day is a working day in that calendar.
So for the default calendar, CheckBox01 and Number01 are used.
A BPM on ProdCalDay.Update (data directive) then maintains the relevant CheckBox and Number fields if anyone makes changes to the working days in any of the Production Calendars.
Then, whenever you need working days in any BAQ (or SQL elsewhere), it can be calculated in a very lightweight way by reading the difference between the relevant Number field in the matching rows.
1 Like
The initialisation SQL is like this:
delete from Ice.UD20;
declare @rundate Date;
declare @rundayno1 int;
declare @workday1 bit;
declare @rundayno2 int;
declare @workday2 bit;
declare @rundayno3 int;
declare @workday3 bit;
declare @wday int;
set @rundate = '2016-01-01'; -- Start date
set @rundayno1 = 0;
set @rundayno2 = 0;
set @rundayno3 = 0;
while @rundate < '2021-01-01' -- End date
begin
set @wday = @@datefirst - datepart(dw, @rundate);
if ((@wday = 0) or (@wday = 6) or (@rundate in (select ModifiedDay from Erp.ProdCalDay where CalendarID = 'Calendar1' and WorkingDay = 0)))
set @workday1 = 0;
else
set @workday1 = 1;
if @workday1 = 1
set @rundayno1 = @rundayno1 + 1;
if ((@wday = 0) or (@wday = 6) or (@rundate in (select ModifiedDay from Erp.ProdCalDay where CalendarID = 'Calendar2' and WorkingDay = 0)))
set @workday2 = 0;
else
set @workday2 = 1;
if @workday2 = 1
set @rundayno2 = @rundayno2 + 1;
if ((@wday = 0) or (@wday = 6) or (@rundate in (select ModifiedDay from Erp.ProdCalDay where CalendarID = 'Calendar3' and WorkingDay = 0)))
set @workday3 = 0;
else
set @workday3 = 1;
if @workday3 = 1
set @rundayno3 = @rundayno3 + 1;
insert into Ice.UD20
(Company, Key1, Date01, CheckBox01, CheckBox02, CheckBox03, Number01, Number02, Number03)
values
('[CompanyID]', format(@rundate, 'yyyy-MM-dd'), @rundate, @workday1, @workday2, @workday3, @rundayno1, @rundayno2, @rundayno3);
set @rundate = dateadd(d,1,@rundate);
end
The BPM looks like this:
with custom code
string cal = (string)(from row in ttProdCalDay where row.RowMod == "A" || row.RowMod == "U" select row.CalendarID).FirstOrDefault() ?? string.Empty;
string calnum = string.Empty;
switch (cal)
{
case "Calendar1":
calnum = "01";
break;
case "Calendar2":
calnum = "02";
break;
case "Calendar3":
calnum = "03";
break;
}
if (calnum != string.Empty)
{
var ud20 = (from row in Db.UD20 where row.Company == Session.CompanyID && row.Date01 >= dayChanged select row);
strMsg = string.Empty;
using (var txScope = IceContext.CreateDefaultTransactionScope())
{
foreach (var ud20row in ud20)
{
if (ud20row.Date01 == dayChanged)
{
if ((bool)ud20row["CheckBox" + calnum] == workDay)
{
break;
}
else
{
ud20row["CheckBox" + calnum] = workDay;
}
}
if (workDay)
{
ud20row["Number" + calnum] = (decimal)ud20row["Number" + calnum] + 1;
}
else
{
ud20row["Number" + calnum] = (decimal)ud20row["Number" + calnum] - 1;
}
}
try
{
Db.Validate();
if (txScope != null)
{
txScope.Complete();
}
}
catch (Exception e)
{
strMsg = "Error updating Master Calendar." + System.Environment.NewLine + e.Message;
}
}
}
1 Like
Yes, but the useful part is the incremented number field.
Two example uses:
First, if you have a BAQ already returning two dates and you want to know the working days between them. You include the UD table in the query twice, joined to each date on Date01 in the respective UD table. The number of working days is a calculated field subtracting Number01 in one UD table from Number01 in the other UD table.
Second, if you have a single date and you need to know the date x number of working days in the future. Again you include the UD table twice. The first is joined to the date you already have on Date01. The second has a criteria filter of CheckBox01 = true and is joined to an expression of Number01 in the first UD table + x equals Number01 in the second UD table. Then Date01 in the second UD table is your working day x days in the future.
Many thanks Daryl, really appreciate that mate, but i am thinking of similar solution and could be easier, - i am thinking loudly here- all your feedbacks are welcome, i am trying to create a BPM to calculate running total of WorkingDays and Exceptions on a UD table, triggered every day, as well as updated/added events on ProdCal, the BPM basically have calendar info stored against date as well as running total (based on first day Epicor been used), then simply when any BPM/BAQ/SQL need to find the working days for any calendar all what you need to do is calling the running total for each date and subtracting them from each other with +1 to be inclusive if wanted.
That is exactly what we have, stored in UD20 in our case.
Your WDRunningTTL column is our UD20.Number01 for Calendar01, and your Date column is our UD20.Date01.
The key difference is that you are considering updating the table on a schedule whereas we do it live triggered by ProdCal changes.
the different that i want to make everything within Epicor, by triggering creating UD records every day by SysSched task not from MSQL, also wondering why can not we do this calculation -on the fly- by looping through each date between start and end period and compare against ProdCal days (working and exceptions)
I’m not sure where the difference is. apart from our set-up script …? And if you really wanted you could do the same as our script in Epicor code somewhere, we just never have because for us there’s no need.
Our UD tables just sit there static 99% of the time, and we haven’t touched them outside of Epicor since they were first put in place. The BPM runs when anyone makes a calendar change, and the UD tables supply the numbers in other BAQs and BPMs with very little calculation overhead the rest of the time.
And yes, you can perfectly well run a loop in code every time you need the results (though it’s more complex in a BAQ). This method simply saves you having to do that, and if you do it a lot (which we do) then it’s a significant saving.
There’s no one right answer, that I know, but this one has worked well for us for quite a while, and for us it minimises calculation load in areas where we need all the speed we can get. Your situation may be different and that’s fine.
1 Like