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)

Posted by: JasonEgg 2 years ago
Red Belt
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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login


This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