Priority Dispatch BAQ - Days Sitting Calculation - Help Needed

Kinetic ERP
1

Situation

We have a baq that mimics the Priority Dispatch report that needs modified to show days sitting in the department. I have created a simple calculated field that calculates the difference between the last operation date to today. This method ignores our production availability/schedule by resource group.

Thought Process/Execution

Using information from the below post I have created the following expression:

case
    when PriorOp.Calculated_PriorOp is null then (DATEDIFF(dd, max(OpenJobOpenOps4.JobHead_CreateDate), Constants.Today)) - ((DATEDIFF(wk, max(OpenJobOpenOps4.JobHead_CreateDate), Constants.Today) * 2) + (CASE WHEN DATEPART(dw, max(OpenJobOpenOps4.JobHead_CreateDate)) = 1 THEN 1 ELSE 0 END) + (CASE WHEN DATEPART(dw, Constants.Today) = 7 THEN 1 ELSE 0 END))
    
    
    when PriorOp.Calculated_PriorOp is not null then (DATEDIFF(dd, max(LaborDtl1.ClockInDate), Constants.Today)) - ((DATEDIFF(wk, max(LaborDtl1.ClockInDate), Constants.Today) * 2) + (CASE WHEN DATEPART(dw, max(LaborDtl1.ClockInDate)) = 1 THEN 1 ELSE 0 END) + (CASE WHEN DATEPART(dw, Constants.Today) = 7 THEN 1 ELSE 0 END))
    
    else '9999'
end

This seems to ignore all Saturdays and Sundays accurately. What I want to do next is create a statement that also ignores Fridays. I have tried adding similar statements to the cases that look at the date parts for 1 and 7. These do not operate as expected.

What I need

I need a way of modifying the above code to exclude Fridays from the calculation like it does for Saturdays and Sundays.

Next Steps

Once we figure out how to filter out individual days I would like to add the following and statement to make for a dynamic date filtering process for all of our companies:

(CASE WHEN DATEPART(dw, Constants.Today) = 7 AND ResourceGroup.DailyCapacity7 = 0 THEN 1 ELSE 0 END))

Building statements for every day of the week, 1-7, should allow for the Days Sitting calculation to filter based on our resource schedule and give a more accurate reading on how we are doing.

Thanks for the help!

2

Note that the second statement in the original list has a ‘x2’ component for the number of days in a weekend. If you want to include Fridays as well, that becomes ‘x3’, and THEN the conditional parts need adjusting to suit starting and ending days.

If you want the flexibility to include ANY particular days, this will become quite complex.

3

Hey Daryl,

I am looking to make the code function for any day based on our Resource Group Availability schedule. Nice to hear from the person that made the code!

With this shouldnt we be able to multiply the

((DATEDIFF(wk, max(LaborDtl1.ClockInDate), Constants.Today) * 2)

by 7 instead of 2 and simply add condition statements to the end of it for each date?

Something else I don’t quite understand about the code is the use of the min date for the first condition statement and the use of the max date for the last condition statement.

4

Those CASE statements are only really to catch edge cases, so to speak.

The essence of this is that the first component gives you the number of days between two dates, then the second gives you the number of weeks between those same two dates. All else being equal, that means that’s how many weekends you have, so you multiply it by two and take that many days off.

The final CASE components catch the situations where, because of the start or finish date, the weekend calculation isn’t right.

If you want to exclude arbitrary days it needs to be constructed a bit differently.

Do you maintain a calendar within Epicor for these days? If so, it’s probably easier to leverage that.

5

How would I check to see if we have a calendar? I know that we have the availability by day of week set for each resource group, but I’m not sure if that’s the same or not.

6

In the client, look at Production Calendar Maintenance.

In a BAQ, look and see if you have relevant entries in ProdCal and ProdCalDay. If you can tie ProdCalDay into a query, you can simply count the days which are not discounted by being marked as not a Working Day in that table.

1 Like
7

I see a calendar listed. How would I count the days?

8

If you have criteria on ProdCalDay for the relevant calendar and ModifiedDay >= start date and ModifiedDay <= end date, you can then use SUM(CASE WHEN WorkingDay = 0 THEN 1 ELSE 0 END), and that will give you directly the number of days to subtract from your total days between start date and end date. Depending how your calendar is set up, you may still need to deal with the weekends separately.

1 Like
9

The prod calendar doesn’t have all the days. It only has exceptions. You’ll have to make a CTE to make a list of all of the days, then reference the calendar to figure out which ones to throw out. (It’s a pain)

One of the things that I’ve done to get calendar days instead of doing a CTE is picking a table that is going to have all the days (like laborHead) and only show the date and group by the date. This will get you all of the dates, and then you can remove them based on calendar exceptions. It’s not 100% reliable (it’s possible that a day could be missing), but if no one clocked in a day, you probably have other problems.

10

I’ve set up dedicated UD tables to hold a full date set some places, for the same reason. If you need the calculations a lot then it’s worth the additional maintenance of a whole table.

Even so, though, given a start and end date SQL can give you a total number of days easily enough, and you only need to subtract the exception count.