Create Expiration Date based on next Date Value

General

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

Tags (3)
1 REPLY
Senior Apprentice

Re: Create Expiration Date based on next Date Value

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