All Forums Analytics
teresacase 1 post Joined 02/13
16 Mar 2015
Help with lag/lead function in Teradata

Hi,
 
I have been trying to produce a lag function to determine latest activity date.  I have only 2 columns -
custid and activity_date
 
I have tried the following code:

SELECT DISTINCT custid
     , MAX(Activity_Date)
            OVER(PARTITION BY 1 ORDER BY Activity_Date
            ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS Lag_1
    FROM table_a

 

 

but it just gives me the following output:

 

custid	Activity_Date	Lag_
2250101763	20/11/2014	20/11/2014
2250101763	21/11/2014	21/11/2014
2250101763	22/11/2014	22/11/2014
2250104686	21/11/2014	21/11/2014
2250104686	22/11/2014	22/11/2014
2250118365	17/11/2014	17/11/2014
2250118365	19/11/2014	19/11/2014
2250118365	21/11/2014	21/11/2014
2250118365	22/11/2014	22/11/2014

 
when really I need the following output:

custid	Activity_Date	Lag_
2250101763	20/11/2014	21/11/2014
2250101763	21/11/2014	22/11/2014
2250101763	22/11/2014	
2250104686	21/11/2014	22/11/2014
2250104686	22/11/2014	
2250118365	17/11/2014	19/11/2014
2250118365	19/11/2014	21/11/2014
2250118365	21/11/2014	22/11/2014
2250118365	22/11/2014	

 
As I would eventually like to calculate the ddays difference between activity_date and Lag_
 

Something like this:

 

custid	Activity_Date	Lag_	days_diff
2250101763	20/11/2014	21/11/2014	1
2250101763	21/11/2014	22/11/2014	1
2250101763	22/11/2014	           -41965
2250104686	21/11/2014	22/11/2014	1
2250104686	22/11/2014	           -41965
2250118365	17/11/2014	19/11/2014	2
2250118365	19/11/2014	21/11/2014	2
2250118365	21/11/2014	22/11/2014	1
2250118365	22/11/2014	           -41965

 
Can anyone offer any advice?
 
Thanks in advance

Teresa

dnoeth 4628 posts Joined 11/04
16 Mar 2015

Hi Teresa,
you can't use a column number in the ORDER or PARTITION BY of an OLAP-function. 
Simply change it to ORDER BY Activity_date

Dieter

You must sign in to leave a comment.