시작일부터 종료일까지 매주 플래너 이벤트 표시

시작일부터 종료일까지 매주 플래너 이벤트 표시

나는 이 일로 지난 이틀 동안 머리를 긁적거렸다 - 나는 전문가의 안목이 필요하다!

기본적으로는 운전자를 위한 플래너 이벤트를 오프까지 1주일 동안 표시하려고 합니다.만약 드라이버가 2018년 12월 10일부터 12월 21일까지 쉬는 경우, 다음 날까지 운전자를 롤오버하여 종료일까지 이름을 표시해 주셨으면 합니다.현재 결과는 현재 날짜의 드라이버만 표시되며 매일 반복되지는 않습니다.

다음은 샘플 데이터 세트입니다.

CREATE TABLE IF NOT EXISTS planner_events (
  planner_id INT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_name VARCHAR(100) NOT NULL,     
  event_start_date DATE,    
  event_end_date DATE,
  user_id INT(5) NOT NULL,  
  user_type VARCHAR(10) NOT NULL,
  date_created DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO planner_events (event_name, event_start_date, event_end_date, user_id, user_type)
VALUES ('Annual Holiday', '2018-12-12', '2018-12-16', 101, 'driver'), 
('Alex on leave', '2018-12-12', '2018-12-15', 102, 'driver'), 
('Reminder', '2018-12-13', '2018-12-13', 103, 'driver');

CREATE TABLE IF NOT EXISTS drivers (
  driver_id INT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  driver_name VARCHAR(100) NOT NULL, 
  date_created DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO drivers (driver_id, driver_name)
VALUES (101, 'Alex'), (102, 'Tom'),(103, 'Trevor');

SQL Fielen을 작성했지만 기본적으로는 SQL Fielen에는 seq_0_to_99999 테이블이 저장되어 있지 않습니다.저는 최신 버전의 MariaDB를 사용하고 있습니다.

SQL Fidle 링크

MariaDB가 설치되어 있는 경우 위의 표와 SQL 문을 로컬머신에 복사할 수 있습니다.

기본적으로 코드에서 in_array 기능을 사용하여 특정 날짜에 드라이버가 꺼진 경우 가용성 상자에서 드라이버를 제거합니다.1개의 쿼리를 통해 이 작업을 수행하려고 합니다.이 쿼리는 일주일 중 빈 요일이나 드라이버가 쉬는 곳을 검색합니다.현재 쿼리는 지정된 날짜에 대한 엔트리가 있는 경우에만 작동하며 종료 날짜가 완료되지 않으면 이월되지 않습니다.

다음은 현재 쿼리를 실행한 좋은 예입니다.아래 화면을 보시면 Mario는 2019년 1월 1일까지 꺼지지만 19일, 20일, 21일, 22일, 23일은 공백이고 이름은 말하지 않습니다.어딘가에 크로스 조인(CROSS JOIN)이 필요할지도 모릅니다.논리적으로 생각해 보세요!

어떤 도움이라도 주시면 감사하겠습니다.타임아웃을 이용해 주셔서 감사합니다!

당신의 바이올린에 있는 질문을 이해하기 좀 힘들었어요.하지만 네가 원하는 건 이거야

select *
from planner_events e
join drivers d on d.driver_id=e.user_id
where e.event_start <= @enddate and e.event_end >= @startdate
  and e.user_type='driver'

여기서 @startdate는 관심 있는 기간의 첫 번째 날이고 @enddate는 마지막 날짜입니다.범위 사이즈가 항상 같거나 1주일 이내일 경우 @enddate를 계산으로 대체할 수 있습니다.

원하는 날짜 범위와 겹치는 이벤트가 있는 모든 드라이버의 목록이 나타납니다.원하는 것이 해당 날짜 범위에 이벤트가 없는 모든 드라이버인 경우 날짜 테스트를 되돌립니다.

아, 물론 "* 선택" 대신 실제로 관심 있는 분야를 나열해야 합니다.그냥 게으른 거였어요.

갱신하다

아, 그렇군요.주중에만 가능한 것이 아니라 매일 가능한 사람의 목록을 원합니다.

좋아, 내가 아는 한, 그러기 위해서는 매일의 기록이 있는 테이블이 필요해.이러한 테이블을 만드는 방법은 여러 가지가 있지만 표준 SQL을 사용하는 방법은 잘 모르겠습니다.그 테이블을 작성하는 것이 문제라면, 그 점에 대해 이야기할 수 있습니다만, 그렇지 않고, 그러한 테이블이 존재해 「근무일」이라고 불리며, 각 레코드에 「날짜 1」이라고 하는 날짜가 포함되어 있다고 가정해 보겠습니다.

그런 다음 다음과 같이 쿼리를 수행할 수 있습니다.

select w.date1, d.driver_id, d.driver_name
from workdays w
cross join drivers d
where w.date1 between @start and @end
 and not exists (select * from planner_event 
   where event_start<=w.date1 and event_end>=w.date1 and user_id=d.driver_id and user_type='driver')
order by w.date1, d.driver_id

그러면 날짜 및 사용 가능한 드라이버 목록이 포함된 결과 세트가 제공되며, 이 경우 원하는 표 형식으로 코드를 입력해야 합니다.매일 1열씩 드라이버별로 1열씩 결과 세트를 찾으시는 경우라면 분명 그렇게 할 수 있을 것입니다. 좀 더 조작해 봐야 합니다.매일 서브쿼리가 있는 지저분한 질문이 될 것 같습니다.

업데이트 2

아, 기간이 일주일이라는 걸 안다면 평일 테이블은 필요 없겠네요.다음과 같이 할 수 있습니다.

select d.driver_id, d.driver_name,
  case when exists (select * from planner_events where '2018-12-31' between event_start_date and event_end_date and user_id=d.driver_id) then 'N' else 'Y' end as Monday,
  case when exists (select * from planner_events where DATE_ADD('2018-12-31', INTERVAL 1 DAY) between event_start_date and event_end_date and user_id=d.driver_id) then 'N' else 'Y' end as Tuesday,
  case when exists (select * from planner_events where DATE_ADD('2018-12-31', INTERVAL 2 DAY) between event_start_date and event_end_date and user_id=d.driver_id) then 'N' else 'Y' end as Wednesday,
  case when exists (select * from planner_events where DATE_ADD('2018-12-31', INTERVAL 3 DAY) between event_start_date and event_end_date and user_id=d.driver_id) then 'N' else 'Y' end as Thursday,
  case when exists (select * from planner_events where DATE_ADD('2018-12-31', INTERVAL 4 DAY) between event_start_date and event_end_date and user_id=d.driver_id) then 'N' else 'Y' end as Friday
from drivers d
order by d.driver_id

앤드리(https://dba.stackexchange.com/users/6965/andriy-m),)라는 신사가 있습니다.그는 과거에 나를 도와줬고 그는 나의 기대에 부응했습니다.이 문제에 대한 또 다른 답변을 게시한 @Jay씨에게도 감사드립니다.

올바른 SQL 쿼리는 다음과 같습니다.

SELECT sub.planner_start_date, COALESCE(d.driver_name, 'N/A') AS driver_name, e.planner_id, e.event_name, e.event_start_date, e.event_start_time, e.event_end_date, e.event_end_time
FROM
( SELECT
        '2018-12-30' + INTERVAL seq.seq DAY AS planner_start_date
     FROM
        seq_0_to_999999 AS seq
     WHERE
        seq.seq <= TIMESTAMPDIFF(DAY, '2018-12-30', '2019-01-05')
    ) as sub
LEFT JOIN planner_events AS e ON e.event_start_date <= sub.planner_start_date AND e.event_end_date >= sub.planner_start_date
LEFT JOIN drivers AS d ON e.user_id = d.driver_id AND e.user_type = 'driver'

도와주신 모든 분들께 감사드립니다.

필요한 질문은 이것뿐인 것 같습니다.

SELECT d.driver_id
     , d.driver_name
     , e.planner_id
     , e.event_name
     , e.event_start_date
     , e.event_end_date
  FROM drivers d 
  JOIN planner_events e 
    ON e.user_id = d.driver_id;

그 외의 문제는 모두 디스플레이의 문제로 보입니다.어플리케이션 코드로 해결하는 것이 가장 좋습니다.

언급URL : https://stackoverflow.com/questions/53748383/display-planner-events-each-week-from-start-date-until-end-date