Get Information from Previous Record and Insert into Next Record

this should do it:

create table #temp
(projectid int, startdate datetime, enddate datetime, interestrate float)
 
insert into #temp
select 12,                 '1/1/06',         '12/31/06',    .07 union all
select 12,                 '1/1/06',         '12/31/07',     .08 union all
select 14,                 '3/5/07',         '12/31/07',     .08 union all
select 14,                 '3/5/07',         '08/05/07',     .07 union all
select 14,                 '3/5/07',         '12/31/08',    .065
 
--run this as ONE big statement
with mycte(ranking, ProjectID,     StartDate,    EndDate ,   InterestRate)
as
(
select ranking = dense_rank() over(partition by projectid order by startdate asc, enddate asc), *
from #temp
)
update o
set
startdate = 
isnull((
select min(enddate) from mycte i
where i.ranking + 1 = o.ranking
),startdate)
from mycte o

Select allOpen in new window

chapmandew,

I did some slight mods to your code:
create table #temp
(projectid int, startdate datetime, enddate datetime, interestrate float, keynumber int)
 
insert into #temp
select ProjectID, InterestCalcStartDate, EndDate, InterestRate, keynumber
from #Table3 ;
 
--run this as ONE big statement
with mycte(ranking, ProjectID,     StartDate,    EndDate ,   InterestRate, keynumber)
as
(
select ranking = dense_rank() over(partition by projectid order by startdate asc, enddate asc), *
from #temp
)
update o
set
startdate =
isnull((
select min(enddate) from mycte i
where i.ranking + 1 = o.ranking
),startdate)
from mycte o

SELECT * FROM #temp
ORDER BY
      KeyNumber, EndDate

I seem to have a problem with the UNION ALL portion. I have tried to put it with the SELECT but with no luck. Any idea's? MT

no reason for a UNION ALL in the statements I gave you....

Then I have something wrong, here is the results I am getting:
ProjectID        StartDate                           EndDate                                                  Key
1647      2006-03-01 00:00:00.000      2006-12-31 00:00:00.000      0.0002515      24
1647      2002-12-31 00:00:00.000      2007-12-31 00:00:00.000      0.0003063      24
1647      2002-12-31 00:00:00.000      2008-08-05 10:22:22.600      0.0003356      24
1408      2004-12-31 00:00:00.000      2006-12-31 00:00:00.000      0.0002515      25
1408      2004-12-31 00:00:00.000      2007-12-31 00:00:00.000      0.0003063      25
1408      2004-12-31 00:00:00.000      2008-08-05 10:22:22.600      0.0003356      25

Should be:
ProjectID        StartDate                           EndDate                                                  Key
1647      2006-03-01 00:00:00.000      2006-12-31 00:00:00.000      0.0002515      24
1647      2006-12-31 00:00:00.000      2007-12-31 00:00:00.000      0.0003063      24
1647      2007-12-31 00:00:00.000      2008-08-05 10:22:22.600      0.0003356      24
1408      2006-12-01 00:00:00.000      2006-12-31 00:00:00.000      0.0002515      25
1408      2006-12-31 00:00:00.000      2007-12-31 00:00:00.000      0.0003063      25
1408      2007-12-31 00:00:00.000      2008-08-05 10:22:22.600      0.0003356      25

Thanks, MT

what do you get when you run this?

with mycte(ranking, ProjectID,     StartDate,    EndDate ,   InterestRate, keynumber)
as
(
select ranking = dense_rank() over(partition by projectid order by startdate asc, enddate asc), *
from #temp
)
select
startdate =
isnull((
select min(enddate) from mycte i
where i.ranking + 1 = o.ranking
),startdate), *
from mycte o

chapmandew:
Here is the results (for project 1647 only)
startdate     Ranking         ProjectID        StartDate      EndDate        Interest          KeyNumber
2002-12-31 3      1647      2007-01-01      2008-08-05  0.0003356      24
2006-03-01 1      1647      2006-03-01       2006-12-31       0.0002515      24
2002-12-31 2      1647      2007-01-01       2007-12-31       0.0003063      24

MT

post the sql code you're using.

Attached is the code, the table #Table3 contains all of the projects (6800 records).
MT

create table #temp
(projectid int, startdate datetime, enddate datetime, interestrate float, keynumber int)
 
insert into #temp
select ProjectID, InterestCalcStartDate, EndDate, InterestRate, keynumber 
from #Table3 ;  --this is a temp table that contains the above information for many projects
 
--run this as ONE big statement
with mycte(ranking, ProjectID,     StartDate,    EndDate ,   InterestRate, keynumber)
as
(
select ranking = dense_rank() over(partition by projectid order by startdate asc, enddate asc), *
from #temp
)
select
startdate =
isnull((
select min(enddate) from mycte i
where i.ranking + 1 = o.ranking
),startdate), *
from mycte o
ORDER BY projectid desc, keynumber, ranking
 
DROP TABLE #Table1
DROP TABLE #Table2
DROP TABLE #Table3
DROP TABLE #temp

Select allOpen in new window

Thanks chapmandew! That works, just by adding the AND i.projectid....
Your help is greatly appreciated!!!!!!
MT