All Forums General
ingaljo 1 post Joined 09/11
28 Mar 2016
Create Expiration Date based on next Date Value

I am looking to build and Effective Date and Expiration Date utilzing a customers next purchase date in the table without a do loop if possible as I do not have access to buid stored procedures.
Below is an example of current data:
ID          InvoiceDate
ABC123  4/16/2015
ABC123  5/20/2015
ABC123  6/30/2015
 
Below is desired result:
ID           InvoiceDate       ExpirationDate
ABC123   4/16/2015         5/19/2015
ABC123   5/20/2015         6/29/2015
ABC123   6/30/2015         12/31/9999   (or current date)
 
Any help is greatly appreciated... thanks
 
 

dnoeth 4628 posts Joined 11/04
28 Mar 2016

The next row can be easily accessed using MIN OVER:

coalesce(min(InvoiceDate)               -- next row's date
         over (partition by ID
               order by InvoiceDate 
               rows between 1 following and 1 following) -1
        ,current_date|DATE '9999-12-31') -- or default date
 

 

Dieter

You must sign in to leave a comment.