Time Difference from previous row

Database
Enthusiast

Time Difference from previous row

SELECT    
 SYS_EVB
,PRIN_EVB   
,SUPPLIER_CDE_EVB
,D.DESCR_CTD
,EVENT_CHANNEL_EVB
,CASE       
    WHEN (POSITION('/' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))) = 2) 
    AND   POSITION('/' IN SUBSTRING(TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))
            
FROM     POSITION('/' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10))))+1 FOR 2)) =2
             THEN SUBSTR(TRIM(CAST(START_DTE_EVB AS VARCHAR(10))),
                5,4)||'-'|| '0'||SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
        1,1)||'-'||'0'||SUBSTR(TRIM(CAST(START_DTE_EVB AS VARCHAR(10))),
                3,1)
            
    WHEN (POSITION('/' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))) = 2)  
    AND  POSITION('/' IN SUBSTRING(TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) 
                     
FROM       POSITION('/' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10))))+1 FOR 3)) =3
                      THEN   SUBSTR(TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
                6,4)||'-'||'0'||SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
        1,1)||'-'||SUBSTR(TRIM(CAST(START_DTE_EVB AS VARCHAR(10))),
                3,2)
                    
    WHEN (POSITION('/' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))) = 3) 
    AND    POSITION('/' IN SUBSTRING(TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))
                     
FROM       POSITION('/' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10))))+1 FOR 2)) =2
                      THEN   SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
                6,4)||'-'||SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
        1,2)||'-'|| '0'||SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
                4,1)
                    
    WHEN  (POSITION('/' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))) = 3)  
    AND  POSITION('/' IN SUBSTRING(TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))
                     
FROM       POSITION('/' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10))))+1 FOR 3)) =3
                      THEN  SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
                7,4)||'-'|| SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
        1,2)||'-'|| SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
                4,2)       
                    
    WHEN   (POSITION('-' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))) = 5) 
    AND   POSITION('-' IN SUBSTRING(TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))
            
FROM     POSITION('-' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10))))+1 FOR 3)) =3
             THEN SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
                1,4)||'-'|| SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
        6,2)||'-'|| SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
                9,2)
                    
    WHEN   (POSITION('-' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))) = 5) 
    AND   POSITION('-' IN SUBSTRING(TRIM(CAST(START_DTE_EVB AS VARCHAR(10)))
            
FROM     POSITION('-' IN TRIM(CAST(START_DTE_EVB AS VARCHAR(10))))+1 FOR 2)) =2  
             THEN  SUBSTR(TRIM(CAST(START_DTE_EVB AS VARCHAR(10))),
                1,4)||'-'|| '0'||SUBSTR(TRIM(CAST(START_DTE_EVB AS VARCHAR(10))),
        6,1)||'-'||SUBSTR( TRIM(CAST(START_DTE_EVB AS VARCHAR(10))) ,
                8,2) 
ELSE          CAST(START_DTE_EVB AS VARCHAR(10))
END        ||' '||oreplace(START_TME_EVB,'.',':') as START_DTE_TME_EVB

,CASE 
WHEN (POSITION('/' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))) = 2)
AND POSITION('/' IN SUBSTRING(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))

FROM POSITION('/' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))))+1 FOR 2)) =2
THEN SUBSTR(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))),
5,4)||'-'|| '0'||SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
1,1)||'-'||'0'||SUBSTR(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))),
3,1)

WHEN (POSITION('/' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))) = 2)
AND POSITION('/' IN SUBSTRING(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))

FROM  POSITION('/' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))))+1 FOR 3)) =3
THEN SUBSTR(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
6,4)||'-'||'0'||SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
1,1)||'-'||SUBSTR(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))),
3,2)

WHEN (POSITION('/' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))) = 3)
AND POSITION('/' IN SUBSTRING(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))

FROM  POSITION('/' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))))+1 FOR 2)) =2
THEN SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
6,4)||'-'||SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
1,2)||'-'|| '0'||SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
4,1)

WHEN (POSITION('/' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))) = 3)
AND POSITION('/' IN SUBSTRING(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))

FROM  POSITION('/' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))))+1 FOR 3)) =3
THEN SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
7,4)||'-'|| SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
1,2)||'-'|| SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
4,2)

WHEN (POSITION('-' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))) = 5)
AND POSITION('-' IN SUBSTRING(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))

FROM POSITION('-' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))))+1 FOR 3)) =3
THEN SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
1,4)||'-'|| SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
6,2)||'-'|| SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
9,2)

