0

Can someone suggest on SQL for the below Input , I am looking to generate Current Quarter and Prior Quarter amount in a row

Input 

Fiscper Quarter_Amount  
2017Q1  7$

2017Q2  8$

2017Q3  5$

2017Q4  3$

2018Q1  10$

Output 

Output  Current Quarter Amount  Prior Quarter Amount    
2017Q1         7$   

2017Q2         8$                7$

2017Q3         5$                8$

2017Q4         3$                5$

2018Q1         10$               3$
Sociopath
  • 13,068
  • 19
  • 47
  • 75
Shrilekha V
  • 1

1 Answers1

0

You want lag() :

select t.*, lag(Quarter_Amount) over (order by Fiscper) as Prior_Quarter_Amount  
from table t;

However, you can also use subquery :

select t.*, (select t1.Quarter_Amount
             from table t1
             where t1.Fiscper < t.Fiscper
             order by t1.Fiscper desc 
             limit 1
            ) as Prior_Quarter_Amount
from table t;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • LAG is introduced in Teradata 16.x. Prior versions have to use windowed aggregate functions explained here: https://stackoverflow.com/questions/8124756/teradata-equivalent-for-lead-and-lag-function-of-oracle – Rob Paller Sep 12 '18 at 14:58