Tact Time 시간 중 점심시간, 저녁시간만큼을 제외하고 싶습니다.(초단위) 0 5 360

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

by chrome [2020.01.17 16:12:26]

1. LAG로 가져온 데이터가 제외 시간내에 있으면 종료시간을 변경(12시)

2. 원본 데이터가 제외 시간내에 있으면 시간 시간을 변경 (13시)

3. LAG으로 가져온 데이터가 제외 시간을 넘어가면 1 계산식에 

추가로  시작 시간을 13시 부터 LAG로 가져온 시간을 계산한 시간을 추가

4. 원본데이터가 제외 시간 외에 있으면 상관 없음

 

CASE WHEN을  써서 로직은 좀 길어지겠죠 


by ㅇㅇ준 [2020.01.17 16:46:03]

답변을 달기에는 설명이 불친절합니다.

올려주신 데이터만 보고, 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
);

 


by 빠바 [2020.01.17 16:56:24]

설명이 부족했던점 죄송합니다 ㅠ 하루 기준으로 구현중인게 맞아요. 두분 답변 참고해서 한번 수정해보겠습니다!


by 마농 [2020.01.17 16:59:04]
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
        )
;

 


by 마농 [2020.01.18 11:42:17]
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
        )
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입