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

17

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

<code>SELECT DATEDIFF(end_date, start_date) from accounts
</code>

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

[РЕДАКТИРОВАТЬ

<code>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, '');
</code>
Да, у меня есть праздничный стол, и да, это нужно сделать в MySQL. Akram
Должно ли это быть сделано в MySQL? Вам может понадобиться отдельный стол с датами праздников на нем? И какое-то предложение where в вашем запросе. Robbo_UK

Ваш Ответ

5   ответов
21

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

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)

Так и должно быть.

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

Это работает нормально, но я получил отрицательный результат, например -14 вместо 14. Akram
У меня есть поле для успеха, позвольте мне опубликовать сценарий Akram
DAYOFWEEK() возвращает 1 для воскресенья и 7 для субботы, поэтому я считаю, что условие для праздников должно бытьDAYOFWEEK(holiday_date) between 2 and 6. Izydorr
Вы должны ограничить подзапрос ваших праздников диапазоном дат, который вы ищете в своем основном запросе. SELECT COUNT(*) FROM holidays ... WHERE date BETWEEN X AND Y). И у вас не должно быть дубликатов в таблице праздничных дней (или измените подзапрос, чтобы они не распознавались). Thorsten
Я вижу одну из твоих проблем в том, что в твоих данных естьcreated даты, которые выше, чемcompleted даты, которые превращают ваш результат в отрицательный. Честно говоря, я не знаю, возвращает ли запрос в моем ответе правильные значения для этого, вы должны это проверить. Если вы хотите получить положительные результаты, вы можете использовать MySQLsabs() функция. Thorsten
1

ходные дни в таблицу.

SELECT 
  DATEDIFF(end_date, start_date) 
FROM table
WHERE date NOT IN (SELECT date FROM holidaydatestable )
Вы добавляете даты выходных в праздничный стол Robbo_UK
Как узнать все выходные за первые 10 лет Akram
Но как ты узнаешь дату выходного дня, я думаю, это невозмож Akram
3

в которой будут указаны все выходные и праздничные дни на следующие 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 function, затем просто используйте вышеизложенное и создайте функцию (есть много ресурсов о том, как создавать функции в каждой СУБД) .. просто убедитесь, что дали ей лучшее имя. ^ _ ^

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

1

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

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

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

Возможно ли достичь этого без использования циклов, я понятия не имею, поэтому я стекаюсь в этой точке. Мне было интересно, могу ли я использовать только определенную библиотеку, чтобы посчитать это? Akram

Похожие вопросы