I have this formula:
datediff (day,PORel.PromiseDt,RcvDtl.ReceiptDate)
I am working on an vendor OnTime report score card and i want to calculate if an order was late. But i dont want to to include the weekend in that calculation.
Any ideas on how to do this?
There has been lots of discussion date tables around here. Some solutions are more complicated than others. I personally like to find a well populated table that more than likely has all of the dates that I need, then only grab the date field and group by it. It’s a ready made list of dates. An example for past dates would be the LaborHead or LaborDtl table, people have to clock in and out on working days, so the days that wouldn’t be in the list are generally non-working days, and you can filter out others (like weekends) after you have that list. It’s not 100% fool proof, so don’t use it for anything legally binding, or incredibly important, but most of the time it’s a quick an dirty way to capture a vast majority of the cases.
Other discussions have flowed around CTE queries, but run into problems with hitting maximum amount of recursion.
A third solution is to populate a UD table with dates simply for this type of use.
Depending on what you want, any of these could be viable options.
Yea i cant do your first option since we are a 24/7 shop. I dont understand how i could use a UD table to exclude the weekends from the datediff formula.