WHEN (POSITION('-' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))) = 5)
AND POSITION('-' IN SUBSTRING(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10)))

FROM POSITION('-' IN TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))))+1 FOR 2)) =2
THEN SUBSTR(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))),
1,4)||'-'|| '0'||SUBSTR(TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))),
6,1)||'-'||SUBSTR( TRIM(CAST(EVENT_END_DTE_EVB AS VARCHAR(10))) ,
8,2)
ELSE  CAST(EVENT_END_DTE_EVB AS VARCHAR(10))
END ||' '||oreplace(EVENT_END_TME_EVB,'.',':') as EVENT_END_TME_EVB

,START_DTE_TME_EVB   -  MIN ( EVENT_END_TME_EVB) OVER (PARTITION BY EVENT_ID_EVB  ORDER BY START_DTE_TME_EVB ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) DAY (4) TO SECOND

,EVENT_ID_EVB
,ONLINE_DESC_EVB
,FST_ORD_AMT_EVB
,LS_CHG_DTE_EVB
FROM NDW_JRNL_VIEWS.EVB_BASE E
INNER JOIN  NDW_JRNL_VIEWS.CTD_DISPLAY D ON
E.SYS_EVB  = D.SYS_CTD AND E. PRIN_EVB   = D.PRIN_CTD AND  D.CDE_TBL_NO_CTD = 'ES'  AND  SUPPLIER_CDE_EVB = D.CDE_VALUE_CTD
WHERE 
SYS_EVB IN ('8495')--('8220', '8396','8771','8529','8495')
AND PRIN_EVB = '6000'
--AND PRIN_EVB NOT IN ('4400','8400')
AND START_DTE_EVB >= CURRENT_DATE
AND SUPPLIER_CDE_EVB = 'CCA'

ORDER BY
SYS_EVB , PRIN_EVB , SUPPLIER_CDE_EVB , START_DTE_EVB , START_TME_EVB

I keep getting Select Failed [5407] Invalid operation for DateTime or Inverval Can someone tell me why?

5 REPLIES
Senior Apprentice

Re: Time Difference from previous row

Both START_DTE_TME_EVB and EVENT_END_TME_EVB seem to be no timestamps, what's the actual datatypes?

And your calculation looks way too complicated, can you show sone example data? 

Enthusiast

Re: Time Difference from previous row

Hi Dieter - Thank you for helping! In our database the date and time columns are serperate columns; The date column is data type [DATE] and the time columns is data type [VARCHAR(8)] so the value appears like this 08.00 instead of this 8:00. So the complicated part of my script is actually combining the start date/start time and end date/end time to create timestamps. 
Enthusiast

Re: Time Difference from previous row

Hi Dieter Thank you for helping! In our database the date and time columns seperate columns; The date is data type [DATE] and the time column is data type [VARCHAR (8)] so the time value appears like this 08.00 instead of like this 8:00 so the complicated part of my script is actually combing the start date/start time and end date/end time to create timestamps. What I want in my end result is to calculate the time difference for end time in row one to the start time in row two. (Hope that makes sense) 

Senior Apprentice

Re: Time Difference from previous row

When START_DTE_EVB is a DATE casting to a string with a specific format is simple, either using Teradata's FORMAT or TO_CHAR:

TO_CHAR(START_DTE_EVB,'yyyy-mm-dd')

But then you got a string and you need to cast it back to a Timestamp to be able to subtract.

So this should work to get a Timestamp with casting to/from string:

CAST(START_DTE_EVB AS TIMESTAMP)
+ CAST(OREPLACE(START_TME_EVB,'.',':') AS INTERVAL HOUR TO MINUTE) AS START_DTE_TME_EVB

,CAST(EVENT_END_DTE_EVB AS TIMESTAMP)
+ CAST(OREPLACE(EVENT_END_TME_EVB,'.',':') AS INTERVAL HOUR TO MINUTE) AS END_DTE_TME_EVB -- new alias

There's another problem in your query, you alias the result of the calculation to EVENT_END_TME_EVB again and when you try to use it within the OLAP-function it's accessing the original column.

There's a basic rule of thumb:

Never alias to an existing column name when you want to use it anywhere else in the query.

,START_DTE_TME_EVB -  
MIN(END_DTE_TME_EVB) -- new alias
OVER (PARTITION BY EVENT_ID_EVB
ORDER BY START_DTE_TME_EVB
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) DAY (4) TO SECOND
Enthusiast

Re: Time Difference from previous row

I will try making the changes you listed to see if it works, thank you for your help!!!