My calculation at first row column Different_Time is incorrect data after second row is correct data until row 10 is incorrect data again then next row the calculation is back to correct calculation again with using LAG function.Appreciate if someone could help me on this problem. Thanks. [![This is sample of data][1]][1]
This is my query as below:
select EQP_ID,LOT_ID,ACTIVITY,DATE_TIME
TO_CHAR (DATE '1900-01-01' + ABS ( LAG(DATE_TIME,1)OVER(PARTITION BY EQP_ID ORDER BY DATE_TIME) - DATE_TIME), 'HH24:MI:SS')AS Different_Time
from STG_RMSTMP_CJ.ota_activity where EQP_ID = '93K-P007'
This is the output result: [1]: https://i.sstatic.net/mjhb7.png
EQP_ID LOT_ID ACTIVITY DATE_TIME Different_Time
93K-P007 GC00495 LOAD_LOT 2020-08-03 16:48:48 NULL
93K-P007 GC00495 LOAD_LOT 2020-08-05 11:51:44 19:02:56
93K-P007 GC00495 LOAD_LOT 2020-08-05 11:57:24 00:05:40
93K-P007 GC00495 LOAD_LOT 2020-08-05 15:14:21 03:16:57
93K-P007 WD72120 LOAD_LOT 2020-08-05 15:17:17 00:02:56
93K-P007 GC00495 LOAD_LOT 2020-08-05 15:28:43 00:11:26
93K-P007 GC00495 LOAD_LOT 2020-08-05 15:30:00 00:01:17
93K-P007 GC00495 LOAD_LOT 2020-08-05 15:42:44 00:12:44
93K-P007 GC00495 LOAD_LOT 2020-08-07 10:28:18 18:45:34
93K-P007 GC00495 LOAD_LOT 2020-08-07 10:33:31 00:05:13
Your first logic is fine. You should also consider day differences. But for the sole purpose of giving you the solution you want try below.
WITH MAIN
AS (SELECT '93K-P007' AS EQP_ID,
'GC00495' LOT_ID,
'LOAD_LOT' ACTIVITY,
TO_DATE ('2020-08-03 16:48:48', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_TIME
FROM DUAL
UNION ALL
SELECT '93K-P007' AS EQP_ID,
'GC00495' LOT_ID,
'LOAD_LOT' ACTIVITY,
TO_DATE ('2020-08-05 11:51:44', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_TIME
FROM DUAL
UNION ALL
SELECT '93K-P007' AS EQP_ID,
'GC00495' LOT_ID,
'LOAD_LOT' ACTIVITY,
TO_DATE ('2020-08-05 11:57:24', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_TIME
FROM DUAL
UNION ALL
SELECT '93K-P007' AS EQP_ID,
'GC00495' LOT_ID,
'LOAD_LOT' ACTIVITY,
TO_DATE ('2020-08-05 15:14:21', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_TIME
FROM DUAL
UNION ALL
SELECT '93K-P007' AS EQP_ID,
'WD72120' LOT_ID,
'LOAD_LOT' ACTIVITY,
TO_DATE ('2020-08-05 15:17:17', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_TIME
FROM DUAL
UNION ALL
SELECT '93K-P007' AS EQP_ID,
'GC00495' LOT_ID,
'LOAD_LOT' ACTIVITY,
TO_DATE ('2020-08-05 15:28:43', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_TIME
FROM DUAL
UNION ALL
SELECT '93K-P007' AS EQP_ID,
'GC00495' LOT_ID,
'LOAD_LOT' ACTIVITY,
TO_DATE ('2020-08-05 15:30:00', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_TIME
FROM DUAL
UNION ALL
SELECT '93K-P007' AS EQP_ID,
'GC00495' LOT_ID,
'LOAD_LOT' ACTIVITY,
TO_DATE ('2020-08-05 15:42:44', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_TIME
FROM DUAL
UNION ALL
SELECT '93K-P007' AS EQP_ID,
'GC00495' LOT_ID,
'LOAD_LOT' ACTIVITY,
TO_DATE ('2020-08-07 10:28:18', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_TIME
FROM DUAL
UNION ALL
SELECT '93K-P007' AS EQP_ID,
'GC00495' LOT_ID,
'LOAD_LOT' ACTIVITY,
TO_DATE ('2020-08-07 10:33:31', 'YYYY-MM-DD HH24:MI:SS')
AS DATE_TIME
FROM DUAL)
SELECT EQP_ID,
LOT_ID,
ACTIVITY,
DATE_TIME,
TO_CHAR(
LPAD(ABS(EXTRACT(HOUR FROM TO_TIMESTAMP(TO_CHAR(LAG (DATE_TIME, 1 ,DATE_TIME)OVER (PARTITION BY EQP_ID ORDER BY DATE_TIME),'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS')) -
EXTRACT(HOUR FROM TO_TIMESTAMP(TO_CHAR(DATE_TIME,'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS'))) ,2,'0')||':'||
LPAD(ABS(EXTRACT(MINUTE FROM TO_TIMESTAMP(TO_CHAR(LAG (DATE_TIME, 1 ,DATE_TIME)OVER (PARTITION BY EQP_ID ORDER BY DATE_TIME),'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS')) -
EXTRACT(MINUTE FROM TO_TIMESTAMP(TO_CHAR(DATE_TIME,'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS'))) ,2,'0')||':'||
LPAD(ABS(EXTRACT(SECOND FROM TO_TIMESTAMP(TO_CHAR(LAG (DATE_TIME, 1 ,DATE_TIME)OVER (PARTITION BY EQP_ID ORDER BY DATE_TIME),'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS')) -
EXTRACT(SECOND FROM TO_TIMESTAMP(TO_CHAR(DATE_TIME,'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS'))),2,'0')
) AS DIFF
FROM MAIN
WHERE EQP_ID = '93K-P007'