How to Create A Date From Preceding Date?

Analytics
Enthusiast

How to Create A Date From Preceding Date?

Dear Who Can Help,

I had a table look like this:
********************************
Cust_Id || Start_Date || End_Date
803 || 2007-04-29 || 2007-05-01
803 || 2007-05-02 || 2007-05-03
803 || 2007-05-04 || 2007-05-07
803 || 2007-05-08 || 2007-06-02
********************************

I need to create a new column using the preceding End_Date like this:
********************************
Cust_Id || Start_Date || End_Date ||New_Date
803 || 2007-04-29 || 2007-05-01 ||
803 || 2007-05-02 || 2007-05-03 || 2007-05-01
803 || 2007-05-04 || 2007-05-07 || 2007-05-03
803 || 2007-05-08 || 2007-06-02 || 2007-05-07
********************************
Please note that the New_Date was taking the previous End_Date.

How to use SQL to create a new column like above?

Many Thanks in Advance!
2 REPLIES
Senior Apprentice

Re: How to Create A Date From Preceding Date?

Hi Benjamin,
this is a task for OLAP:
MIN(end_date) OVER (PARTITION BY Cust_Id ORDER BY end_date
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)

Dieter
Enthusiast

Re: How to Create A Date From Preceding Date?

Dear dnoeth,
It work ok.

Thank you very much ^_^