0

I'm trying to calculate how many business days have passed since a given date

CREATE TABLE `TestTable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

And this is my query, but I got stuck 

SELECT 
    SUM(5 * (DATEDIFF(NOW(), date) DIV 7) + MID('0123444401233334012222340111123400001234000123440',
        7 * WEEKDAY(date) + WEEKDAY(date) + 1,
        1) + ((TIMESTAMPDIFF(SECOND,
        NOW(),
        date) / 86400) - (DATEDIFF(NOW(), date)))) AS Business_Days, user, date
FROM
    MyDB.TestTable

If someone can help me telling me what I'm doing wrong.

Absorbing
  • 95
  • 7
Uffo
  • 9,628
  • 24
  • 90
  • 154
  • Because I need to select a lot of users, will be run as a cron job – Uffo Nov 22 '18 at 12:29
  • https://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates – jeroen Nov 22 '18 at 12:31
  • 3
    Please think about what constitutes a business day for your business. Are all holidays needed for the calculations? Are different holidays needed for different countries? Usually it's better to have a table of calendars in which you store all holidays (and weekends) and subtract those from the number of days between two dates to arrive at the number of business days. – Corion Nov 22 '18 at 12:57

0 Answers0