database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41230 게시물 읽기
No. 41230
oracel 프로시저 질문
작성자
김용완(sonyon21)
작성일
2016-09-06 18:17
조회수
6,645

RAWID   START_DTTS END_DTTS
1   2016/08/30 오전 8:59:14.800000 2016/08/30 오전 9:39:42.908000
2   2016/08/30 오전 9:00:24.281000 2016/08/30 오전 9:40:43.672000
3   2016/08/30 오전 9:01:33.196000 2016/08/30 오전 9:41:40.025000
4   2016/08/30 오전 9:02:42.234000 2016/08/30 오전 9:42:49.062000
5   2016/08/30 오전 9:03:51.293000 2016/08/30 오전 9:43:58.253000
6   2016/08/30 오전 9:05:00.570000 2016/08/30 오전 9:45:07.399000
7   2016/08/30 오전 9:06:09.606000 2016/08/30 오전 9:46:24.895000
8   2016/08/30 오전 9:07:17.655000 2016/08/30 오전 9:47:24.846000
9   2016/08/30 오전 9:08:25.525000 2016/08/30 오전 9:48:33.124000
10   2016/08/30 오전 9:09:33.754000 2016/08/30 오전 9:49:40.809000
11   2016/08/30 오전 9:10:41.819000 2016/08/30 오전 9:50:49.500000
12   2016/08/30 오전 9:11:10.487000 2016/08/30 오전 9:51:17.043000
13   2016/08/30 오전 9:11:55.036000 2016/08/30 오전 9:52:06.495000
14   2016/08/30 오전 9:13:02.760000 2016/08/30 오전 9:53:08.110000
15   2016/08/30 오전 9:14:09.980000 2016/08/30 오전 9:54:15.145000
16   2016/08/30 오전 9:15:18.920000 2016/08/30 오전 9:55:23.795000
17   2016/08/30 오전 9:16:27.264000 2016/08/30 오전 9:56:32.223000
18   2016/08/30 오전 9:17:53.209000 2016/08/30 오전 9:57:58.230000
19   2016/08/30 오전 9:18:57.661000 2016/08/30 오전 9:59:02.864000
20   2016/08/30 오전 9:20:03.325000 2016/08/30 오전 10:00:08.110000
21   2016/08/30 오전 9:21:11.456000 2016/08/30 오전 10:01:16.205000
22   2016/08/30 오전 9:22:19.542000 2016/08/30 오전 10:02:24.392000
23   2016/08/30 오전 9:23:44.756000 2016/08/30 오전 10:03:49.342000
24   2016/08/30 오전 9:24:49.488000 2016/08/30 오전 10:04:54.606000
25   2016/08/30 오전 9:25:54.580000 2016/08/30 오전 10:06:00.151000
26   2016/08/30 오전 9:27:03.115000 2016/08/30 오전 10:07:08.037000
27   2016/08/30 오전 9:28:10.822000 2016/08/30 오전 10:08:16.128000
28   2016/08/30 오전 9:29:09.134000 2016/08/30 오전 10:09:27.670000
29   2016/08/30 오전 9:30:40.142000 2016/08/30 오전 10:10:45.291000
30   2016/08/30 오전 9:31:45.948000 2016/08/30 오전 10:11:51.085000
31   2016/08/30 오전 9:32:54.753000 2016/08/30 오전 10:12:59.922000
32   2016/08/30 오전 9:34:02.237000 2016/08/30 오전 10:14:06.953000
33   2016/08/30 오전 9:35:07.214000 2016/08/30 오전 10:15:12.330000
34   2016/08/30 오전 9:36:04.211000 2016/08/30 오전 10:16:23.634000
35   2016/08/30 오전 9:37:36.940000 2016/08/30 오전 10:17:42.132000
36   2016/08/30 오전 9:38:46.476000 2016/08/30 오전 10:18:52.038000
37   2016/08/30 오전 9:39:54.386000 2016/08/30 오전 10:19:59.799000
38   2016/08/30 오전 10:04:25.645000 2016/08/30 오전 10:44:31.087000
39   2016/08/30 오후 1:45:09.982000 2016/08/30 오후 2:25:13.146000
40   2016/08/30 오후 1:46:16.474000 2016/08/30 오후 2:26:19.467000
41   2016/08/30 오후 1:47:22.588000 2016/08/30 오후 2:27:26.087000
42   2016/08/30 오후 1:48:28.952000 2016/08/30 오후 2:28:32.584000
43   2016/08/30 오후 1:49:36.280000 2016/08/30 오후 2:29:39.423000
44   2016/08/30 오후 1:50:43.520000 2016/08/30 오후 2:30:53.586000
45   2016/08/30 오후 1:51:50.799000 2016/08/30 오후 2:31:57.018000
46   2016/08/30 오후 1:52:58.100000 2016/08/30 오후 2:33:03.043000
47   2016/08/30 오후 1:54:05.993000 2016/08/30 오후 2:34:10.730000
48   2016/08/30 오후 1:55:14.024000 2016/08/30 오후 2:35:18.733000
49   2016/08/30 오후 1:56:22.384000 2016/08/30 오후 2:36:31.343000
50   2016/08/30 오후 1:57:30.449000 2016/08/30 오후 2:37:34.897000
51   2016/08/30 오후 1:58:38.052000 2016/08/30 오후 2:38:43.090000
52   2016/08/30 오후 1:59:45.989000 2016/08/30 오후 2:39:50.895000
53   2016/08/30 오후 2:00:54.286000 2016/08/30 오후 2:40:59.376000
54   2016/08/30 오후 2:02:02.294000 2016/08/30 오후 2:42:08.513000
55   2016/08/30 오후 2:03:10.201000 2016/08/30 오후 2:43:14.845000
56   2016/08/30 오후 2:04:17.809000 2016/08/30 오후 2:44:23.005000
57   2016/08/30 오후 2:05:25.676000 2016/08/30 오후 2:45:30.761000
58   2016/08/30 오후 2:06:33.224000 2016/08/30 오후 2:46:38.463000
59   2016/08/30 오후 2:07:40.620000 2016/08/30 오후 2:47:46.340000
60   2016/08/30 오후 2:08:47.976000 2016/08/30 오후 2:48:53.045000
61   2016/08/30 오후 2:09:55.432000 2016/08/30 오후 2:50:01.361000
62   2016/08/30 오후 2:11:02.988000 2016/08/30 오후 2:51:08.247000
63   2016/08/30 오후 2:12:10.566000 2016/08/30 오후 2:52:16.947000
64   2016/08/30 오후 2:13:18.371000 2016/08/30 오후 2:53:24.302000
65   2016/08/30 오후 2:14:25.921000 2016/08/30 오후 2:54:31.532000
66   2016/08/30 오후 2:15:34.123000 2016/08/30 오후 2:55:38.982000
67   2016/08/30 오후 2:16:41.243000 2016/08/30 오후 2:56:46.944000
68   2016/08/30 오후 3:07:12.375000 2016/08/30 오후 3:47:18.064000
69   2016/08/30 오후 3:20:47.465000 2016/08/30 오후 4:00:54.125000
70   2016/08/30 오후 3:21:53.800000 2016/08/30 오후 4:02:00.611000
71   2016/08/30 오후 3:23:00.112000 2016/08/30 오후 4:03:06.796000
72   2016/08/30 오후 3:24:07.177000 2016/08/30 오후 4:04:14.339000
73   2016/08/30 오후 3:25:14.062000 2016/08/30 오후 4:05:20.421000
74   2016/08/30 오후 3:26:21.399000 2016/08/30 오후 4:06:44.180000
75   2016/08/30 오후 3:27:28.523000 2016/08/30 오후 4:07:50.877000
76   2016/08/30 오후 3:28:36.520000 2016/08/30 오후 4:08:57.055000
77   2016/08/30 오후 3:29:44.450000 2016/08/30 오후 4:10:04.042000
78   2016/08/30 오후 3:30:52.472000 2016/08/30 오후 4:11:10.600000
79   2016/08/30 오후 3:32:00.686000 2016/08/30 오후 4:12:06.758000
80   2016/08/30 오후 3:33:08.240000 2016/08/30 오후 4:13:13.379000
81   2016/08/30 오후 3:34:15.740000 2016/08/30 오후 4:14:20.836000
82   2016/08/30 오후 3:35:23.498000 2016/08/30 오후 4:15:28.720000
83   2016/08/30 오후 3:36:31.404000 2016/08/30 오후 4:16:36.619000
84   2016/08/30 오후 3:37:39.362000 2016/08/30 오후 4:18:03.827000
85   2016/08/30 오후 3:38:46.835000 2016/08/30 오후 4:19:05.904000
86   2016/08/30 오후 3:39:54.609000 2016/08/30 오후 4:20:11.161000
87   2016/08/30 오후 3:41:02.339000 2016/08/30 오후 4:21:18.711000
88   2016/08/30 오후 3:42:09.827000 2016/08/30 오후 4:22:27.199000
89   2016/08/30 오후 3:43:17.569000 2016/08/30 오후 4:23:24.002000
90   2016/08/30 오후 3:44:25.109000 2016/08/30 오후 4:24:30.192000
91   2016/08/30 오후 3:45:32.543000 2016/08/30 오후 4:25:37.647000
92   2016/08/30 오후 3:46:40.316000 2016/08/30 오후 4:26:45.335000
93   2016/08/30 오후 3:47:28.592000 2016/08/30 오후 4:27:34.096000
94   2016/08/30 오후 3:48:36.185000 2016/08/30 오후 4:28:41.331000
95   2016/08/30 오후 3:49:43.885000 2016/08/30 오후 4:29:49.307000
96   2016/08/30 오후 3:50:51.838000 2016/08/30 오후 4:30:56.959000
97   2016/08/30 오후 3:52:01.128000 2016/08/30 오후 4:32:26.285000
98   2016/08/30 오후 3:53:13.325000 2016/08/30 오후 4:33:18.581000
99   2016/08/30 오후 3:54:22.470000 2016/08/30 오후 4:34:28.049000
100   2016/08/30 오후 3:55:31.586000 2016/08/30 오후 4:35:36.980000
101   2016/08/30 오후 3:56:40.186000 2016/08/30 오후 4:36:45.906000
102   2016/08/30 오후 3:57:48.948000 2016/08/30 오후 4:38:04.740000
103   2016/08/30 오후 3:58:57.861000 2016/08/30 오후 4:39:05.028000
104   2016/08/30 오후 4:00:07.258000 2016/08/30 오후 4:40:14.295000
105   2016/08/30 오후 4:01:04.764000 2016/08/30 오후 4:41:15.315000
106   2016/08/30 오후 4:02:11.243000 2016/08/30 오후 4:42:22.982000
107   2016/08/30 오후 4:03:17.680000 2016/08/30 오후 4:43:24.139000
108   2016/08/30 오후 4:04:25.666000 2016/08/30 오후 4:45:16.825000
109   2016/08/30 오후 4:05:31.904000 2016/08/30 오후 4:45:45.944000
110   2016/08/30 오후 4:06:55.308000 2016/08/30 오후 4:47:06.474000
111   2016/08/30 오후 4:08:02.249000 2016/08/30 오후 4:48:12.476000
112   2016/08/30 오후 4:09:08.888000 2016/08/30 오후 4:49:31.473000
113   2016/08/30 오후 4:10:15.782000 2016/08/30 오후 4:50:20.809000
114   2016/08/30 오후 4:11:22.438000 2016/08/30 오후 4:51:44.528000
115   2016/08/30 오후 4:12:18.830000 2016/08/30 오후 4:52:23.959000
116   2016/08/30 오후 4:13:24.838000 2016/08/30 오후 4:53:31.776000
117   2016/08/30 오후 4:14:32.324000 2016/08/30 오후 4:54:38.930000
118   2016/08/30 오후 4:15:40.326000 2016/08/30 오후 4:55:47.012000
119   2016/08/30 오후 4:16:48.219000 2016/08/30 오후 4:56:55.046000
120   2016/08/30 오후 4:18:15.457000 2016/08/30 오후 4:58:41.424000
121   2016/08/30 오후 4:19:17.509000 2016/08/30 오후 4:59:43.973000
122   2016/08/30 오후 4:20:22.637000 2016/08/30 오후 5:00:46.077000
123   2016/08/30 오후 4:21:30.671000 2016/08/30 오후 5:01:48.737000
124   2016/08/30 오후 4:22:39.152000 2016/08/30 오후 5:02:52.095000
125   2016/08/30 오후 4:23:35.611000 2016/08/30 오후 5:03:41.013000
126   2016/08/30 오후 4:24:41.446000 2016/08/30 오후 5:04:48.049000
127   2016/08/30 오후 4:44:55.802000 2016/08/30 오후 5:25:01.805000
128   2016/08/30 오후 4:45:28.430000 2016/08/30 오후 5:25:31.762000
129   2016/08/30 오후 4:46:38.652000 2016/08/30 오후 5:26:41.776000
130   2016/08/30 오후 4:47:45.270000 2016/08/30 오후 5:27:48.581000
131   2016/08/30 오후 4:49:13.324000 2016/08/30 오후 5:29:16.516000
132   2016/08/30 오후 4:49:43.202000 2016/08/30 오후 5:29:46.238000
133   2016/08/30 오후 4:50:32.675000 2016/08/30 오후 5:30:41.641000
134   2016/08/30 오후 4:51:56.245000 2016/08/30 오후 5:32:01.736000
135   2016/08/30 오후 4:52:35.669000 2016/08/30 오후 5:32:50.199000
136   2016/08/30 오후 4:53:43.261000 2016/08/30 오후 5:33:49.090000
137   2016/08/30 오후 4:54:50.287000 2016/08/30 오후 5:34:56.236000
138   2016/08/30 오후 4:55:58.511000 2016/08/30 오후 5:36:20.861000
139   2016/08/30 오후 4:57:06.158000 2016/08/30 오후 5:37:12.968000
140   2016/08/30 오후 4:58:24.105000 2016/08/30 오후 5:38:29.606000
141   2016/08/30 오후 4:58:53.034000 2016/08/30 오후 5:39:00.072000
142   2016/08/30 오후 4:59:55.627000 2016/08/30 오후 5:40:01.280000
143   2016/08/30 오후 5:00:57.918000 2016/08/30 오후 5:41:03.354000
144   2016/08/30 오후 5:02:00.576000 2016/08/30 오후 5:42:06.279000
145   2016/08/30 오후 5:03:03.731000 2016/08/30 오후 5:43:08.897000
146   2016/08/30 오후 5:03:52.855000 2016/08/30 오후 5:44:06.500000
147   2016/08/30 오후 5:04:59.644000 2016/08/30 오후 5:45:04.863000
148   2016/08/30 오후 5:06:08.241000 2016/08/30 오후 5:46:13.669000
149   2016/08/30 오후 5:07:15.944000 2016/08/30 오후 5:47:21.349000
150   2016/08/30 오후 5:08:24.059000 2016/08/30 오후 5:48:29.309000
151   2016/08/30 오후 5:09:38.941000 2016/08/30 오후 5:49:45.617000
152   2016/08/30 오후 5:10:47.918000 2016/08/30 오후 5:50:53.611000
153   2016/08/30 오후 5:11:56.051000 2016/08/30 오후 5:52:01.948000
154   2016/08/30 오후 5:13:04.211000 2016/08/30 오후 5:53:09.875000
155   2016/08/30 오후 5:14:12.362000 2016/08/30 오후 5:54:18.265000
156   2016/08/30 오후 5:15:20.290000 2016/08/30 오후 5:55:26.622000

 

