代码之家 › 专栏 › 技术社区 › Karl

在SQL Server 2008中查找特定记录的下一个日期

date select sql-server-2008

0

Karl · 技术社区 · 16 年前

在SQL Server 2008中:

我有两个表,dtlscheme和dtlrenewal,具有一对多关系(一个方案可以有多个续签)。dtlrenewal有一个唯一的键(dteeeffectivedate,dtlschemeid)。

现在假设我在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

我想为每次续签寻找下一个和上一个计划的生效日期。换句话说,我需要归还这个:

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

谢谢

卡尔

2 回复 | 直到 13 年前

1

2

KM. 16 年前

试试这个:

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

输出:

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)

2

0

user1974357 13 年前

上述结果不正确。

例如,如果上一个日期是2005-01-01 00:00:00.000,则下一个日期是2007-01-01 00:00:00.000,在这种情况下,下一个日期应该是2006-01-01 00:00:00.000。