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, 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

Answers


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.


Need Your Help

Variable Range Used for a Formula

excel vba excel-vba

I recorded the below code for use in my macro, but it would only be useful if that range never changes.

BFS, DFS searches required to mark as Visited for trees?

graph tree depth-first-search breadth-first-search

Looking at the BFS and DFS algorithms they seem to mark the nodes as visited. If I am navigating trees only is it still necessary for my implementation to mark nodes as visited or not? I want to pe...