제가 프로젝트를 수행하는데 오라클 프로시저 작업을 하는데 첨해보는 거라 이렇게 실례를 무릎쓰고 질문을 드립니다.

쿼리 질의를 했을 경우 위와 같이 데이터가 출력이 되고 위 결과를 가지고

작업시간을 구하는 데 로직은 END_DTTS에서 START_DTTS를 빼는것입니다.

그런데 문제는 위 예제 데이터의 구조를 보면 1번째 Row Data의 End_DTTS가 끝나기도 전에 2번째 Row Data의 Start_dtts가 시작을 합니다.

그래서 실제 작업시간을 구할때는 위의 색으로 구분 해놓은 것처럼 RawID=38번의 END_DTTS에서 RawID=1번의 START_DTTS를 빼야 합니다.

그 담에는 RawID=67의 END_DTTS에서 RawID=39번의 START_DTTS를 빼야 합니다.

그 이하의 데이터도 색으로  구분해 놓은 블록으로 END_DTTS에서 START_DTTS를 빼야합니다.

구글링으로 해봐도 정확하게 어떻게 해야 하는지 결과를 찾을 수가 없어 여기에 글을 올립니다.

많은 의견 부탁합니다.

 

이 글에 대한 댓글이 총 2건 있습니다.

WITH t0 AS
(
SELECT '2016/08/30 오전 08:59:14.800000' start_dtts, '2016/08/30 오전 09:39:42.908000' end_dtts FROM dual
UNION ALL SELECT '2016/08/30 오전 09:00:24.281000', '2016/08/30 오전 09:40:43.672000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:01:33.196000', '2016/08/30 오전 09:41:40.025000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:02:42.234000', '2016/08/30 오전 09:42:49.062000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:03:51.293000', '2016/08/30 오전 09:43:58.253000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:05:00.570000', '2016/08/30 오전 09:45:07.399000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:06:09.606000', '2016/08/30 오전 09:46:24.895000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:07:17.655000', '2016/08/30 오전 09:47:24.846000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:08:25.525000', '2016/08/30 오전 09:48:33.124000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:09:33.754000', '2016/08/30 오전 09:49:40.809000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:10:41.819000', '2016/08/30 오전 09:50:49.500000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:11:10.487000', '2016/08/30 오전 09:51:17.043000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:11:55.036000', '2016/08/30 오전 09:52:06.495000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:13:02.760000', '2016/08/30 오전 09:53:08.110000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:14:09.980000', '2016/08/30 오전 09:54:15.145000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:15:18.920000', '2016/08/30 오전 09:55:23.795000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:16:27.264000', '2016/08/30 오전 09:56:32.223000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:17:53.209000', '2016/08/30 오전 09:57:58.230000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:18:57.661000', '2016/08/30 오전 09:59:02.864000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:20:03.325000', '2016/08/30 오전 10:00:08.110000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:21:11.456000', '2016/08/30 오전 10:01:16.205000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:22:19.542000', '2016/08/30 오전 10:02:24.392000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:23:44.756000', '2016/08/30 오전 10:03:49.342000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:24:49.488000', '2016/08/30 오전 10:04:54.606000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:25:54.580000', '2016/08/30 오전 10:06:00.151000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:27:03.115000', '2016/08/30 오전 10:07:08.037000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:28:10.822000', '2016/08/30 오전 10:08:16.128000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:29:09.134000', '2016/08/30 오전 10:09:27.670000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:30:40.142000', '2016/08/30 오전 10:10:45.291000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:31:45.948000', '2016/08/30 오전 10:11:51.085000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:32:54.753000', '2016/08/30 오전 10:12:59.922000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:34:02.237000', '2016/08/30 오전 10:14:06.953000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:35:07.214000', '2016/08/30 오전 10:15:12.330000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:36:04.211000', '2016/08/30 오전 10:16:23.634000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:37:36.940000', '2016/08/30 오전 10:17:42.132000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:38:46.476000', '2016/08/30 오전 10:18:52.038000' FROM dual
UNION ALL SELECT '2016/08/30 오전 09:39:54.386000', '2016/08/30 오전 10:19:59.799000' FROM dual
UNION ALL SELECT '2016/08/30 오전 10:04:25.645000', '2016/08/30 오전 10:44:31.087000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:45:09.982000', '2016/08/30 오후 02:25:13.146000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:46:16.474000', '2016/08/30 오후 02:26:19.467000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:47:22.588000', '2016/08/30 오후 02:27:26.087000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:48:28.952000', '2016/08/30 오후 02:28:32.584000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:49:36.280000', '2016/08/30 오후 02:29:39.423000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:50:43.520000', '2016/08/30 오후 02:30:53.586000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:51:50.799000', '2016/08/30 오후 02:31:57.018000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:52:58.100000', '2016/08/30 오후 02:33:03.043000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:54:05.993000', '2016/08/30 오후 02:34:10.730000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:55:14.024000', '2016/08/30 오후 02:35:18.733000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:56:22.384000', '2016/08/30 오후 02:36:31.343000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:57:30.449000', '2016/08/30 오후 02:37:34.897000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:58:38.052000', '2016/08/30 오후 02:38:43.090000' FROM dual
UNION ALL SELECT '2016/08/30 오후 01:59:45.989000', '2016/08/30 오후 02:39:50.895000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:00:54.286000', '2016/08/30 오후 02:40:59.376000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:02:02.294000', '2016/08/30 오후 02:42:08.513000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:03:10.201000', '2016/08/30 오후 02:43:14.845000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:04:17.809000', '2016/08/30 오후 02:44:23.005000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:05:25.676000', '2016/08/30 오후 02:45:30.761000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:06:33.224000', '2016/08/30 오후 02:46:38.463000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:07:40.620000', '2016/08/30 오후 02:47:46.340000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:08:47.976000', '2016/08/30 오후 02:48:53.045000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:09:55.432000', '2016/08/30 오후 02:50:01.361000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:11:02.988000', '2016/08/30 오후 02:51:08.247000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:12:10.566000', '2016/08/30 오후 02:52:16.947000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:13:18.371000', '2016/08/30 오후 02:53:24.302000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:14:25.921000', '2016/08/30 오후 02:54:31.532000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:15:34.123000', '2016/08/30 오후 02:55:38.982000' FROM dual
UNION ALL SELECT '2016/08/30 오후 02:16:41.243000', '2016/08/30 오후 02:56:46.944000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:07:12.375000', '2016/08/30 오후 03:47:18.064000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:20:47.465000', '2016/08/30 오후 04:00:54.125000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:21:53.800000', '2016/08/30 오후 04:02:00.611000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:23:00.112000', '2016/08/30 오후 04:03:06.796000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:24:07.177000', '2016/08/30 오후 04:04:14.339000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:25:14.062000', '2016/08/30 오후 04:05:20.421000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:26:21.399000', '2016/08/30 오후 04:06:44.180000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:27:28.523000', '2016/08/30 오후 04:07:50.877000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:28:36.520000', '2016/08/30 오후 04:08:57.055000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:29:44.450000', '2016/08/30 오후 04:10:04.042000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:30:52.472000', '2016/08/30 오후 04:11:10.600000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:32:00.686000', '2016/08/30 오후 04:12:06.758000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:33:08.240000', '2016/08/30 오후 04:13:13.379000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:34:15.740000', '2016/08/30 오후 04:14:20.836000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:35:23.498000', '2016/08/30 오후 04:15:28.720000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:36:31.404000', '2016/08/30 오후 04:16:36.619000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:37:39.362000', '2016/08/30 오후 04:18:03.827000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:38:46.835000', '2016/08/30 오후 04:19:05.904000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:39:54.609000', '2016/08/30 오후 04:20:11.161000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:41:02.339000', '2016/08/30 오후 04:21:18.711000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:42:09.827000', '2016/08/30 오후 04:22:27.199000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:43:17.569000', '2016/08/30 오후 04:23:24.002000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:44:25.109000', '2016/08/30 오후 04:24:30.192000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:45:32.543000', '2016/08/30 오후 04:25:37.647000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:46:40.316000', '2016/08/30 오후 04:26:45.335000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:47:28.592000', '2016/08/30 오후 04:27:34.096000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:48:36.185000', '2016/08/30 오후 04:28:41.331000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:49:43.885000', '2016/08/30 오후 04:29:49.307000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:50:51.838000', '2016/08/30 오후 04:30:56.959000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:52:01.128000', '2016/08/30 오후 04:32:26.285000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:53:13.325000', '2016/08/30 오후 04:33:18.581000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:54:22.470000', '2016/08/30 오후 04:34:28.049000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:55:31.586000', '2016/08/30 오후 04:35:36.980000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:56:40.186000', '2016/08/30 오후 04:36:45.906000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:57:48.948000', '2016/08/30 오후 04:38:04.740000' FROM dual
UNION ALL SELECT '2016/08/30 오후 03:58:57.861000', '2016/08/30 오후 04:39:05.028000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:00:07.258000', '2016/08/30 오후 04:40:14.295000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:01:04.764000', '2016/08/30 오후 04:41:15.315000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:02:11.243000', '2016/08/30 오후 04:42:22.982000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:03:17.680000', '2016/08/30 오후 04:43:24.139000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:04:25.666000', '2016/08/30 오후 04:45:16.825000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:05:31.904000', '2016/08/30 오후 04:45:45.944000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:06:55.308000', '2016/08/30 오후 04:47:06.474000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:08:02.249000', '2016/08/30 오후 04:48:12.476000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:09:08.888000', '2016/08/30 오후 04:49:31.473000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:10:15.782000', '2016/08/30 오후 04:50:20.809000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:11:22.438000', '2016/08/30 오후 04:51:44.528000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:12:18.830000', '2016/08/30 오후 04:52:23.959000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:13:24.838000', '2016/08/30 오후 04:53:31.776000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:14:32.324000', '2016/08/30 오후 04:54:38.930000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:15:40.326000', '2016/08/30 오후 04:55:47.012000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:16:48.219000', '2016/08/30 오후 04:56:55.046000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:18:15.457000', '2016/08/30 오후 04:58:41.424000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:19:17.509000', '2016/08/30 오후 04:59:43.973000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:20:22.637000', '2016/08/30 오후 05:00:46.077000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:21:30.671000', '2016/08/30 오후 05:01:48.737000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:22:39.152000', '2016/08/30 오후 05:02:52.095000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:23:35.611000', '2016/08/30 오후 05:03:41.013000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:24:41.446000', '2016/08/30 오후 05:04:48.049000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:44:55.802000', '2016/08/30 오후 05:25:01.805000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:45:28.430000', '2016/08/30 오후 05:25:31.762000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:46:38.652000', '2016/08/30 오후 05:26:41.776000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:47:45.270000', '2016/08/30 오후 05:27:48.581000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:49:13.324000', '2016/08/30 오후 05:29:16.516000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:49:43.202000', '2016/08/30 오후 05:29:46.238000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:50:32.675000', '2016/08/30 오후 05:30:41.641000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:51:56.245000', '2016/08/30 오후 05:32:01.736000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:52:35.669000', '2016/08/30 오후 05:32:50.199000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:53:43.261000', '2016/08/30 오후 05:33:49.090000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:54:50.287000', '2016/08/30 오후 05:34:56.236000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:55:58.511000', '2016/08/30 오후 05:36:20.861000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:57:06.158000', '2016/08/30 오후 05:37:12.968000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:58:24.105000', '2016/08/30 오후 05:38:29.606000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:58:53.034000', '2016/08/30 오후 05:39:00.072000' FROM dual
UNION ALL SELECT '2016/08/30 오후 04:59:55.627000', '2016/08/30 오후 05:40:01.280000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:00:57.918000', '2016/08/30 오후 05:41:03.354000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:02:00.576000', '2016/08/30 오후 05:42:06.279000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:03:03.731000', '2016/08/30 오후 05:43:08.897000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:03:52.855000', '2016/08/30 오후 05:44:06.500000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:04:59.644000', '2016/08/30 오후 05:45:04.863000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:06:08.241000', '2016/08/30 오후 05:46:13.669000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:07:15.944000', '2016/08/30 오후 05:47:21.349000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:08:24.059000', '2016/08/30 오후 05:48:29.309000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:09:38.941000', '2016/08/30 오후 05:49:45.617000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:10:47.918000', '2016/08/30 오후 05:50:53.611000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:11:56.051000', '2016/08/30 오후 05:52:01.948000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:13:04.211000', '2016/08/30 오후 05:53:09.875000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:14:12.362000', '2016/08/30 오후 05:54:18.265000' FROM dual
UNION ALL SELECT '2016/08/30 오후 05:15:20.290000', '2016/08/30 오후 05:55:26.622000' FROM dual
)

