We help IT Professionals succeed at work.

Get Information from Previous Record and Insert into Next Record

MajikTara
MajikTara asked
on
462 Views
Last Modified: 2013-11-05
I have created a temp table with the following information:
ProjectID      StartDate    EndDate    InterestRate
12                 1/1/06         12/31/06    .07
12                 1/1/06         12/31/07     .08
14                 3/5/07         12/31/07     .08
14                 3/5/07         08/05/07     .07
14                 3/5/07         12/31/08    .065

What I need is to look at the previous record for the project (12) and update the StartDate for the next record with the same project with the previous record's EndDate (12/31/06). So my table should show;

ProjectID      StartDate    EndDate    InterestRate
12                 1/1/06         12/31/06    .07
12                 12/31/06     12/31/07     .08
14                 3/5/07         12/31/07     .08
14                 12/31/07      08/05/08     .07
14                 08/05/07      12/31/08    .065

I am not sure how to do this. Any help would be greatly appreciated
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
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

Open in new window

Author

Commented:
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
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

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

Author

Commented:
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
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
post the sql code you're using.

Author

Commented:
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

Open in new window

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.