Как подсчитать разницу по дате, исключая выходные и праздничные дни в MySQL

Мне нужно подсчитать дни (рабочие дни) между двумя датами, исключая выходные (наиболее важные) и праздники

SELECT DATEDIFF(end_date, start_date) from accounts

Но я не знаю, как я должен это делать в MySQL, я нашел эту статью Количество дней между двумя датами, исключая выходные (только MySQL), Я не могу понять, как работать с функциональным запросом в mysql. Можете ли вы дать некоторую информацию о том, как добиться этого с помощью запроса mysql. Если я что-то пропустил, дайте мне знать.

[EDIT]

CREATE TABLE `candidatecase` (
  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID',
  `CreatedBy` int(11) NOT NULL,
  `UseraccountID` int(11) NOT NULL COMMENT 'User Account ID',
  `ReportReadyID` int(11) DEFAULT NULL COMMENT 'Report Ready ID',
  `DateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Date Created',
  `InitiatedDate` timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date Initiated',
  `ActualCompletedDate` timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date Completed Case',
  `ProjectedCompletedDate` timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date Projected Finish',
  `CheckpackagesID` int(11) DEFAULT NULL COMMENT 'Default Check Package Auto Assign Once Initiate Start',
  `Alacartepackage1` int(11) DEFAULT NULL COMMENT 'Ala carte Request #2',
  `Alacartepackage2` int(11) DEFAULT NULL COMMENT 'Ala carte Request #3',
  `OperatorID` int(11) NOT NULL COMMENT 'User Account - Operator',
  `Status` int(11) NOT NULL COMMENT 'Status',
  `caseRef` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=293 ;

--
-- Dumping data for table `candidatecase`
--

INSERT INTO `candidatecase` (`ID`, `CreatedBy`, `UseraccountID`, `ReportReadyID`, `DateCreated`, `InitiatedDate`, `ActualCompletedDate`, `ProjectedCompletedDate`, `CheckpackagesID`, `Alacartepackage1`, `Alacartepackage2`, `OperatorID`, `Status`, `caseRef`) VALUES
(1, 43, 70, NULL, '2011-07-22 02:29:31', '2011-07-07 07:27:44', '2011-07-22 02:29:31', '2011-07-17 06:53:52', 11, NULL, NULL, 44, 6, ''),
(2, 43, 74, NULL, '2012-04-03 04:17:15', '2011-07-11 07:07:23', '2011-07-13 05:32:58', '2011-07-21 07:01:34', 20, 0, 0, 51, 0, ''),
(3, 43, 75, NULL, '2011-07-29 04:10:07', '2011-07-11 07:27:12', '2011-07-29 04:10:07', '2011-07-21 07:02:14', 20, NULL, NULL, 45, 6, ''),
(4, 43, 78, NULL, '2011-07-18 03:32:27', '2011-07-11 07:51:31', '2011-07-13 02:18:34', '2011-07-21 07:37:53', 20, NULL, NULL, 45, 6, ''),
(5, 43, 76, NULL, '2011-07-29 04:09:19', '2011-07-11 07:51:11', '2011-07-29 04:09:19', '2011-07-21 07:38:30', 20, NULL, NULL, 45, 6, ''),
(6, 43, 77, NULL, '2011-07-18 03:32:49', '2011-07-11 07:51:34', '2011-07-18 02:18:46', '2011-07-21 07:39:00', 20, NULL, NULL, 45, 6, ''),
(7, 43, 79, NULL, '2011-07-18 03:33:02', '2011-07-11 07:53:24', '2011-07-18 01:50:12', '2011-07-21 07:42:57', 20, NULL, NULL, 45, 6, ''),
(8, 43, 80, NULL, '2011-07-29 04:10:38', '2011-07-11 07:53:58', '2011-07-29 04:10:38', '2011-07-21 07:43:14', 20, NULL, NULL, 45, 6, ''),
(9, 43, 81, NULL, '2011-07-18 03:31:54', '2011-07-11 07:53:49', '2011-07-13 02:17:02', '2011-07-21 07:43:43', 20, NULL, NULL, 45, 6, ''),
(11, 43, 88, NULL, '2011-07-18 03:15:53', '2011-07-13 04:57:38', '2011-07-15 08:57:15', '2011-07-23 04:39:14', 12, NULL, NULL, 44, 6, ''),
(13, 43, 90, NULL, '2011-07-26 07:39:24', '2011-07-13 12:16:48', '2011-07-26 07:39:24', '2011-07-23 12:13:50', 15, NULL, NULL, 51, 6, ''),
(63, 43, 176, NULL, '2011-09-13 08:23:13', '2011-08-26 10:00:32', '2011-09-13 08:23:13', '2011-09-05 09:58:47', 41, NULL, NULL, 45, 6, ''),
(62, 43, 174, NULL, '2011-08-24 03:54:30', '2011-08-24 03:53:13', '2011-08-24 03:54:30', '2011-08-29 03:52:48', 17, NULL, NULL, 51, 6, ''),
(61, 43, 173, NULL, '2011-08-24 03:55:05', '2011-08-24 03:53:39', '2011-08-24 03:55:05', '2011-08-29 03:52:36', 17, NULL, NULL, 51, 6, ''),
(60, 43, 172, NULL, '2011-08-24 03:22:41', '2011-08-24 03:21:50', '2011-08-24 03:22:41', '2011-08-29 03:21:11', 17, NULL, NULL, 51, 6, ''),
(59, 43, 171, NULL, '2011-08-24 03:23:19', '2011-08-24 03:22:00', '2011-08-24 03:23:19', '2011-08-29 03:20:57', 17, NULL, NULL, 51, 6, '');

Ответ 1

Возможно, вы захотите попробовать следующее:

Подсчитайте количество рабочих дней (взято из здесь)

SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1)

Это дает вам 261 рабочий день на 2012 год.
Теперь вам нужно знать свой отпуск, который не в выходные дни

SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6

Результат этого зависит от вашей праздничной таблицы.
Нам нужно получить это в одном запросе:

SELECT 5 * (DATEDIFF('2012-12-31', '2012-01-01') DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY('2012-01-01') + WEEKDAY('2012-12-31') + 1, 1) - (SELECT COUNT(*) FROM holidays WHERE DAYOFWEEK(holiday) < 6)

Это должно быть.

Изменить: Обратите внимание, что это работает только в том случае, если дата окончания превышает дату начала.

Ответ 2

Создайте таблицу, содержащую все выходные и праздничные дни, в течение следующих 100 лет.

Вам нужно указать, когда день является "праздником", так как никто не знает, какими будут праздники для 2052, но в то же время вы не сможете сделать точную функцию. просто обновляйте свой дневной рабочий стол каждый год, когда праздники станут известны (но вы всегда будете знать выходные).

Затем ваш запрос будет выглядеть следующим образом:

SELECT DATEFIFF(end_date, start_date) - COALESCE((SELECT COUNT(1) FROM nonWorkDays WHERE nonWorkDays.date BETWEEN start_date AND end_date), 0)
FROM accounts

Если вам действительно нужно написать функцию DATEDIFFWITHOUTWEEKENDSORHOLIDAYS, просто используйте приведенное выше и создайте функцию (там будет много ресурсов о том, как делать функции в каждой СУБД). Просто не забудьте дать ей лучшее имя. ^ _ ^

Одна вещь, которую вам нужно исправить, я думаю, что там +1 отсутствует где-то выше, например DATEDIFF (сегодня, сегодня), если сегодня выходной будет возвращать -1 вместо того, чтобы возвращать 0.

Ответ 3

Что-то вроде этого может работать. Добавьте все даты отпуска и даты выходных в таблицу.

SELECT 
  DATEDIFF(end_date, start_date) 
FROM table
WHERE date NOT IN (SELECT date FROM holidaydatestable )

Ответ 4

Попробуйте этот код, это не рассчитает количество дней без учета выходных дней

 SELECT
       (DATEDIFF(dd, @StartDate, @EndDate)+1)
      -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
from test_tbl where date NOT IN (SELECT date FROM holidaydatestable )

Ответ 5

Сделайте функцию, которая будет делать цикл while между датами, увеличивающими количество дней, когда это не будет в субботу или воскресенье.