복붙노트

[SQL] MySQL의 함수는 두 날짜 사이의 작업 일 수를 찾을 수

SQL

MySQL의 함수는 두 날짜 사이의 작업 일 수를 찾을 수

Excel에서 두 날짜 사이의 일 수를 찾을 수 NETWORKDAYS () 함수를 가지고있다.

누구는 MySQL을위한 유사한 기능을 가지고? 휴일 복잡성을 추가하기 때문에,이 솔루션은 휴일을 처리 할 필요가 없습니다.

해결법

  1. ==============================

    1.이 표현 -

    이 표현 -

    5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

    시작 날짜 @S과 종료 날짜 @E 사이의 영업일 수를 계산합니다.

    이전 날짜 (@S)를 시작하지 않습니다 종료 날짜 (@E)을 가정합니다. DATEDIFF와 호환이 동일한 날짜에 시작과 끝 날짜 제로 일 수 있습니다. 휴일을 무시합니다.

    다음과 같이 숫자의 문자열이 구성됩니다. 테이블을 생성 일 시작하고 일을 종료, 행이 (WEEKDAY 월요일로 시작해야합니다 0)과 열뿐만 아니라 월요일 시작해야합니다. 을 작성 위에서 대각선은 0, 즉있다 (모두 0 아래쪽 좌우 월요일과 월요일, 화요일과 화요일 등) 사이의 일. 대각선에서 하루의 시작을 위해 (항상 0이어야합니다) 및 채우기에 한 번에 오른쪽 일일에 열입니다. 당신은에 착륙하는 경우 주말 (비 영업일) 열, 일의 수 변경되지 않습니다, 그것은 왼쪽에서 실시한다. 그렇지 않으면, 수 하나 영업일의 증가. 당신은의 끝에 도달 할 때 같은 행과 시작에 행 루프 다시는 때까지 계속 대각선 다시 도달합니다. 그런 다음 행으로 이동합니다.

    예를 들면 토요일과 일요일 가정은 영업일 수 없습니다 -

     | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

    그런 다음 문자열로 테이블에 49 개 값을 연결.

    당신이 어떤 버그를 발견하면 알려 주시기 바랍니다.

    -편집하다 개선 테이블 :

     | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 4 0

    개선 된 문자열 : '0123444401233334012222340111123400001234000123440'

    개선 된 표현 :

    5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
  2. ==============================

    2.이 솔루션은 훨씬 더 복잡 기본적 행렬 생성 방법의 로저 제외한 동일한 방식을 사용한다. 참고 :이 솔루션이 출력은 NETWORKDAYS와 호환되지 않습니다.

    이 솔루션은 훨씬 더 복잡 기본적 행렬 생성 방법의 로저 제외한 동일한 방식을 사용한다. 참고 :이 솔루션이 출력은 NETWORKDAYS와 호환되지 않습니다.

    로저의 솔루션과 마찬가지로,이 저장 프로 시저를 정의하지 않고 시작 날짜 (@S)과 종료 날짜 (@E) 사이의 영업일 수를 계산합니다. 그것은 종료 날짜가 시작 날짜 이전 아니라고 가정합니다. 같은 시작과 끝 날짜를 사용하면 0 휴일은 고려되지 않습니다 생성합니다.

    이 용액과 로저의 주요 차이점은 숫자의 매트릭스 생성 문자열 I을 포함하지 않은 복잡한 알고리즘에 의해 생성된다는 것이다. 이 알고리즘의 출력은 단위 테스트 (아래 테스트 입출력 참조)에 의해 검증된다. 매트릭스는, 주어진 x 및 y 값 쌍의 교점 (WEEKDAY (@S) 평일 (@E) 두 값 사이에 근무일의 차이를 산출한다. 두 개의 서로에 추가로 할당 순서를 실제로 중요하지 위치를 플롯.

    영업일은 월요일부터 금요일입니다

     | M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 5 0 1 2 3 4 4
W| 4 5 0 1 2 3 3
T| 3 4 5 0 1 2 2
F| 2 3 4 5 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0

    테이블의 49 개 값은 다음 문자열로 연결된다 :

    0123455501234445012333450122234501101234000123450

    결국, 정확한 표현은 다음과 같습니다

    5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

    나는이 솔루션을 사용하여 다음 입력과 출력을 확인했습니다 :

    Sunday, 2012-08-26 -> Monday, 2012-08-27 = 0
Sunday, 2012-08-26 -> Sunday, 2012-09-02 = 5
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Monday, 2012-09-10 = 10
Monday, 2012-08-27 -> Monday, 2012-09-17 = 15
Monday, 2012-08-27 -> Tuesday, 2012-09-18 = 16
Monday, 2012-08-27 -> Monday, 2012-09-24 = 20
Monday, 2012-08-27 -> Monday, 2012-10-01 = 25
Tuesday, 2012-08-28 -> Wednesday, 2012-08-29 = 1
Wednesday, 2012-08-29 -> Thursday, 2012-08-30 = 1
Thursday, 2012-08-30 -> Friday, 2012-08-31 = 1
Friday, 2012-08-31 -> Saturday, 2012-09-01 = 1
Saturday, 2012-09-01 -> Sunday, 2012-09-02 = 0
Sunday, 2012-09-02 -> Monday, 2012-09-03 = 0
Monday, 2012-09-03 -> Tuesday, 2012-09-04 = 1
Tuesday, 2012-09-04 -> Wednesday, 2012-09-05 = 1
Wednesday, 2012-09-05 -> Thursday, 2012-09-06 = 1
Thursday, 2012-09-06 -> Friday, 2012-09-07 = 1
Friday, 2012-09-07 -> Saturday, 2012-09-08 = 1
Saturday, 2012-09-08 -> Sunday, 2012-09-09 = 0
Monday, 2012-09-24 -> Sunday, 2012-10-07 = 10
Saturday, 2012-08-25 -> Saturday, 2012-08-25 = 0
Saturday, 2012-08-25 -> Sunday, 2012-08-26 = 0
Saturday, 2012-08-25 -> Monday, 2012-08-27 = 0
Saturday, 2012-08-25 -> Tuesday, 2012-08-28 = 1
Saturday, 2012-08-25 -> Wednesday, 2012-08-29 = 2
Saturday, 2012-08-25 -> Thursday, 2012-08-30 = 3
Saturday, 2012-08-25 -> Friday, 2012-08-31 = 4
Saturday, 2012-08-25 -> Sunday, 2012-09-02 = 0
Monday, 2012-08-27 -> Monday, 2012-08-27 = 0
Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1
Monday, 2012-08-27 -> Wednesday, 2012-08-29 = 2
Monday, 2012-08-27 -> Thursday, 2012-08-30 = 3
Monday, 2012-08-27 -> Friday, 2012-08-31 = 4
Monday, 2012-08-27 -> Saturday, 2012-09-01 = 5
Monday, 2012-08-27 -> Sunday, 2012-09-02 = 5
  3. ==============================

    3.당신이 휴일 곳을 추적 할 필요가 있기 때문에, 일정 테이블은 적절한 것 같습니다 :

    당신이 휴일 곳을 추적 할 필요가 있기 때문에, 일정 테이블은 적절한 것 같습니다 :

    CREATE TABLE Calendar
(
     calendar_date     DATETIME     NOT NULL,
     is_holiday        BIT          NOT NULL,
     is_weekend        BIT          NOT NULL,
     CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)

    물론 필요의 당신은 당신이 이제까지 응용 프로그램에서 작동 할 수 어떤 시간 동안 모든 날짜와 함께 웁니다. 1900에서 2100에 가고, 1 년에만 365 (또는 366) 일이 있기 때문에 큰 문제가되지 않습니다. 그냥 당신이 모든 날짜뿐 아니라 휴일에로드 확인하십시오.

    당신이 사소한 될 필요가 없다는 것과 같은 그 시점 질의에서 :

    SELECT
     COUNT(*)
FROM
     Calendar
WHERE
     calendar_date BETWEEN '2009-01-01' AND '2009-10-01' AND
     is_holiday = 0 AND
     is_weekend = 0

    경고 : 내가 MS SQL과 주로 일을하고 위를 조정할 필요가 있습니다, 그래서 오랜 시간에 MySQL과 일을하지 않았습니다. MySQL은 비트 데이터 형식이있는 경우 예를 들어, 난 기억하지 않는다.

  4. ==============================

    4.제안 된 문자열이 잘못 될 수 있을까?

    제안 된 문자열이 잘못 될 수 있을까?

    DATEDIFF '에서'제외 (에,에서). 같은 방식 때문에이 문자열이해야에서 :

    월요일 -> 금요일 = {월, 화, 수, 목} = 4

    월요일 -> 토요일 = {월, 화, 수, 목, 금} = 5

    화요일 -> 월요일 = {화는, 수요일은, 목은, 금,은, 일을 건너, 토를 건너, 월은 제외한다} = 4

    등등

    매트릭스 제안 :

     | M T W T F S S
-|--------------
M| 0 1 2 3 4 5 5
T| 4 0 1 2 3 4 4
W| 3 4 0 1 2 3 3
T| 2 3 4 0 1 2 2
F| 1 2 3 4 0 1 1
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 5 0

    문자열 : '0123455401234434012332340122123401101234000123450'

    내가 여기서 뭔가를 놓치고 있습니까? :)

  5. ==============================

    5.그냥 더 참조. 위의 나이지만 @Jeff Kooser의 수정 된 버전 근무의 없음 :

    그냥 더 참조. 위의 나이지만 @Jeff Kooser의 수정 된 버전 근무의 없음 :

    SELECT (DATEDIFF(date_end, date_start)) -
        ((WEEK(date_end) - WEEK(date_start)) * 2) -
        (case when weekday(date_end) = 6 then 1 else 0 end) -
        (case when weekday(date_start) = 5 then 1 else 0 end) -
        (SELECT COUNT(*) FROM holidays WHERE holiday>=date_start and holiday<=data_end)
  6. ==============================

    6.달의 첫 날을 감안할 때,이 해당 월 내에서 평일의 수를 반환합니다. MySQL을합니다. 저장 프로 시저없이.

    달의 첫 날을 감안할 때,이 해당 월 내에서 평일의 수를 반환합니다. MySQL을합니다. 저장 프로 시저없이.

    SELECT (DATEDIFF(LAST_DAY(?),?) + 1) - 
    ((WEEK(LAST_DAY(?)) - WEEK(?)) * 2) -
    (case when weekday(?) = 6 then 1 else 0 end) - 
    (case when weekday(LAST_DAY(?)) = 5 then 1 else 0 end)
  7. ==============================

    7.야다하여 위의 기능을 바탕으로, 여기에 목표 일까지 (포함하지 않음) 현재 날짜에서 왼쪽 작업 일을 계산 주제에 약간의 변화입니다. 또한 이스라엘에서 다른 주말을 처리 :-) 목표 날짜가 지난 경우이 (내가 원하던 것입니다) 부정적인 결과를 않습니다.

    야다하여 위의 기능을 바탕으로, 여기에 목표 일까지 (포함하지 않음) 현재 날짜에서 왼쪽 작업 일을 계산 주제에 약간의 변화입니다. 또한 이스라엘에서 다른 주말을 처리 :-) 목표 날짜가 지난 경우이 (내가 원하던 것입니다) 부정적인 결과를 않습니다.

    DELIMITER //
