How do i exclude weekends from counting in 'Days Open' column of reports ?
We are closed on weekends and I dont want to count those hours/days against ticket completion time
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
JasonEgg
6 years ago
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.