Find next date for certain record in SQL Server 2008

Posted by Karl on Stack Overflow See other posts from Stack Overflow or by Karl
Published on 2010-03-25T08:48:36Z Indexed on 2010/03/25 8:53 UTC
Read the original article Hit count: 562

Filed under:
|
|

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

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about date