DROP FUNCTION IF EXISTS WORKDAYS_LEFT//

CREATE FUNCTION WORKDAYS_LEFT(target_date DATE, location char(2))
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
  DECLARE start_date DATE;
  DECLARE end_date DATE;
  DECLARE check_date DATE;
  DECLARE diff INT;
  DECLARE extra_weekend_days INT;
  DECLARE weeks_diff INT;

  SET start_date = CURDATE();
  SET end_date = target_date;
  SET diff = DATEDIFF(end_date, start_date);
  SET weeks_diff = FLOOR(diff / 7);
  SET end_date = DATE_SUB(end_date, INTERVAL (weeks_diff * 7) DAY);
  SET check_date = DATE_ADD(start_date, INTERVAL 1 DAY);
  SET extra_weekend_days = 0;
  WHILE check_date <= end_date DO
    SET extra_weekend_days = extra_weekend_days +
      IF(DAYNAME(check_date) = 'Saturday', 1, 0) +
      IF(DAYNAME(check_date) = IF(location = 'IL','Friday', 'Sunday'), 1, 0);
    SET check_date = DATE_ADD(check_date, INTERVAL 1 DAY);
  END WHILE;

  RETURN diff - weeks_diff*2 - extra_weekend_days;
END//

DELIMITER ;
  8. ==============================

    8.야다의 솔루션은 제대로 작동하지 않습니다. 내 변경 :

    야다의 솔루션은 제대로 작동하지 않습니다. 내 변경 :

    DELIMITER $$

