Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Wed, 24 Feb 2016 @ 20:52:29 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Subtracting Timestamps SQL help
 
From:   Dieter Noeth

Johnpaul J wrote:

  have a requirement to calculate time spent on ticket each time it moved from Need-info state to any other state.  


OLAP-time again :)

As Teradata doesn't implement LAG you have to rewrite it using MIN:

     SELECT ....
         update_time
         - MIN(update_time) -- previous update_time
           OVER (PARTITION BY Ticket_no
                 ORDER BY update_time
                 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) DAY(4) TO SECOND FROM tab QUALIFY
         MIN(State) -- previous State
         OVER (PARTITION BY Ticket_no
               ORDER BY update_time
               ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) = 'need-info'
         AND State <> 'need-info'

Dieter



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 24 Jul 2020