, t1 AS
(
SELECT TO_TIMESTAMP(start_dtts, 'yyyy/mm/dd pm hh:mi:ss.ff') start_dtts
     , TO_TIMESTAMP(end_dtts  , 'yyyy/mm/dd pm hh:mi:ss.ff') end_dtts
  FROM t0
)
SELECT grp
     , MIN(start_dtts) start_dtts
     , MAX(end_dtts)   end_dtts
     , MAX(end_dtts) - MIN(start_dtts) work_time
  FROM (SELECT start_dtts, end_dtts
             , SUM(flag) OVER(ORDER BY start_dtts, end_dtts) grp
          FROM (SELECT start_dtts, end_dtts
                     , CASE WHEN
                       MAX(end_dtts) OVER(ORDER BY start_dtts, end_dtts
                       ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
                       >= start_dtts
                       THEN 0 ELSE 1 END flag
                  FROM t1
                )
        )
 GROUP BY grp
 ORDER BY grp
;

마농(manon94)님이 2016-09-07 07:48에 작성한 댓글입니다.
이 댓글은 2016-09-07 10:50에 마지막으로 수정되었습니다.

감사합니다.

제가 3일동안 헤매던 건데 이렇게 해결해 주시다니......

역시 능력자 이십니다. 다시 한번 감사합니다.

김용완(sonyon21)님이 2016-09-07 10:56에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
41234스토어 프로시져의 파라메터 정보는 어디서 확인가능한가여? [2]
최종길
2016-09-07
6425
41233oracle 프로시저 추가 질문 [2]
김용완
2016-09-07
6607
41232인덱스테이블스페이스 용량문의. [2]
김삼
2016-09-07
6562
41230oracel 프로시저 질문 [2]
김용완
2016-09-06
6645
41227로그성 데이터 쿼리 문의 드립니다. [5]
쿼리OTL
2016-08-29
7097
41226급합니다 도와주세요 마감과 당월까지의 합게 [1]
울산
2016-08-29
6871
41225간단한 날짜 쿼리 [2]
울산
2016-08-26
6922
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.020초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다