DROP FUNCTION IF EXISTS `catalog`.`WORKDAYS` $$
CREATE FUNCTION `catalog`.`WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC

BEGIN

  DECLARE start_date DATE;
  DECLARE end_date DATE;
  DECLARE diff INT;

  IF (first_date < second_date) THEN
    SET start_date = first_date;
    SET end_date = second_date;
  ELSE
    SET start_date = second_date;
    SET end_date = first_date;
  END IF;

  SET diff = DATEDIFF(end_date, start_date);

  RETURN (CASE WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Saturday' THEN diff
               WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Sunday' THEN (diff - 2)

               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Sunday' THEN (diff - 1)
               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Saturday' THEN (diff + 1)
               WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) in ('Saturday', 'Sunday') THEN (diff + 1)

               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff -1)
               WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff + 1)

               WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) not in ('Saturday', 'Sunday')
                    && WEEKDAY(start_date) > WEEKDAY(end_date) THEN (diff - 2)
               ELSE diff END)
    - (FLOOR(diff / 7) * 2)
    - (CASE WHEN DAYNAME(start_date) = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DAYNAME(end_date) = 'Saturday' THEN 1 ELSE 0 END);

END $$

DELIMITER ;
  9. ==============================

    9.이 쿼리는 쉽게 두 날짜가 주말을 제외 사이의 작업 일 수를 반환합니다 :

    이 쿼리는 쉽게 두 날짜가 주말을 제외 사이의 작업 일 수를 반환합니다 :

    select datediff('2016-06-19','2016-06-01') - (floor(datediff('2016-06-19','2016-06-01')/6) + floor(datediff('2016-06-19','2016-06-01')/7));
  10. ==============================

    10.OK 소년 소녀는, 여기 분명히 최적의 솔루션을 가지고 두 날짜 사이의 평일의 수를 얻을 수있는 간단한 선택 진술했습니다.

    OK 소년 소녀는, 여기 분명히 최적의 솔루션을 가지고 두 날짜 사이의 평일의 수를 얻을 수있는 간단한 선택 진술했습니다.

    select 
    FLOOR(DATEDIFF(later_date, earlier_date) / 7) * 5 +  
    least(DATEDIFF(later_date, earlier_date) % 7, 5) + 
    if(weekday(later_date) < weekday(earlier_date), -2, 0);

    간단한 설명

  11. ==============================

    11.당신이 "무시 휴일"파에있을 것이다 문제는 각 나라가 서로 다른 휴가를 것입니다.

    당신이 "무시 휴일"파에있을 것이다 문제는 각 나라가 서로 다른 휴가를 것입니다.

    당신은 당신의 국가의 휴일을 정의하여 시작하고 특정 날짜가 휴일이 있는지 확인하기 위해 그들을 통과해야합니다.

    난 당신이 MySQL의에서 무엇을 원하는가 일반적인 기능으로 모르는

    죄송합니다!

  12. ==============================

    12.비 주말 차이는이 방법으로 달성 될 수있다 :

    비 주말 차이는이 방법으로 달성 될 수있다 :

    CREATE FUNCTION `WDDIFF` (d0 DATE, d1 DATE) 
  RETURNS INT DETERMINISTIC 
  COMMENT 'Date0, Date1' 
BEGIN 
  RETURN DATEDIFF(d1, d0) - (DATEDIFF(DATE_SUB(d1, INTERVAL WEEKDAY(d1) DAY), DATE_ADD(d0, INTERVAL (7 - WEEKDAY(d0)) DAY))/7+1)*2 + IF(WEEKDAY(d0)>4, 1, 0) + 1; 
END

    사용법 : 달의 시작부터 주 일

    SELECT ap.WDDIFF (DATE_SUB (CURDATE (), INTERVAL DAYOFMONTH (CURDATE ()) - 1 DAY) CURDATE ())

    참고 :이 기능은 시작과 종료 날짜를 모두 계산

  13. ==============================

    13.당신이 진정으로 주말의 존재를 무시하고 싶은 경우에, 당신은 토 / 일에서 유래 그것은 월에 시작된 것처럼 것을 치료 뭔가 할 필요가; 뭔가 그게 토 / 일에 끝 정말 금에 끝난 것처럼 따라서, 주말에 시작 및 종료, 당신은 가지고 뭔가 시작과 끝을 모두 무시합니다. 나는 다른 답변의 이렇게 생각하지 않습니다.

    당신이 진정으로 주말의 존재를 무시하고 싶은 경우에, 당신은 토 / 일에서 유래 그것은 월에 시작된 것처럼 것을 치료 뭔가 할 필요가; 뭔가 그게 토 / 일에 끝 정말 금에 끝난 것처럼 따라서, 주말에 시작 및 종료, 당신은 가지고 뭔가 시작과 끝을 모두 무시합니다. 나는 다른 답변의 이렇게 생각하지 않습니다.

    다음 기능이 수행합니다

    CREATE DEFINER=`root`@`localhost` FUNCTION `weekdayDiff`
(
edate datetime,
sdate datetime
)
RETURNS int
DETERMINISTIC

BEGIN
if edate>sdate
then
 return 5 * (DATEDIFF(edate, sdate) DIV 7) + MID('+0+1+2+3+4+4+4+4+0+1+2+3+3+3+3+4+0+1+2+2+2+2+3+4+0+1+1+1+1+2+3+4+0+0+0+0+1+2+3+4-1-1+0+1+2+3+4+4-1', 2*(7 * WEEKDAY(sdate) + WEEKDAY(edate)) + 1, 2);
else
 return -(5 * (DATEDIFF(sdate, edate) DIV 7) + MID('+0+1+2+3+4+4+4+4+0+1+2+3+3+3+3+4+0+1+2+2+2+2+3+4+0+1+1+1+1+2+3+4+0+0+0+0+1+2+3+4-1-1+0+1+2+3+4+4-1', 2*(7 * WEEKDAY(edate) + WEEKDAY(sdate)) + 1, 2));
end if;

-- The following works unless both start and finish date are on weekends.
-- return 5 * (DATEDIFF(edate, sdate) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(sdate) + WEEKDAY(edate) + 1, 1);

END;

    로저의 대답의 언어, (그것의 경우 -1 대신 0으로 시작하고 토요일 / 일요일에 끝의 유일한 차이점) 아래에 위의 문자열을 만든 테이블에서 :

     |  M  T  W  T  F  S  S
-|---------------------
M| +0 +1 +2 +3 +4 +4 +4
T| +4 +0 +1 +2 +3 +3 +3
W| +3 +4 +0 +1 +2 +2 +2
T| +2 +3 +4 +0 +1 +1 +1
F| +1 +2 +3 +4 +0 +0 +0
S| +0 +1 +2 +3 +4 -1 -1
S| +0 +1 +2 +3 +4 +4 -1
  14. ==============================

    14.하지 2016-04에 예를 들어, 나를 위해 올바르게 작동 @Rodger Bagnall에 의해 게시 답변. 그것은 일일 덜 진짜에 있음을 보여줍니다.

    하지 2016-04에 예를 들어, 나를 위해 올바르게 작동 @Rodger Bagnall에 의해 게시 답변. 그것은 일일 덜 진짜에 있음을 보여줍니다.

    쿼리에 의해 계산에 대해 얘기하면 - 내가 이것을 사용 :

    set
@S = '2016-04-01',
@E = '2016-04-30';
 select
    case 
        when WEEKDAY(@S) < 5 then 5 - WEEKDAY(@S)
        else 0
    end #startweek
    +
    case 
        when WEEKDAY(@E) < 5 then WEEKDAY(@E) + 1
        else 5
    end #endweek
    +
    (
        DATEDIFF(@E, @S) + 1 # plus 1 day cause params is inside 1 month
        - (7 - WEEKDAY(@S)) # minus start week
        - (WEEKDAY(@E) + 1) # minus end week
    ) DIV 7 * 5 #rest part


    as work_date_count;

    쿼리 숫자가 어디에서 왔는지 보여주기 위해 단지 최적화되지

  15. ==============================

    15.

    SELECT  5* (DATEDIFF(u.EndDate, u.StartDate) DIV 7) + MID('1234555512344445123333451222234511112345001234550', 7 * WEEKDAY(u.StartDate) + WEEKDAY(u.EndDate) + 1, 1)

    다음과 같은 경우를 고려할 때이다 :

    1) = STARTDATE ENDDATE 경우, 길이 = 1 마찬가지로 ..

    나는 대부분의 투표 대답에 언급 내가 필요에 따라 결과를 가지고 논리를 사용하여 문자열을 계산했다.

  16. ==============================

    16.이 SQL Server 2005에서 작동

    이 SQL Server 2005에서 작동

    망가는 당신을위한거야 일 경우 알고있다.

    DECLARE @StartDate DATETIME,
        @EndDate DATETIME

SELECT  @StartDate = '22 Nov 2009',
        @EndDate = '28 Nov 2009'

;WITH CTE AS(
        SELECT  @StartDate DateVal,
                DATENAME(dw, @StartDate) DayNameVal
        UNION ALL
        SELECT  DateVal + 1,
                DATENAME(dw, DateVal + 1)
        FROM    CTE
        WHERE   DateVal < @EndDate
)
SELECT  COUNT(1)
FROM    (
            SELECT *
            FROM CTE
            WHERE DayNameVal NOT IN ('Sunday','Saturday')
        ) DayVals
  17. ==============================

    17.나는이 오래된 스레드 알지만, 내 솔루션은 어떤 사람들을 위해 도움이 될 수 있다고 생각했다. 이것은 내가 기능의 필요없이 비즈 일을 찾기 위해했던 쿼리입니다. 당신은 그냥 일부러 비워 당신이 원하는 필드 이름을 지정할 수 있습니다.

    나는이 오래된 스레드 알지만, 내 솔루션은 어떤 사람들을 위해 도움이 될 수 있다고 생각했다. 이것은 내가 기능의 필요없이 비즈 일을 찾기 위해했던 쿼리입니다. 당신은 그냥 일부러 비워 당신이 원하는 필드 이름을 지정할 수 있습니다.

    SELECT

   @tmp_s   := ept.`date_start`,
   @tmp_e   := IF(ept.`date_end` IS NULL, NOW(),ept.`date_end`),
   @start   := IF(DAYOFWEEK(@tmp_s)=1,@tmp_s + INTERVAL 1 DAY,(IF(DAYOFWEEK(@tmp_s)=7,@tmp_s + INTERVAL 2 DAY,@tmp_s)),
   @end     := IF(DAYOFWEEK(@tmp_e)=1,@tmp_e - INTERVAL 2 DAY,(IF(DAYOFWEEK(@tmp_e)=7,@tmp_e - INTERVAL 1 DAY,@tmp_e)),
   @bizdays := CASE
                  WHEN DATEDIFF(@end,@start)>7 THEN CEIL((DATEDIFF(@end,@start)/7)*5)
                  WHEN DAYOFWEEK(@end)< DAYOFWEEK(@start) THEN DATEDIFF(@end,@start)-2
                  ELSE DATEDIFF(@end,@start)
               END,
   DATE(@start),
   DATE(@end),
   IF(@bizdays>=10,10,@bizdays)

FROM `employee_points` ept
WHERE ept.`date_start` > '2011-01-01'
  18. ==============================

    18.만료일 시작일 7 일 이내에하고 주말에 걸쳐있을 때 NETWORKDAYS 들어 () 함수는 상기 하나 개 이상의 조건은 커버 케이스에 추가되어야한다.

    만료일 시작일 7 일 이내에하고 주말에 걸쳐있을 때 NETWORKDAYS 들어 () 함수는 상기 하나 개 이상의 조건은 커버 케이스에 추가되어야한다.

        RETURN (diff + 1)
    - (FLOOR(diff / 7) * 2)
    - (CASE WHEN DAYNAME(start_date) = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DAYNAME(end_date) = 'Saturday' THEN 1 ELSE 0 END)
    - (CASE WHEN diff<7 and WEEK(start_date)<>WEEK(end_date) THEN 2 ELSE 0 end);
  19. ==============================

    19.아주 오래 된 포스트하지만 많은 도움이 있지만. @shahcool가 제공하는 솔루션에 따라 정확한 일을 반환하지 않습니다 예를 들면

    아주 오래 된 포스트하지만 많은 도움이 있지만. @shahcool가 제공하는 솔루션에 따라 정확한 일을 반환하지 않습니다 예를 들면

    주중 ( '2013년 3월 26일', '2013년 4월 1일') 반환 3 일 그러나 실제로이 있어야 5 일

    아래는 내가 테스트 및 반환 정확한 작업 일 한 솔루션입니다

    DELIMITER $$
DROP FUNCTION IF EXISTS WORKDAYS $$
CREATE FUNCTION `WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC

BEGIN

DECLARE start_date DATE;
DECLARE end_date DATE;
DECLARE diff INT;
DECLARE NumberOfWeeks INT;
DECLARE RemainingDays INT;
DECLARE firstDayOfTheWeek INT;
DECLARE lastDayOfTheWeek INT;
DECLARE WorkingDays INT;  

IF (first_date < second_date) THEN
SET start_date = first_date;
SET end_date = second_date;
ELSE
SET start_date = second_date;
SET end_date = first_date;
END IF;

## Add one to include both days in interval
SET diff = DATEDIFF(end_date, start_date)+1;
SET NumberOfWeeks=floor(diff/7);
SET RemainingDays=MOD(diff,7);
SET firstDayOfTheWeek=DAYOFWEEK(start_date);
SET lastDayOfTheWeek=DAYOFWEEK(end_date); 


IF(firstDayOfTheWeek <= lastDayOfTheWeek) THEN 

   IF( firstDayOfTheWeek<=6 AND 6 <=lastDayOfTheWeek) THEN SET        RemainingDays=RemainingDays-1; END IF;
   IF( firstDayOfTheWeek<=7 AND 7 <=lastDayOfTheWeek) THEN SET RemainingDays=RemainingDays-1; END IF; 
   ELSE
       IF( firstDayOfTheWeek=7) THEN SET RemainingDays=RemainingDays-1;
         IF (lastDayOfTheWeek=6) THEN  SET RemainingDays=RemainingDays-1; END IF;  
       ELSE SET RemainingDays=RemainingDays-2;
       END IF;
   END IF;

   SET WorkingDays=NumberOfWeeks*5;

   IF(RemainingDays>0) THEN RETURN WorkingDays+RemainingDays;

   ELSE RETURN WorkingDays; END IF;

 END $$

 DELIMITER ;
  20. ==============================

    20.(포함) 2 날짜 사이의 일을 반환 MYSQL 기능. 2와 6 사이에 월요일부터 금요일까지이며, 이것은 일정 / 지역에 따라 조정할 수 있습니다.

    (포함) 2 날짜 사이의 일을 반환 MYSQL 기능. 2와 6 사이에 월요일부터 금요일까지이며, 이것은 일정 / 지역에 따라 조정할 수 있습니다.

    -- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_GetBusinessDaysBetweenDates`(d1 DATE, d2 DATE) RETURNS int(11)
BEGIN
    DECLARE bDaysInPeriod INT;

    SET bDaysInPeriod=0;
    WHILE d1<=d2 DO
        IF DAYOFWEEK(d1) BETWEEN 2 AND 6 THEN
            SET bDaysInPeriod=bDaysInPeriod+1;
        END IF;

        SET d1=d1+INTERVAL 1 day;
    END WHILE;

    RETURN bDaysInPeriod;
END
  21. ==============================

    21.

    Below function will give you the Weekdays, Weekends, Date difference with proper results:

You can call the below function like,
select getWorkingday('2014-04-01','2014-05-05','day_diffs');
select getWorkingday('2014-04-01','2014-05-05','work_days');
select getWorkingday('2014-04-01','2014-05-05','weekend_days');




    DROP FUNCTION IF EXISTS PREPROCESSOR.getWorkingday;
    CREATE FUNCTION PREPROCESSOR.`getWorkingday`(d1 datetime,d2 datetime, retType varchar(20)) RETURNS varchar(255) CHARSET utf8
    BEGIN
     DECLARE dow1, dow2,daydiff,workdays, weekenddays, retdays,hourdiff INT;
        declare newstrt_dt datetime;
       SELECT dd.iDiff, dd.iDiff - dd.iWeekEndDays AS iWorkDays, dd.iWeekEndDays into daydiff, workdays, weekenddays
      FROM (
       SELECT
         dd.iDiff,
         ((dd.iWeeks * 2) + 
          IF(dd.iSatDiff >= 0 AND dd.iSatDiff < dd.iDays, 1, 0) + 
          IF (dd.iSunDiff >= 0 AND dd.iSunDiff < dd.iDays, 1, 0)) AS iWeekEndDays
           FROM (
          SELECT  dd.iDiff, FLOOR(dd.iDiff / 7) AS iWeeks, dd.iDiff % 7 iDays, 5 - dd.iStartDay AS iSatDiff,  6 - dd.iStartDay AS iSunDiff
         FROM (
          SELECT
            1 + DATEDIFF(d2, d1) AS iDiff,
            WEEKDAY(d1) AS iStartDay
          ) AS dd
        ) AS dd
      ) AS dd ;
      if(retType = 'day_diffs') then
      set retdays = daydiff; 
     elseif(retType = 'work_days') then
      set retdays = workdays; 
     elseif(retType = 'weekend_days') then  
      set retdays = weekenddays; 
     end if; 
        RETURN retdays; 
        END;


Thank You.
Vinod Cyriac.
Bangalore
  22. ==============================

    22.나는 두 가지 기능을 필요로했다. 하나는 날짜 / 빼기 X 영업일을 추가하는 두 날짜와 하나 사이에 일의 수를 계산합니다. 여기에 내가 인터넷에서 발견 예에서 함께 넣어 것입니다. 그들은 가까이 서로의 계산뿐만 아니라 칭찬과 표준 DATEDIFF ()와 DATE_ADD () 함수로 만들어집니다. 예를 들어, DateDiffBusiness ( '2014년 5월 14일'DateAddBusiness는 ( '2014년 5월 14일', 5)) (5)와 동일 할 것이다.

    나는 두 가지 기능을 필요로했다. 하나는 날짜 / 빼기 X 영업일을 추가하는 두 날짜와 하나 사이에 일의 수를 계산합니다. 여기에 내가 인터넷에서 발견 예에서 함께 넣어 것입니다. 그들은 가까이 서로의 계산뿐만 아니라 칭찬과 표준 DATEDIFF ()와 DATE_ADD () 함수로 만들어집니다. 예를 들어, DateDiffBusiness ( '2014년 5월 14일'DateAddBusiness는 ( '2014년 5월 14일', 5)) (5)와 동일 할 것이다.

    DROP FUNCTION IF EXISTS DateDiffBusiness;
DELIMITER &
CREATE FUNCTION DateDiffBusiness( d2 DATE, d1 DATE )
RETURNS INT
DETERMINISTIC
COMMENT 'Calculates the number of bussiness days between two dates'
BEGIN
  DECLARE dow1, dow2, days INT;
  SET dow1 = DAYOFWEEK(d1);
  SET dow2 = DAYOFWEEK(d2);
  SET days = FLOOR( DATEDIFF(d2,d1)/7 ) * 5 +
             CASE
               WHEN dow1=1 AND dow2=7 THEN 5
               WHEN dow1 IN(7,1) AND dow2 IN (7,1) THEN 0
               WHEN dow1=dow2 THEN 1
               WHEN dow1 IN(7,1) AND dow2 NOT IN (7,1) THEN dow2-1
               WHEN dow1 NOT IN(7,1) AND dow2 IN(7,1) THEN 7-dow1
               WHEN dow1<=dow2 THEN dow2-dow1+1
               WHEN dow1>dow2 THEN 5-(dow1-dow2-1)
               ELSE 0
             END;
  RETURN days-1;
END&
DELIMITER ;


DROP FUNCTION IF EXISTS DateAddBusiness;
DELIMITER &
CREATE FUNCTION DateAddBusiness(mydate DATE, numday INT) 
RETURNS DATE
DETERMINISTIC
COMMENT 'Adds bussiness days between two dates'
BEGIN
 DECLARE num_week INT DEFAULT 0;
 DECLARE num_day INT DEFAULT 0;
 DECLARE adj INT DEFAULT 0;
 DECLARE total INT DEFAULT 0;
 SET num_week = numday DIV 5;
 SET num_day = MOD(numday, 5);
 IF (WEEKDAY(mydate) + num_day >= 5) then
  SET adj = 2;
 END IF;
 SET total = num_week * 7 + adj + num_day;
 RETURN DATE_ADD(mydate, INTERVAL total DAY);
END&
DELIMITER ;
  23. ==============================

    23.Helooo 테스트하십시오.

    Helooo 테스트하십시오.

    DELIMITER $$

DROP FUNCTION IF EXISTS `WORKDAYS` $$
CREATE FUNCTION `WORKDAYS` (first_date DATETIME, second_date DATETIME) RETURNS INT
LANGUAGE SQL
DETERMINISTIC

BEGIN

  DECLARE start_date DATE;
  DECLARE end_date DATE;
  DECLARE diff INT;
  DECLARE cnt INT;

  IF (first_date < second_date) THEN
    SET start_date = first_date;
    SET end_date = second_date;
  ELSE
    SET start_date = second_date;
    SET end_date = first_date;
  END IF;

   SELECT COUNT(*) INTO cnt FROM `holiday` WHERE (hday BETWEEN start_date AND end_date) and (DAYOFWEEK(hday) != 7 and DAYOFWEEK(hday) != 1);

  SET diff = DATEDIFF(end_date, start_date) ;

  RETURN (CASE WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Saturday' THEN (diff - cnt)
               WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) = 'Sunday' THEN (diff - 2 - cnt)

               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Sunday' THEN (diff - 1 - cnt)
               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) = 'Saturday' THEN (diff + 1 - cnt)
               WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) in ('Saturday', 'Sunday') THEN (diff + 1 - cnt)

               WHEN DAYNAME(start_date) = 'Saturday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff -1 - cnt)
               WHEN DAYNAME(start_date) = 'Sunday' && DAYNAME(end_date) not in ('Saturday', 'Sunday') THEN (diff + 1 - cnt)

               WHEN DAYNAME(start_date) not in ('Saturday', 'Sunday') && DAYNAME(end_date) not in ('Saturday', 'Sunday')
                    && WEEKDAY(start_date) > WEEKDAY(end_date) THEN (diff - 2 - cnt)
               ELSE (diff - cnt)  END)
    - (FLOOR(diff / 7) * 2)
    - (CASE WHEN DAYNAME(start_date) = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DAYNAME(end_date) = 'Saturday' THEN 1 ELSE 0 END);

END $$

    테이블 휴가

    DROP TABLE IF EXISTS `holiday`;
CREATE TABLE `holiday` (
  `id` bigint(32) unsigned NOT NULL AUTO_INCREMENT,
  `hday` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `holiday` (`id`, `hday`) VALUES
(1, '2012-01-01'),
(2, '2012-05-01'),
(3, '2012-05-08'),
(4, '2012-07-05'),
(5, '2012-07-06'),
(6, '2012-09-28'),
(7, '2012-10-28'),
(8, '2012-11-17'),
(9, '2012-12-24'),
(10,    '2012-12-25'),
(11,    '2012-12-26');
etc...
  24. ==============================

    24.로저 Bagnall 용액 https://stackoverflow.com/a/6762805/218418에 기초 NETWORKDAYS.INTL을 에뮬레이트하는 기능

    로저 Bagnall 용액 https://stackoverflow.com/a/6762805/218418에 기초 NETWORKDAYS.INTL을 에뮬레이트하는 기능

    DELIMITER //
DROP FUNCTION IF EXISTS NETWORKDAYS//
CREATE FUNCTION NETWORKDAYS(sd DATE, ed DATE)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
  RETURN (5 * (DATEDIFF(ed, sd) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(sd) + WEEKDAY(ed) + 1, 1))+1;
END//
DELIMITER ;

    그리고 선택

    SELECT NETWORKDAYS('2015-01-01 06:00:00', '2015-01-20 06:00:00');
  25. ==============================

    25.이 DATEDIFF 모두 +에 대한 작품 적이 있고 - 제가 차 교체에 드롭입니다.

    이 DATEDIFF 모두 +에 대한 작품 적이 있고 - 제가 차 교체에 드롭입니다.

    DELIMITER $$
DROP FUNCTION IF EXISTS WORKDAYSDIFF$$
CREATE FUNCTION WORKDAYSDIFF(sd DATE, ed DATE)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC
BEGIN
 RETURN IF (sd >= ed, 
    5 * (DATEDIFF(sd, ed) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(ed) + WEEKDAY(sd) + 1, 1),
  -(5 * (DATEDIFF(ed, sd) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(sd) + WEEKDAY(ed) + 1, 1)) );
END$$
DELIMITER ;
  26. ==============================

    26.나는이 요구 사항을했고, (별도의 테이블을 사용하여) 특정 국가를 위해 주말과 휴일의 시간을 피하면서 계산할 수 완성 기능을 작성했습니다. 나는 설명과 플로우 차트 및 휴일 테이블 등의 생성과 함께 내 블로그 (http://mgw.dumatics.com/mysql-function-to-calculate-elapsed-working-time/)에 전체 기능과 세부 사항을 넣어 가지고 .. .... .I 기꺼이 여기를 둘 것입니다하지만 너무 오래 조금이다

    나는이 요구 사항을했고, (별도의 테이블을 사용하여) 특정 국가를 위해 주말과 휴일의 시간을 피하면서 계산할 수 완성 기능을 작성했습니다. 나는 설명과 플로우 차트 및 휴일 테이블 등의 생성과 함께 내 블로그 (http://mgw.dumatics.com/mysql-function-to-calculate-elapsed-working-time/)에 전체 기능과 세부 사항을 넣어 가지고 .. .... .I 기꺼이 여기를 둘 것입니다하지만 너무 오래 조금이다

    문제의 예 해결 :

    하자이 사건은 16 : 00 9시 사이에 열리는 'UK'의 사이트에 "12시 금요일 2016년 6월 10일"에 기록 된 말. 이 사건은 "14:00 화요일 2016년 6월 14일"에 대한 폐쇄되었습니다.

    상기 입사 함수 (일 (16시부터 9시까지) 월요일에서 금요일 960분 = 16시간 = [4 시간 (16시에서 12시까지) + 7시간 +로 5 시간 연령을 산출한다 들어 14시부터 9시까지)]

  27. ==============================

    27.나는이 솔루션을 사용, 마지막으로, 참조하시기 바랍니다 :

    나는이 솔루션을 사용, 마지막으로, 참조하시기 바랍니다 :

    DROP FUNCTION IF EXISTS datediff_workdays;
CREATE FUNCTION datediff_workdays(start_date DATE, end_date DATE) RETURNS INTEGER
BEGIN
  RETURN 5 * (DATEDIFF(end_date, start_date) DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY(start_date) + WEEKDAY(end_date) + 1, 1);
END
  28. ==============================

    28.시작 날짜와 종료 날짜 만 제외 종료 날짜 사이의 일 계산 상단의 대답.

    시작 날짜와 종료 날짜 만 제외 종료 날짜 사이의 일 계산 상단의 대답.

    또한 시작과 같은 주말에 끝이 토요일 2018년 5월 12일 토요일 2018년 5월 5일 말할 어떤 날짜를 들어, 하루 더 계산.

    여기에 나를 위해 완벽하게 작동하는 기능입니다!

    drop procedure if exists get_duration$$
create procedure get_duration(in data_from date, in data_to date)
begin
    if (WEEKDAY(data_from) = 5 AND WEEKDAY(data_to) = 5) 
    OR (WEEKDAY(data_from) = 6 AND WEEKDAY(data_to) = 6) then
        select (5 * (DATEDIFF(data_to, data_from) DIV 7) 
        + MID('0123444401233334012222340111123400001234000123440',
        7 * WEEKDAY(data_from) + WEEKDAY(data_to) + 1, 1)) dur;
    else 
        select (5 * (DATEDIFF(data_to, data_from) DIV 7) 
        + MID('0123444401233334012222340111123400001234000123440',
        7 * WEEKDAY(data_from) + WEEKDAY(data_to) + 1, 1))+1 dur;
    end if;
end$$
  29. ==============================

    29.내 팀의 일을 작업 합계를 계산하는 내 MySQL을 DB에 저장 프로 시저를 추가 (나는 근무일라고도 함) :

    내 팀의 일을 작업 합계를 계산하는 내 MySQL을 DB에 저장 프로 시저를 추가 (나는 근무일라고도 함) :

    RETURN ABS(DATEDIFF(date2, date1)) + 1
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7)
     - (SELECT DISTINCT COUNT(PriKey) FROM holidays WHERE date BETWEEN date1 AND date2)
     + (SELECT DISTINCT COUNT(PriKey) FROM weekenddaysworked WHERE date BETWEEN date1 AND date2)

    내 DB에 두 개의 테이블을 추가 : 휴일 두 열 (PriKey INT (11), 데이터 (날짜))와 모두와 weekenddaysworked

    휴일에 나는 고려 될 필요가 휴일을 추가로 내가 내 사람이 주말에 근무 날짜를 추가 weekenddaysworked.

    I는 결과로서 INT와 함수로서 순서를 첨가. 날짜 1과 날짜 2는 DATE로 정의됩니다.

    지금은 이렇게 같은 MySQL의 함수를 호출 할 수 있습니다 :

    WORKDAYS (날짜 1, 날짜 2) - 그래서 예 근무일에 대한 ( '2018년 11월 1일', '2018년 12월 1일')

  30. ==============================

    30.브라이언 - 라그 티와 @ 로저 - bagnall의 답변 @ @caveman에 약간의 구축, 내가 버전을 필요로 그 수 또한 쿼리 "전 평일"에 대한 계산 뒤로. 시작일 전이나 종료일 이후 인 경우에 적응 작동합니다.

    브라이언 - 라그 티와 @ 로저 - bagnall의 답변 @ @caveman에 약간의 구축, 내가 버전을 필요로 그 수 또한 쿼리 "전 평일"에 대한 계산 뒤로. 시작일 전이나 종료일 이후 인 경우에 적응 작동합니다.

    SELECT 5 * (DATEDIFF(@E, @S) DIV 7) +
  CASE WHEN @E < @S THEN
    -1 * MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(@E) + WEEKDAY(@S) + 1, 1)
  ELSE
    MID('0123455401234434012332340122123401101234000123450', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)
  END

    두 경우 모두에서 샘플 결과 :

    +------------+------------+-----------+
| @S         | @E         | wday_diff |
+------------+------------+-----------+
| 2019-11-25 | 2019-10-26 |       -20 |
| 2019-11-25 | 2019-11-28 |         3 |
+------------+------------+-----------+

    당신이 어떤 버그를 발견하면 알려 주시기 바랍니다.

    31. from https://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] (오라클) 각 그룹에 대해 최고 결과를 얻을 수 있습니다  (0) 2020.03.19
[SQL] WHERE COL IN (...) 조건에 대한 제한  (0) 2020.03.19
[SQL] 한 쿼리에서 두 테이블에서 삭제  (0) 2020.03.18
[SQL] 어떻게 SQL Server의 날짜에 BIGINT (UNIX 타임 스탬프)를 변환 할 수 있습니까?  (0) 2020.03.18
[SQL] 여러 값을 하나의 INSERT 대 여러 INSERT 문  (0) 2020.03.18
,

티스토리툴바