04/03/2018 916 views
We are closed on weekends and I dont want to count those hours/days against ticket completion time
0 Comments   [ + ] Show comments


All Answers

This is a surprisingly complex problem in MySQL (other SQL flavors have built-in functions). Typically it's best for a database to have a calendar table and that makes the problem easier, but we don't have that in the KACE database. I'm taking my answer directly from this Stack Overflow thread. I suggest reading through that page for more information.

To calculate the number of business days between the start date @S and the end date @E: 
5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

NOTE: This does not account for holidays.

Answered 04/20/2018 by: JasonEgg
Red Belt