안녕하세요 여러분, 달력에서 데이터를 입력받아,
년,월,일 시간을 입력받고있습니다.
입력받은 날짜와 시간값으로 달력에 예약이 완료되면, 예약완료되었다는 표시를 보여줘야하는데,
몇일째 고민하다가 어떻게 해결해야 할지 감이 안잡혀서 질문을 드립니다.
CHECK_IN_DAY , CHECK_IN_TIME , CHECK_OUT_DAY , CHECK_OUT_TIME 와 같이 컬럼이 있습니다.
샘플 데이터는 아래와 같습니다.
2019-02-12 | 00:00 | 2019-02-13 | 00:00 |
2019-02-13 | 01:00 | 2019-02-13 | 03:00 |
2019-02-13 | 03:00 | 2019-02-13 | 05:00 |
2019-02-13 | 05:00 | 2019-02-13 | 07:00 |
2019-02-13 | 07:00 | 2019-02-13 | 09:00 |
2019-02-13 | 15:00 | 2019-02-13 | 18:00 |
2019-02-13 | 23:00 | 2019-02-14 | 01:00 |
2019-02-14 | 01:00 | 2019-02-14 | 03:00 |
2019-02-14 | 22:00 | 2019-02-15 | 01:00 |
2019-02-15 | 01:00 | 2019-02-15 | 02:00 |
2019-02-15 | 02:00 | 2019-02-15 | 05:00 |
2019-02-15 | 05:00 | 2019-02-15 | 07:00 |
2019-02-15 | 07:00 | 2019-02-15 | 23:00 |
2019-02-15 | 23:00 | 2019-02-16 | 00:00 |
2019-02-16 | 00:00 | 2019-02-16 | 01:00 |
2019-02-17 | 01:00 | 2019-02-17 | 02:00 |
2019-02-19 | 01:00 | 2019-02-20 | 02:00 |
2019-02-26 | 00:00 | 2019-02-27 | 00:00 |
2019-02-28 | 00:00 | 2019-02-28 | 01:00 |
2019-02-28 | 01:00 | 2019-02-28 | 02:00 |
2019-02-28 | 02:00 | 2019-02-28 | 03:00 |
2019-02-28 | 03:00 | 2019-02-28 | 04:00 |
2019-02-28 | 05:00 | 2019-02-28 | 06:00 |
2019-02-28 | 06:00 | 2019-02-28 | 07:00 |
2019-02-28 | 08:00 | 2019-02-28 | 09:00 |
2019-02-28 | 14:00 | 2019-02-28 | 15:00 |
2019-02-28 | 15:00 | 2019-02-28 | 16:00 |
2019-02-28 | 16:00 | 2019-02-28 | 17:00 |
체크인 날로 부터 24시까지 모두 예약이 완료되면 해당일은 예약이 완료 되었다고 표시해야합니다.
예를들어
체크인 체크아웃
2019-02-13 01:00 ~ 2019-02-14 02:00
이렇게 예약이 되면
2019-02-13 00:00 ~ 2019-02-13 01:00 까지의 1시간 예약가능하기떄문에 예약이 모두 다찬것은 아닙니다.
00:00 ~ 24:00 시간이 꽉차야지만 해당일의 예약이 모두 다찬것으로 보여지는데요.
2019-02-14 05:00 ~ 2019-02-16 17:00 이렇게 연박도 가능하고
문제가 쉽지않아 도움을 요청드립니다ㅠ 도와주세요.
달력은 아래와 같은 쿼리로 보여주고있습니다.
달력형태가 아닌 로우형태여도 상관없습니다.
SELECT ym
, MIN(CASE dw WHEN 1 THEN d END) Sun
, MIN(CASE dw WHEN 2 THEN d END) Mon
, MIN(CASE dw WHEN 3 THEN d END) Tue
, MIN(CASE dw WHEN 4 THEN d END) Wed
, MIN(CASE dw WHEN 5 THEN d END) Thu
, MIN(CASE dw WHEN 6 THEN d END) Fri
, MIN(CASE dw WHEN 7 THEN d END) Sat
FROM (SELECT date_format(dt,'%Y%m') ym
, Week(dt) w
, Day(dt) d
, DayofWeek(dt) dw
FROM (SELECT CONCAT(y, '0101') + INTERVAL a*100 + b*10 + c DAY dt
FROM (SELECT 0 a
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
) a
, (SELECT 0 b
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
) b
, (SELECT 0 c
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
) c
, (SELECT '2019' y) d
WHERE a*100 + b*10 + c < DayOfYear(CONCAT(y, '1231'))
) a
) a
where ym = '201901'
GROUP BY ym, w;
WITH RECURSIVE calendar_t AS ( SELECT '2019-02-01' dt UNION ALL SELECT dt + INTERVAL 1 DAY FROM calendar_t WHERE dt < '2019-02-28' ) , data_t AS ( SELECT '2019-02-12' check_in_day, '00:00' check_in_time, '2019-02-13' check_out_day, '00:00' check_out_time UNION ALL SELECT '2019-02-13', '01:00', '2019-02-13', '03:00' UNION ALL SELECT '2019-02-13', '03:00', '2019-02-13', '05:00' UNION ALL SELECT '2019-02-13', '05:00', '2019-02-13', '07:00' UNION ALL SELECT '2019-02-13', '07:00', '2019-02-13', '09:00' UNION ALL SELECT '2019-02-13', '15:00', '2019-02-13', '18:00' UNION ALL SELECT '2019-02-13', '23:00', '2019-02-14', '01:00' UNION ALL SELECT '2019-02-14', '01:00', '2019-02-14', '03:00' UNION ALL SELECT '2019-02-14', '22:00', '2019-02-15', '01:00' UNION ALL SELECT '2019-02-15', '01:00', '2019-02-15', '02:00' UNION ALL SELECT '2019-02-15', '02:00', '2019-02-15', '05:00' UNION ALL SELECT '2019-02-15', '05:00', '2019-02-15', '07:00' UNION ALL SELECT '2019-02-15', '07:00', '2019-02-15', '23:00' UNION ALL SELECT '2019-02-15', '23:00', '2019-02-16', '00:00' UNION ALL SELECT '2019-02-16', '00:00', '2019-02-16', '01:00' UNION ALL SELECT '2019-02-17', '01:00', '2019-02-17', '02:00' UNION ALL SELECT '2019-02-19', '01:00', '2019-02-20', '02:00' UNION ALL SELECT '2019-02-26', '00:00', '2019-02-27', '00:00' UNION ALL SELECT '2019-02-28', '00:00', '2019-02-28', '01:00' UNION ALL SELECT '2019-02-28', '01:00', '2019-02-28', '02:00' UNION ALL SELECT '2019-02-28', '02:00', '2019-02-28', '03:00' UNION ALL SELECT '2019-02-28', '03:00', '2019-02-28', '04:00' UNION ALL SELECT '2019-02-28', '05:00', '2019-02-28', '06:00' UNION ALL SELECT '2019-02-28', '06:00', '2019-02-28', '07:00' UNION ALL SELECT '2019-02-28', '08:00', '2019-02-28', '09:00' UNION ALL SELECT '2019-02-28', '14:00', '2019-02-28', '15:00' UNION ALL SELECT '2019-02-28', '15:00', '2019-02-28', '16:00' UNION ALL SELECT '2019-02-28', '16:00', '2019-02-28', '17:00' ) -- MariaDB 10.3 에서 테스트 했습니다. With 문 빼고 아래 쿼리만 봐주세요. SELECT dt , IFNULL(FLOOR(SUM(TIMEDIFF(e, s))/10000), 0) tm -- 예약시간 FROM (SELECT a.dt , GREATEST(ADDTIME(ADDDATE(dt, 0), 0), ADDTIME(ADDDATE(check_in_day , 0), check_in_time )) s , LEAST (ADDTIME(ADDDATE(dt, 1), 0), ADDTIME(ADDDATE(check_out_day, 0), check_out_time)) e FROM calendar_t a LEFT OUTER JOIN data_t b ON a.dt BETWEEN b.check_in_day AND b.check_out_day WHERE a.dt LIKE '2019-02%' ) a GROUP BY dt ;