Duplicate Date Output Window Function

Question

Sample data here:
http://rextester.com/VNGMF66717

I have the following data:

  ID    Year    Date    
1111    2016    2016-02-28
1111    2016    2016-02-28
1111    2016    2016-03-31
1111    2016    2016-03-31
1111    2016    2016-03-31
1111    2016    2016-04-02
1111    2016    2016-05-31
1111    2016    2016-08-01
1111    2016    2016-12-11
1111    2017    2017-01-02
1111    2017    2017-01-02
1111    2017    2017-02-04
1111    2017    2017-02-04
1111    2017    2017-07-08
2222    2016    2016-02-11
2222    2016    2016-02-11
2222    2016    2016-03-28
2222    2016    2016-03-28
2222    2016    2016-03-28
2222    2016    2016-07-22
2222    2016    2016-12-31
2222    2017    2017-02-01
2222    2017    2017-02-14

I want to add a NextDate column (which is reset in each identifier and year) using the input window function directly in the SELECT statement, instead of joining it myself using the RANK window function, as I did in the above example.

NextDate column should be exactly like

  ID    Year    Date        NextDate
1111    2016    2016-02-28  2016-03-31
1111    2016    2016-02-28  2016-03-31
1111    2016    2016-03-31  2016-04-02
1111    2016    2016-03-31  2016-04-02
1111    2016    2016-03-31  2016-04-02
1111    2016    2016-04-02  2016-05-31
1111    2016    2016-05-31  2016-08-01
1111    2016    2016-08-01  2016-12-11
1111    2016    2016-12-11  NULL
1111    2017    2017-01-02  2017-02-04
1111    2017    2017-01-02  2017-02-04
1111    2017    2017-02-04  2017-07-08
1111    2017    2017-02-04  2017-07-08
1111    2017    2017-07-08  NULL
2222    2016    2016-02-11  2016-03-28
2222    2016    2016-02-11  2016-03-28
2222    2016    2016-03-28  2016-07-22
2222    2016    2016-03-28  2016-07-22
2222    2016    2016-03-28  2016-07-22
2222    2016    2016-07-22  2016-12-31
2222    2016    2016-12-31  NULL
2222    2017    2017-02-01  2017-02-14
2222    2017    2017-02-14  NULL

Does anyone know how to do this correctly?

Answer 1

, , select: MAX (LEAD), .

select
    id, yr, dt
    ,MAX(nx_dt) OVER (PARTITION BY id, dt) nxt_dt
FROM
    (
        select 
            *
            , LEAD(dt) OVER (PARTITION BY id, yr ORDER BY id, yr, dt) nx_dt
        from
        #testtable
    ) sub

/*
drop table #testtable

create table #testtable
(
    id int, yr int, dt date
)
insert into #testtable
(
    id, yr, dt
)
SELECT 1111,2016,'2016-02-28'   union all
SELECT 1111,2016,'2016-02-28'   union all
SELECT 1111,2016,'2016-03-31'   union all
SELECT 1111,2016,'2016-03-31'   union all
SELECT 1111,2016,'2016-03-31'   union all
SELECT 1111,2016,'2016-04-02'   union all
SELECT 1111,2016,'2016-05-31'   union all
SELECT 1111,2016,'2016-08-01'   union all
SELECT 1111,2016,'2016-12-11'   union all
SELECT 1111,2017,'2017-01-02'   union all
SELECT 1111,2017,'2017-01-02'   union all
SELECT 1111,2017,'2017-02-04'   union all
SELECT 1111,2017,'2017-02-04'   union all
SELECT 1111,2017,'2017-07-08'   union all
SELECT 2222,2016,'2016-02-11'   union all
SELECT 2222,2016,'2016-02-11'   union all
SELECT 2222,2016,'2016-03-28'   union all
SELECT 2222,2016,'2016-03-28'   union all
SELECT 2222,2016,'2016-03-28'   union all
SELECT 2222,2016,'2016-07-22'   union all
SELECT 2222,2016,'2016-12-31'   union all
SELECT 2222,2017,'2017-02-01'   union all
SELECT 2222,2017,'2017-02-14'
*/

Answer 2

outer apply :

select cte.*, next_cte.date
from cte outer apply
     (select top 1 cte2.*
      from cte cte2
      where cte2.id = cte.id and cte2.year = cte.year and
            cte2.date > cte.date
      order by cte2.date desc
     ) next_cte;

lead() :

select cte.*, cte_next.next_date
from cte join
     (select id, year, date,
             lead(date) over (partition by id, year order by date) as next_date
      from cte
      group by id, year, date
     ) cte_next
     ON cte.id = cte_next.id and cte.year = cte_next.year and cte.date = cte_next.date;

Answer 3

Another approach would be to use a correlated subquery that gets the next date of the year if it LEADreturns the same date as the current date for the identifier.

SELECT 
      [ID]
     ,[Year]
     ,[Date]
     ,CASE WHEN LEAD([Date]) OVER(PARTITION BY [ID],[Year] ORDER BY [Date]) = [Date]
      THEN (SELECT MIN([Date]) FROM CTE WHERE [Date]>c.[Date] and [ID]=c.[ID] and [Year]=c.[Year])
      ELSE LEAD([Date]) OVER(PARTITION BY [ID],[Year] ORDER BY [Date]) END AS [NextDate]
FROM CTE C