Get Prior record in Teradata based on date

Database

Get Prior record in Teradata based on date

Hello Guru's

I am stuck with this scenario and need your expert advice in achieving this

here is my source table in teradata : I want current status codes based on last date 12/21/2012 and immediate prior date 12/11/2012

AppNo  STS_CD    LOAN_NUM                      DTE                                                   SEQ

3982       1              1138391175         12/9/2012 5:59:00 PM                                    1

3982       9              1138391175         12/9/2012 6:47:00 PM                                    2

3982       10           1138391175         12/9/2012 7:00:00 PM                                     3

3982       19           1138391175         12/9/2012 7:05:00 PM                                     4

3982       20           1138391175         12/9/2012 7:10:00 PM                                     5

3982       21           1138391175         12/10/2012 12:07:00 PM                                 6

3982       33           1138391175         12/10/2012 5:15:00 PM                                   7

3982       35           1138391175         12/11/2012 11:54:00 AM                         8

3982       41           1138391175         12/21/2012  01:00:00 PM                         9

3982       42           1138391175         12/21/2012 02:00 PM                               10

3982       60           1138391175         12/21/2012 4:00 PM                                 11

3982       100         1138391175         12/21/2012 11:43:00 PM                          12

My output should be

AppNo      STS_CD   PRV_STS_CD        LOAN_NUM                            Curr_ DTE                             PREV_DT

3982              41                 35           1138391175                        12/21/2012  01:00:00 PM        12/11/2012 11:54:00 AM

3982              42                  41        1138391175                         12/21/2012 02:00 PM             12/21/2012  01:00:00 PM

3982              60                  42         1138391175                         12/21/2012 4:00 PM               12/21/2012 02:00 PM                                     

3982              100                 60        1138391175                         12/21/2012 11:43:00 PM         12/21/2012 4:00 PM            

Thank you in advance

sachin                           

1 REPLY
Enthusiast

Re: Get Prior record in Teradata based on date

Assuming the value of SEQ column will be in order, you can use the below sql to get the result:

SEL CURR.AppNo,CURR.STS_CD,PREV.STS_CD,CURR.LOAN_NUM,CURR.DTE,PREV.DTE
FROM <TABLE> CURR
LEFT JOIN <TABLE> PREV
ON CURR.AppNo = PREV.AppNo
AND CURR.SEQ-1 = PREV.SEQ