declare @t table (ID int,StartDate datetime,Name varchar(10))
insert	@t
select 1, '01/01/1980', 'N1' union all
select 1, '03/13/1985', 'N2' union all
select 1, '01/02/1989', 'N3' union all
select 1, '01/02/2008', 'N4' union all
select 1, '05/24/2009', 'N5' union all
select 2, '01/01/1981', 'B1'

/*
ID  StartDate  EndDate     Name
--------------------------------
1  01/01/1980  03/12/1985  N1
1  03/13/1985  01/01/1989  N2
1  01/02/1989  01/01/2008  N3
1  01/02/2008  05/23/2009  N4
1  05/24/2009  12/31/9999  N5
2  01/02/2006  12/31/9999  B1
*/

;WITH cte AS
(
    SELECT 
        ID, 
        StartDate, 
        Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY StartDate) AS RowNum
    FROM @t
)

SELECT
    A.ID,
    A.StartDate,
    COALESCE(DATEADD(DAY, -1, B.StartDate), '9999-12-31') AS EndDate,
    A.Name
FROM 
    cte AS A
LEFT OUTER JOIN
    cte AS B 
    ON A.ID = B.ID
    AND A.RowNum = B.RowNum - 1

quote:

---

Originally posted by ch9862

I have a table with "start date" column, and need to come up with "end date" based on other records for the same ID.
As an example, for the following data:

declare @t table (ID int,StartDate datetime,Name varchar(10))
insert	@t
select 1, '01/01/1980', 'N1' union all
select 1, '03/13/1985', 'N2' union all
select 1, '01/02/1989', 'N3' union all
select 1, '01/02/2008', 'N4' union all
select 1, '05/24/2009', 'N5' union all
select 2, '01/01/1981', 'B1'

I would expect this output:

/*
ID  StartDate  EndDate     Name
--------------------------------
1  01/01/1980  03/12/1985  N1
1  03/13/1985  01/01/1989  N2
1  01/02/1989  01/01/2008  N3
1  01/02/2008  05/23/2009  N4
1  05/24/2009  12/31/9999  N5
2  01/02/2006  12/31/9999  B1
*/

How can it be done?

---

quote:

---

Originally posted by visakh16
how did startdate for id 2 record change in output?

---

quote:

---

Originally posted by ch9862

quote:

---

Originally posted by visakh16
how did startdate for id 2 record change in output?

---

My mistake - it should be the same as in the source.

Thanks Lamprey - now I'll read up on how it works :).

---