sql - Equivalent to Lead and Lag in order to get the number of previous purchases -

i new teradata. have following table , need following result table:

result table continuation of 2 tables , have not mentioned remaining columns prodtype : rugby. there 5 more columns rugby understandable.

i able write following query generates current , previous revenues each prodtype. not sure how proceed write different time spans of last3months, last1year , lifetime.

my query is:

select orderno, ordertime,prodname, prodtype,  (case when prodtype = 'cricket' revenue else 0 end) cricket_currrev, min(cricket_currrev) on (order cricket_currrev rows between 1 preceding , 1 preceding) cricket_prevrev,  (case when prodtype = 'soccer' revenue else 0 end) soccer_currrev, min(soccer_currrev) on (order soccer_currrev rows between 1 preceding , 1 preceding) soccer_prevrev,  (case when prodtype = 'rugby' revenue else 0 end) rugby_currrev, min(rugby_currrev) on (order rugby_currrev rows between 1 preceding , 1 preceding) rugby_prevrev  given table userid='123' order ordertime; 

so in result table, need revenue every product type before particular order in different time spans. example, 3rd record cricball cricket. hence cricketprevrev 44.24 (first cricket product revenue) , able that. if see same record, field of cricket_last1year should have 44.24 because cricball ordered on 31oct2011, within year before product ordered, cricbat ordered i.e 12nov2010. revenue of cricbat should reflected in 3rd record. hope example makes clear. same should hold true every product. lifetime columns product should total revenue generated before particular product.

any appreciated.