Find next date for certain record in SQL Server 2008
- by Karl
Hi
In SQL Server 2008:
I have two tables, dtlScheme and dtlRenewal, with a one to many relationship (one scheme can have many renewals). dtlRenewal has a unique key (dteEffectiveDate, dtlSchemeID).
Now suppose I have the following data in dtlRenewal:
dtlRenewalID dtlSchemeID dteEffectiveDate
1 1 1/1/2005
2 1 1/1/2006
3 1 1/1/2007
4 1 1/1/2008
5 1 1/1/2009
I would like to find for each renewal the next and previous effective date for the scheme. In other words, I need to return this:
dtlRenewalID dtlSchemeID dteEffectiveDate dtePrevious dteNext
1 1 1/1/2005 NULL 1/1/2006
2 1 1/1/2006 1/1/2005 1/1/2007
3 1 1/1/2007 1/1/2006 1/1/2008
4 1 1/1/2008 1/1/2007 1/1/2009
5 1 1/1/2009 1/1/2008 NULL
Thanks
Karl