데이터베이스 질문과 답변
by 빠바 [2020.01.17 11:15:35]
안녕하세요. Tact Time 계산 중.. 점심시간 이전 작업완료 제품과 점심시간 이후 작업완료 제품의 시간차이를 구하고 싶어서 문의 드립니다.
두 작업간의 차이는 LAG 함수를 이용하여 구하였습니다.
먼저 쿼리와 결과 보여드립니다. 결과값에서 빨간색 표시한 부분이 질문드린 부분입니다.
SELECT LH.LOTID ,
LH.TRACKOUTTIME ,
LAG(LH.TRACKOUTTIME) OVER (ORDER BY LH.TIMEKEY ASC) AS PREVTRACKOUTTIME ,
(LH.TRACKOUTTIME - LAG(LH.TRACKOUTTIME) OVER (ORDER BY LH.TIMEKEY ASC)) * (24*60*60) AS USETIME
FROM
PRODUCTIONINFODEF PI
INNER JOIN
PRODUCTDEF PD
ON PD.PRODUCTID = PI.PRODUCTID
LEFT JOIN
LOTHISTORY LH
ON LH.PRODUCTID = PI.PRODUCTID
AND LH.EVENT = 'TrackOut'
AND LH.LASTPROCESSOPERATIONID = 'TD5400' /**P*/
AND LH.EVENTTIME >= TO_DATE( '2020-01-16' /**P*/ || '00:00:00',
'YYYY-MM-DD HH24:MI:SS')
AND LH.EVENTTIME <= TO_DATE( '2020-01-16' /**P*/ || '23:59:59',
'YYYY-MM-DD HH24:MI:SS')
WHERE
PI.LINEID = 'B' /**P*/
AND PI.CURRENTDATE = TO_DATE( '2020-01-16' /**P*/, 'YYYY-MM-DD')
ORDER BY
PI.STARTTIME,
LH.TIMEKEY ASC;
LOTID TrackOut PrevTrackOut WorkTime(s)
TD2020011600177 2020/01/16 오후 1:08:53
TD2020011600176 2020/01/16 오후 1:11:51 2020/01/16 오후 1:08:53 178
TD2020011600178 2020/01/16 오후 1:14:39 2020/01/16 오후 1:11:51 168
TD2020011600175 2020/01/16 오후 1:18:30 2020/01/16 오후 1:14:39 231
TD2020011600197 2020/01/16 오후 1:23:49 2020/01/16 오후 1:18:30 319
TD2020011600217 2020/01/16 오후 1:29:05 2020/01/16 오후 1:23:49 316
TD2020011600199 2020/01/16 오후 2:00:52 2020/01/16 오후 1:29:05 1907
TD2020011600223 2020/01/16 오후 2:07:53 2020/01/16 오후 2:00:52 421
TD2020011600244 2020/01/16 오후 2:10:55 2020/01/16 오후 2:07:53 182
TD2020011600233 2020/01/16 오후 2:14:30 2020/01/16 오후 2:10:55 215
TD2020011600222 2020/01/16 오후 2:18:33 2020/01/16 오후 2:14:30 243
TD2020011600245 2020/01/16 오후 2:22:16 2020/01/16 오후 2:18:33 223
TD2020011600247 2020/01/16 오후 2:29:35 2020/01/16 오후 2:22:16 439
TD2020011600246 2020/01/16 오후 2:33:53 2020/01/16 오후 2:29:35 258
TD2020011600237 2020/01/16 오후 2:37:01 2020/01/16 오후 2:33:53 188
TD2020011600238 2020/01/16 오후 2:39:51 2020/01/16 오후 2:37:01 170
TD2020011600248 2020/01/16 오후 2:42:43 2020/01/16 오후 2:39:51 172
TD2020011600258 2020/01/16 오후 2:53:40 2020/01/16 오후 2:42:43 657
TD2020011600276 2020/01/16 오후 3:12:47 2020/01/16 오후 2:53:40 1147
TD2020011600275 2020/01/16 오후 3:15:15 2020/01/16 오후 3:12:47 148
TD2020011600290 2020/01/16 오후 3:17:23 2020/01/16 오후 3:15:15 128
TD2020011600289 2020/01/16 오후 3:19:55 2020/01/16 오후 3:17:23 152
TD2020011600291 2020/01/16 오후 3:22:38 2020/01/16 오후 3:19:55 163
TD2020011600292 2020/01/16 오후 3:24:40 2020/01/16 오후 3:22:38 122
TD2020011600309 2020/01/16 오후 3:31:48 2020/01/16 오후 3:24:40 428
TD2020011600308 2020/01/16 오후 3:34:25 2020/01/16 오후 3:31:48 157
TD2020011600303 2020/01/16 오후 3:37:18 2020/01/16 오후 3:34:25 173
TD2020011600302 2020/01/16 오후 3:41:33 2020/01/16 오후 3:37:18 255
TD2020011600314 2020/01/16 오후 3:51:01 2020/01/16 오후 3:41:33 568
TD2020011600313 2020/01/16 오후 3:53:31 2020/01/16 오후 3:51:01 150
TD2020011600327 2020/01/16 오후 3:59:26 2020/01/16 오후 3:53:31 355
TD2020011600326 2020/01/16 오후 4:03:31 2020/01/16 오후 3:59:26 245
TD2020011600334 2020/01/16 오후 4:05:57 2020/01/16 오후 4:03:31 146
TD2020011600333 2020/01/16 오후 4:08:22 2020/01/16 오후 4:05:57 145
TD2020011600340 2020/01/16 오후 4:10:17 2020/01/16 오후 4:08:22 115
TD2020011600339 2020/01/16 오후 4:12:20 2020/01/16 오후 4:10:17 123
TD2020011600342 2020/01/16 오후 4:14:29 2020/01/16 오후 4:12:20 129
TD2020011600341 2020/01/16 오후 4:17:07 2020/01/16 오후 4:14:29 158
TD2020011600344 2020/01/16 오후 4:22:38 2020/01/16 오후 4:17:07 331
TD2020011600343 2020/01/16 오후 4:24:44 2020/01/16 오후 4:22:38 126
TD2020011600346 2020/01/16 오후 4:26:49 2020/01/16 오후 4:24:44 125
TD2020011600345 2020/01/16 오후 4:29:00 2020/01/16 오후 4:26:49 131
TD2020011600347 2020/01/16 오후 4:31:44 2020/01/16 오후 4:29:00 164
TD2020011600350 2020/01/16 오후 4:41:22 2020/01/16 오후 4:31:44 578
TD2020011600352 2020/01/16 오후 4:45:32 2020/01/16 오후 4:41:22 250
TD2020011600351 2020/01/16 오후 4:48:25 2020/01/16 오후 4:45:32 173
TD2020011600354 2020/01/16 오후 4:51:27 2020/01/16 오후 4:48:25 182
TD2020011600353 2020/01/16 오후 4:53:34 2020/01/16 오후 4:51:27 127
TD2020011600356 2020/01/16 오후 4:55:28 2020/01/16 오후 4:53:34 114
TD2020011600355 2020/01/16 오후 5:33:28 2020/01/16 오후 4:55:28 2280
TD2020011600348 2020/01/16 오후 7:01:12 2020/01/16 오후 5:33:28 5264 이 부분에서 저녁시간을 빼고 싶습니다. 점심시간도 동일... 17:00부터 18:00 까지 점심시간이며 이시점에서 3600초를 빼고 5264-3600 = 1664 의 값이 나왔으면 합니다.
TD2020011600349 2020/01/16 오후 7:04:17 2020/01/16 오후 7:01:12 185
TD2020011600358 2020/01/16 오후 7:07:20 2020/01/16 오후 7:04:17 183
TD2020011600357 2020/01/16 오후 7:20:32 2020/01/16 오후 7:07:20 792
TD2020011600360 2020/01/16 오후 7:23:25 2020/01/16 오후 7:20:32 173
TD2020011600359 2020/01/16 오후 7:26:01 2020/01/16 오후 7:23:25 156
TD2020011600362 2020/01/16 오후 7:43:31 2020/01/16 오후 7:26:01 1050
TD2020011600361 2020/01/16 오후 7:45:52 2020/01/16 오후 7:43:31 141
TD2020011600364 2020/01/16 오후 7:47:45 2020/01/16 오후 7:45:52 113
TD2020011600363 2020/01/16 오후 7:49:48 2020/01/16 오후 7:47:45 123
TD2020011600369 2020/01/16 오후 7:51:39 2020/01/16 오후 7:49:48 111
TD2020011600368 2020/01/16 오후 7:53:39 2020/01/16 오후 7:51:39 120
TD2020011600371 2020/01/16 오후 7:55:29 2020/01/16 오후 7:53:39 110
TD2020011600198 2020/01/16 오후 7:57:42 2020/01/16 오후 7:55:29 133
1. LAG로 가져온 데이터가 제외 시간내에 있으면 종료시간을 변경(12시)
2. 원본 데이터가 제외 시간내에 있으면 시간 시간을 변경 (13시)
3. LAG으로 가져온 데이터가 제외 시간을 넘어가면 1 계산식에
추가로 시작 시간을 13시 부터 LAG로 가져온 시간을 계산한 시간을 추가
4. 원본데이터가 제외 시간 외에 있으면 상관 없음
CASE WHEN을 써서 로직은 좀 길어지겠죠
답변을 달기에는 설명이 불친절합니다.
올려주신 데이터만 보고, TrackOut와 PrevTrackOut 가 무조건 같은 날짜라는 조건하에
의식의 흐름대로 짠 쿼리로 참고만 해주세용~
WITH A AS(
SELECT 'TD2020011600177' COL1,'20200116130853' COL2,'' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600176' COL1,'20200116131151' COL2,'20200116130853' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600178' COL1,'20200116131439' COL2,'20200116131151' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600175' COL1,'20200116131830' COL2,'20200116131439' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600197' COL1,'20200116132349' COL2,'20200116131830' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600217' COL1,'20200116132905' COL2,'20200116132349' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600199' COL1,'20200116140052' COL2,'20200116132905' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600223' COL1,'20200116140753' COL2,'20200116140052' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600244' COL1,'20200116141055' COL2,'20200116140753' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600233' COL1,'20200116141430' COL2,'20200116141055' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600222' COL1,'20200116141833' COL2,'20200116141430' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600245' COL1,'20200116142216' COL2,'20200116141833' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600247' COL1,'20200116142935' COL2,'20200116142216' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600246' COL1,'20200116143353' COL2,'20200116142935' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600237' COL1,'20200116143701' COL2,'20200116143353' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600238' COL1,'20200116143951' COL2,'20200116143701' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600248' COL1,'20200116144243' COL2,'20200116143951' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600258' COL1,'20200116145340' COL2,'20200116144243' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600276' COL1,'20200116151247' COL2,'20200116145340' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600275' COL1,'20200116151515' COL2,'20200116151247' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600290' COL1,'20200116151723' COL2,'20200116151515' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600289' COL1,'20200116151955' COL2,'20200116151723' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600291' COL1,'20200116152238' COL2,'20200116151955' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600292' COL1,'20200116152440' COL2,'20200116152238' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600309' COL1,'20200116153148' COL2,'20200116152440' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600308' COL1,'20200116153425' COL2,'20200116153148' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600303' COL1,'20200116153718' COL2,'20200116153425' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600302' COL1,'20200116154133' COL2,'20200116153718' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600314' COL1,'20200116155101' COL2,'20200116154133' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600313' COL1,'20200116155331' COL2,'20200116155101' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600327' COL1,'20200116155926' COL2,'20200116155331' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600326' COL1,'20200116160331' COL2,'20200116155926' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600334' COL1,'20200116160557' COL2,'20200116160331' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600333' COL1,'20200116160822' COL2,'20200116160557' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600340' COL1,'20200116161017' COL2,'20200116160822' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600339' COL1,'20200116161220' COL2,'20200116161017' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600342' COL1,'20200116161429' COL2,'20200116161220' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600341' COL1,'20200116161707' COL2,'20200116161429' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600344' COL1,'20200116162238' COL2,'20200116161707' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600343' COL1,'20200116162444' COL2,'20200116162238' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600346' COL1,'20200116162649' COL2,'20200116162444' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600345' COL1,'20200116162900' COL2,'20200116162649' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600347' COL1,'20200116163144' COL2,'20200116162900' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600350' COL1,'20200116164122' COL2,'20200116163144' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600352' COL1,'20200116164532' COL2,'20200116164122' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600351' COL1,'20200116164825' COL2,'20200116164532' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600354' COL1,'20200116165127' COL2,'20200116164825' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600353' COL1,'20200116165334' COL2,'20200116165127' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600356' COL1,'20200116165528' COL2,'20200116165334' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600355' COL1,'20200116173328' COL2,'20200116165528' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600348' COL1,'20200116190112' COL2,'20200116173328' COL3,'XX' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600349' COL1,'20200116190417' COL2,'20200116190112' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600358' COL1,'20200116190720' COL2,'20200116190417' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600357' COL1,'20200116192032' COL2,'20200116190720' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600360' COL1,'20200116192325' COL2,'20200116192032' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600359' COL1,'20200116192601' COL2,'20200116192325' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600362' COL1,'20200116194331' COL2,'20200116192601' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600361' COL1,'20200116194552' COL2,'20200116194331' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600364' COL1,'20200116194745' COL2,'20200116194552' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600363' COL1,'20200116194948' COL2,'20200116194745' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600369' COL1,'20200116195139' COL2,'20200116194948' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600368' COL1,'20200116195339' COL2,'20200116195139' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600371' COL1,'20200116195529' COL2,'20200116195339' COL3,'' COL4 FROM DUAL UNION ALL
SELECT 'TD2020011600198' COL1,'20200116215000' COL2,'20200116115000' COL3,'' COL4 FROM DUAL
)
SELECT COL1
, COL2
, COL3
, ROUND(((TO_DATE(COL2,'YYYYMMDDHH24MISS')-TO_DATE(NVL(COL3,COL2),'YYYYMMDDHH24MISS'))
- CASE WHEN (COL7 BETWEEN COL3 AND COL2 OR COL6 BETWEEN COL3 AND COL2)
THEN ((CASE WHEN TO_DATE(COL2,'YYYYMMDDHH24MISS') >= TO_DATE(COL7,'YYYYMMDDHH24MISS')
THEN TO_DATE(COL7,'YYYYMMDDHH24MISS')
ELSE TO_DATE(COL2,'YYYYMMDDHH24MISS')
END)
-(CASE WHEN TO_DATE(NVL(COL3,COL2),'YYYYMMDDHH24MISS') >= TO_DATE(COL6,'YYYYMMDDHH24MISS')
THEN TO_DATE(NVL(COL3,COL2),'YYYYMMDDHH24MISS')
ELSE TO_DATE(COL6,'YYYYMMDDHH24MISS')
END))
ELSE 0
END
-CASE WHEN (COL5 BETWEEN COL3 AND COL2 OR COL4 BETWEEN COL3 AND COL2)
THEN ((CASE WHEN TO_DATE(COL2,'YYYYMMDDHH24MISS') >= TO_DATE(COL5,'YYYYMMDDHH24MISS')
THEN TO_DATE(COL5,'YYYYMMDDHH24MISS')
ELSE TO_DATE(COL2,'YYYYMMDDHH24MISS')
END)
-(CASE WHEN TO_DATE(NVL(COL3,COL2),'YYYYMMDDHH24MISS') >= TO_DATE(COL4,'YYYYMMDDHH24MISS')
THEN TO_DATE(NVL(COL3,COL2),'YYYYMMDDHH24MISS')
ELSE TO_DATE(COL4,'YYYYMMDDHH24MISS')
END))
ELSE 0
END
)*24*60*60)
FROM (
SELECT COL1
, COL2
, COL3
, SUBSTR(COL2,1,8)||'120000' COL4--점심 식사 시간 시작
, SUBSTR(COL2,1,8)||'130000' COL5--점심 식사 시간 종료
, SUBSTR(COL2,1,8)||'180000' COL6--저녁 식사 시간 시작
, SUBSTR(COL2,1,8)||'190000' COL7--저녁 식사 시간 종료
FROM A
ORDER BY 2 ASC
);
설명이 부족했던점 죄송합니다 ㅠ 하루 기준으로 구현중인게 맞아요. 두분 답변 참고해서 한번 수정해보겠습니다!
WITH t AS
(
SELECT Lotid
, TO_DATE(ttt, 'yyyymmddhh24miss') trackouttime
FROM (
SELECT 'TD2020011600177' Lotid, '20200116130853' ttt FROM dual
UNION ALL SELECT 'TD2020011600176', '20200116131151' FROM dual
UNION ALL SELECT 'TD2020011600178', '20200116131439' FROM dual
UNION ALL SELECT 'TD2020011600175', '20200116131830' FROM dual
UNION ALL SELECT 'TD2020011600197', '20200116132349' FROM dual
UNION ALL SELECT 'TD2020011600217', '20200116132905' FROM dual
UNION ALL SELECT 'TD2020011600199', '20200116140052' FROM dual
UNION ALL SELECT 'TD2020011600223', '20200116140753' FROM dual
UNION ALL SELECT 'TD2020011600244', '20200116141055' FROM dual
UNION ALL SELECT 'TD2020011600233', '20200116141430' FROM dual
UNION ALL SELECT 'TD2020011600222', '20200116141833' FROM dual
UNION ALL SELECT 'TD2020011600245', '20200116142216' FROM dual
UNION ALL SELECT 'TD2020011600247', '20200116142935' FROM dual
UNION ALL SELECT 'TD2020011600246', '20200116143353' FROM dual
UNION ALL SELECT 'TD2020011600237', '20200116143701' FROM dual
UNION ALL SELECT 'TD2020011600238', '20200116143951' FROM dual
UNION ALL SELECT 'TD2020011600248', '20200116144243' FROM dual
UNION ALL SELECT 'TD2020011600258', '20200116145340' FROM dual
UNION ALL SELECT 'TD2020011600276', '20200116151247' FROM dual
UNION ALL SELECT 'TD2020011600275', '20200116151515' FROM dual
UNION ALL SELECT 'TD2020011600290', '20200116151723' FROM dual
UNION ALL SELECT 'TD2020011600289', '20200116151955' FROM dual
UNION ALL SELECT 'TD2020011600291', '20200116152238' FROM dual
UNION ALL SELECT 'TD2020011600292', '20200116152440' FROM dual
UNION ALL SELECT 'TD2020011600309', '20200116153148' FROM dual
UNION ALL SELECT 'TD2020011600308', '20200116153425' FROM dual
UNION ALL SELECT 'TD2020011600303', '20200116153718' FROM dual
UNION ALL SELECT 'TD2020011600302', '20200116154133' FROM dual
UNION ALL SELECT 'TD2020011600314', '20200116155101' FROM dual
UNION ALL SELECT 'TD2020011600313', '20200116155331' FROM dual
UNION ALL SELECT 'TD2020011600327', '20200116155926' FROM dual
UNION ALL SELECT 'TD2020011600326', '20200116160331' FROM dual
UNION ALL SELECT 'TD2020011600334', '20200116160557' FROM dual
UNION ALL SELECT 'TD2020011600333', '20200116160822' FROM dual
UNION ALL SELECT 'TD2020011600340', '20200116161017' FROM dual
UNION ALL SELECT 'TD2020011600339', '20200116161220' FROM dual
UNION ALL SELECT 'TD2020011600342', '20200116161429' FROM dual
UNION ALL SELECT 'TD2020011600341', '20200116161707' FROM dual
UNION ALL SELECT 'TD2020011600344', '20200116162238' FROM dual
UNION ALL SELECT 'TD2020011600343', '20200116162444' FROM dual
UNION ALL SELECT 'TD2020011600346', '20200116162649' FROM dual
UNION ALL SELECT 'TD2020011600345', '20200116162900' FROM dual
UNION ALL SELECT 'TD2020011600347', '20200116163144' FROM dual
UNION ALL SELECT 'TD2020011600350', '20200116164122' FROM dual
UNION ALL SELECT 'TD2020011600352', '20200116164532' FROM dual
UNION ALL SELECT 'TD2020011600351', '20200116164825' FROM dual
UNION ALL SELECT 'TD2020011600354', '20200116165127' FROM dual
UNION ALL SELECT 'TD2020011600353', '20200116165334' FROM dual
UNION ALL SELECT 'TD2020011600356', '20200116165528' FROM dual
UNION ALL SELECT 'TD2020011600355', '20200116173328' FROM dual
UNION ALL SELECT 'TD2020011600348', '20200116190112' FROM dual --
UNION ALL SELECT 'TD2020011600349', '20200116190417' FROM dual
UNION ALL SELECT 'TD2020011600358', '20200116190720' FROM dual
UNION ALL SELECT 'TD2020011600357', '20200116192032' FROM dual
UNION ALL SELECT 'TD2020011600360', '20200116192325' FROM dual
UNION ALL SELECT 'TD2020011600359', '20200116192601' FROM dual
UNION ALL SELECT 'TD2020011600362', '20200116194331' FROM dual
UNION ALL SELECT 'TD2020011600361', '20200116194552' FROM dual
UNION ALL SELECT 'TD2020011600364', '20200116194745' FROM dual
UNION ALL SELECT 'TD2020011600363', '20200116194948' FROM dual
UNION ALL SELECT 'TD2020011600369', '20200116195139' FROM dual
UNION ALL SELECT 'TD2020011600368', '20200116195339' FROM dual
UNION ALL SELECT 'TD2020011600371', '20200116195529' FROM dual
UNION ALL SELECT 'TD2020011600198', '20200116195742' FROM dual
))
SELECT Lotid
, trackouttime
, s prevtrackouttime
, ROUND( -- 초 환산 오차보정
( (e - s) -- 전체시간
- NVL(CASE WHEN TO_CHAR(s, 'hh24miss') < '130000'
AND TO_CHAR(e, 'hh24miss') > '120000' THEN LEAST (e, TRUNC(e) + 13/24)
- GREATEST(s, TRUNC(s) + 12/24)
WHEN TO_CHAR(s, 'hh24miss') < '190000'
AND TO_CHAR(e, 'hh24miss') > '180000' THEN LEAST (e, TRUNC(e) + 19/24)
- GREATEST(s, TRUNC(s) + 18/24)
ELSE 0 END, 0) -- 제외시간
) *24*60*60 -- 초 환산
) WorkTime
FROM (SELECT Lotid
, trackouttime
, trackouttime e
, LAG(trackouttime) OVER(ORDER BY trackouttime) s
FROM t
)
;
SELECT Lotid
, trackouttime
, s prevtrackouttime
, ROUND( -- 초 환산 오차보정
( (e - s) -- 전체시간
- GREATEST(0, LEAST(e, TRUNC(e) + 13/24) - GREATEST(s, TRUNC(s) + 12/24)) -- 점심제외
- GREATEST(0, LEAST(e, TRUNC(e) + 19/24) - GREATEST(s, TRUNC(s) + 18/24)) -- 저녁제외
) *24*60*60 -- 초 환산
) WorkTime
FROM (SELECT Lotid
, trackouttime
, trackouttime e
, LAG(trackouttime) OVER(ORDER BY trackouttime) s
FROM t
)
;
) 버튼을 클릭하여 작성 하시면 됩니다.