안녕하세요
고객이 별걸 다 요구하는군요 힘드네요
두개의 테이블이 있는데
하나는 시작시간과 종료시간만 마스타성 테이블 이구요 4시 부터 4시 반이라는 데이터 입니다.
하나는 시작시간 종료시간 의 초단위 상세 데이타가 있는데 4시부터 4시반사이의 데이터입니다.
두 테이블을 조인해서 비어 있는 시간 초단위 기준 시작 ~ 종료로 시간을 구하고 flag 를 넣어서
가상데이타는 0 실제 데이타는 1를 필드로 보여야 하는데 가능한지 문의드립니다.
감사합니다.
SELECT to_Date ('2014-11-20 04:00:00','YYYY-MM-DD HH24:MI:SS') start_time , to_date('2014-11-20 04:30:00','YYYY-MM-DD HH24:MI:SS') end_time
FROM dual
SELECT to_Date ('2014-11-20 04:24:53','YYYY-MM-DD HH24:MI:SS') sdt ,
to_date('2014-11-20 04:25:04','YYYY-MM-DD HH24:MI:SS') edt
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:25:05','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:25:14','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:26:28','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:27:00','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:27:01','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:27:12','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:27:13','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:27:26','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:27:27','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:27:37','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:27:38','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:27:48','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:27:49','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:28:18','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:28:19','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:28:30','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:28:31','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:03','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:29:04','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:15','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:29:16','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:28','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:29:29','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:42','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:29:43','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:55','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:29:56','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:29:59','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:30:00','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:30:10','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:30:11','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:30:23','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:30:24','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:30:34','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:30:35','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:30:48','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:30:49','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:31:00','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:31:01','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:31:12','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:31:38','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:37:53','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:37:54','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:38:08','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:38:09','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:38:23','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:38:24','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:38:40','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:38:41','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 03:43:50','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 03:43:51','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:06:37','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:06:38','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:06:51','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:06:52','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:07:06','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:07:07','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:07:27','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:07:28','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:07:40','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:07:41','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:08:08','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:08:09','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:08:19','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:08:20','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:08:33','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:08:34','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:08:44','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:08:45','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:08:55','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:08:56','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:09:08','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:09:09','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:09:23','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:10:01','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:10:11','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:10:12','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:10:23','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:10:24','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:10:36','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:10:37','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:10:50','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:10:51','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:11:02','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:11:03','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:11:16','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:11:17','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:24:41','YYYY-MM-DD HH24:MI:SS')
FROM dual
UNION ALL
SELECT to_Date ('2014-11-20 04:24:42','YYYY-MM-DD HH24:MI:SS'),
to_date('2014-11-20 04:24:52','YYYY-MM-DD HH24:MI:SS')
FROM dual
|