Oracle SQL LAG function return the wrong calculation in Time

Question

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.stack.imgur.com/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

Could you please add the input data and not the image, Lag also have default value parameter which can be used in case of Null. — Atif, Aug 19 '20 at 06:37
Hi ismetguzelgun, the (Different_Time column) calculation in line 10 is NOT the correct data, it should be 15:42:44 - 10:28:18 = 05:14:26 ,right? Thanks — Yong, Aug 19 '20 at 06:52
In this case your second line must be wrong too right ? @Yong — ismetguzelgun, Aug 19 '20 at 07:14
Hi ismetguzelgun, hmm yes, but start from third line the data is correct back. — Yong, Aug 19 '20 at 07:55
But this is the real case. Between 2020-08-07 10:28:18 and 2020-08-05 15:42:44 there is a day and 18:45:34 time difference. — ismetguzelgun, Aug 19 '20 at 08:17

ismetguzelgun · Accepted Answer · 2020-08-19T09:06:08.333

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.

Solution

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'

Hi ismetguzelgun, only the second row return the correct value after row 3 untill row 11 is not the correct value. — Yong, Aug 19 '20 at 07:08
Hi ismetguzelgun, i already tried on this method, mean subract from second row time to first row time. Thank you for your solution. appreciate so much. — Yong, Aug 20 '20 at 00:44

score 1 · Answer 2 · answered Aug 19 '20 at 08:27

Your query is fine but you just need to format the handling of days, hours...

I have done the same for you:

Demo

select EQP_ID,LOT_ID,ACTIVITY,DATE_TIME, abs(extract (DAY from Different_Time))||' '||abs(extract (HOUR from Different_Time))||':'||abs(extract (MINUTE from Different_Time))||':'||abs(extract (SECOND from Different_Time)) AS Different_Time from (
select EQP_ID,LOT_ID,ACTIVITY,DATE_TIME,
 LAG(DATE_TIME,1) OVER(PARTITION BY EQP_ID ORDER BY DATE_TIME) - DATE_TIME AS Different_Time
from table1 where EQP_ID = '93K-P007');

Oracle SQL LAG function return the wrong calculation in Time

2 Answers2