Help with lag/lead function in Teradata

Analytics

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

2 REPLIES
Senior Apprentice

Re: Help with lag/lead function in Teradata

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

DW
Visitor

Re: Help with lag/lead function in Teradata

Lead and lag can be achived with the ordered analytics windowing features. Check this out:

create volatile table table_a(custid number,activity_date date)primary index (custid)on commit preserve rows;


insert into table_a (custid, activity_date) values(2250101763, '2014-11-20');
insert into table_a (custid, activity_date) values(2250101763, '2015-11-21');
insert into table_a (custid, activity_date) values(2250101763, '2014-11-22');
insert into table_a (custid, activity_date) values(2250104686, '2014-11-21');
insert into table_a (custid, activity_date) values(2250104686, '2014-11-22');
insert into table_a (custid, activity_date) values(2250118365, '2014-11-17');
insert into table_a (custid, activity_date) values(2250118365, '2014-11-19'); 
insert into table_a (custid, activity_date) values(2250118365, '2014-11-21'); 
insert into table_a (custid, activity_date) values(2250118365, '2014-11-22'); 

select * from table_a order by custid, activity_date;

select
custid,activity_date,first_value(activity_date) over(partition by custid order by custid, activity_date rows between 1 preceding and 0 following) lag_activity_date,last_value(activity_date) over(partition by custid order by custid, activity_date rows between 0 preceding and 1 following) lead_activity_date,activity_date - lag_activity_date as activity_diff
from table_a
order by custid, activity_date;

Results:

line_no custid activity_date lag_activity_date lead_activity_date activity_diff
1 2,250,101,763 11/20/2014 11/20/2014 11/22/2014 0
2 2,250,101,763 11/22/2014 11/20/2014 11/21/2015 2
3 2,250,101,763 11/21/2015 11/22/2014 11/21/2015 364
4 2,250,104,686 11/21/2014 11/21/2014 11/22/2014 0
5 2,250,104,686 11/22/2014 11/21/2014 11/22/2014 1
6 2,250,118,365 11/17/2014 11/17/2014 11/19/2014 0
7 2,250,118,365 11/19/2014 11/17/2014 11/21/2014 2
8 2,250,118,365 11/21/2014 11/19/2014 11/22/2014 2
9 2,250,118,365 11/22/2014 11/21/2014 11/22/2014 1

 

Regards,

Don