Find next date for certain record in SQL Server 2008

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, dtlS开发者_StackOverflow社区chemeID).

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

try this:

DECLARE @YourTable table (dtlRenewalID int, dtlSchemeID int, dteEffectiveDate datetime)
SET NOCOUNT ON
INSERT @YourTable VALUES (1,1,'1/1/2005')
INSERT @YourTable VALUES (2,1,'1/1/2006')
INSERT @YourTable VALUES (3,1,'1/1/2007')
INSERT @YourTable VALUES (4,1,'1/1/2008')
INSERT @YourTable VALUES (5,1,'1/1/2009')
INSERT @YourTable VALUES (6,2,'1/1/2005') --I just repeated the data to make sure 
INSERT @YourTable VALUES (7,2,'1/1/2006') --it would work with multiple dtlSchemeID
INSERT @YourTable VALUES (8,2,'1/1/2007') --values, which it does
INSERT @YourTable VALUES (9,2,'1/1/2008')
INSERT @YourTable VALUES(10,2,'1/1/2009')
SET NOCOUNT OFF


;WITH YourTableCTE AS
(SELECT
    dtlRenewalID, dtlSchemeID, dteEffectiveDate
        ,ROW_NUMBER() OVER(PARTITION by dtlSchemeID order by dtlSchemeID,dtlRenewalID) AS RowNumber
    FROM @YourTable
)
SELECT
    c.dtlRenewalID, c.dtlSchemeID, c.dteEffectiveDate, p.dteEffectiveDate AS dtePrevious, n.dteEffectiveDate AS dteNext
    FROM YourTableCTE                 c
        LEFT OUTER JOIN YourTableCTE  p ON c.dtlSchemeID=p.dtlSchemeID AND c.RowNumber-1=p.RowNumber
        LEFT OUTER JOIN YourTableCTE  n ON c.dtlSchemeID=n.dtlSchemeID AND c.RowNumber+1=n.RowNumber

OUTPUT:

dtlRenewalID dtlSchemeID dteEffectiveDate        dtePrevious             dteNext
------------ ----------- ----------------------- ----------------------- -----------------------
1            1           2005-01-01 00:00:00.000 NULL                    2006-01-01 00:00:00.000
2            1           2006-01-01 00:00:00.000 2005-01-01 00:00:00.000 2007-01-01 00:00:00.000
3            1           2007-01-01 00:00:00.000 2006-01-01 00:00:00.000 2008-01-01 00:00:00.000
4            1           2008-01-01 00:00:00.000 2007-01-01 00:00:00.000 2009-01-01 00:00:00.000
5            1           2009-01-01 00:00:00.000 2008-01-01 00:00:00.000 NULL
6            2           2005-01-01 00:00:00.000 NULL                    2006-01-01 00:00:00.000
7            2           2006-01-01 00:00:00.000 2005-01-01 00:00:00.000 2007-01-01 00:00:00.000
8            2           2007-01-01 00:00:00.000 2006-01-01 00:00:00.000 2008-01-01 00:00:00.000
9            2           2008-01-01 00:00:00.000 2007-01-01 00:00:00.000 2009-01-01 00:00:00.000
10           2           2009-01-01 00:00:00.000 2008-01-01 00:00:00.000 NULL

(10 row(s) affected)

The above results is incorrect.

For instance - where previous date is 2005-01-01 00:00:00.000 the next date is 2007-01-01 00:00:00.000 where the next date in above case should be 2006-01-01 00:00:00.000.