Need help with UPDATE

[spartan2010]

Hello Friends,

I have a small table TEST with Keys,Start date & End date .Here are three sample rows.

keys start date end date

1 2 3 4 5/10/2010 4PM 5/10/2010 4PM
1 2 3 4 5/11/2010 4PM 5/10/2010 4PM
1 2 3 4 5/12/2010 4PM 5/13/2010 4PM

The first two rows' end dates are wrong.The end date should be the same as start date of the next.They should be like the following


keys start date end date

1 2 3 4 5/10/2010 4PM 5/11/2010 4PM
1 2 3 4 5/11/2010 4PM 5/12/2010 4PM
1 2 3 4 5/12/2010 4PM 5/13/2010 4PM



How can I easily update the end dates with an UPDATE statement?

 

[markros]

;with cte as (select *, row_number() over (partition by Keys order by StartDate) as Row from Test),
ct1 as (select c1.Keys, c1.Start_Date, c2.Start_Date as End_Date from cte c1 inner join cte c2 on c1.Keys = c2.Keys and c1.Row = c2.Row - 1 where c1.EndDate <> c2.StartDate)

update Test set EndDate = c1.EndDate 
from Test inner join ct1 c1 on Test.Keys = c1.Keys
where Test.EndDate <> c1.EndDate

PluralSight Learning Library

 

[spartan2010]

Thanks Markros..

One problem though...It updates all the rows for the end date with the first value end date value...5/11/2010 4PM

 

[markros]

Verify that you're getting the right result by just using select * from Ct1 (add original c1.End_Date and c2.Start_Date in the ct1)

PluralSight Learning Library

 

[spartan2010]

No luck so far Markros! Can I email you the query & the structure of the table?

 

[markros]

Here is the full test I just tried:

declare @t table (Keys varchar(100), Start_Date datetime, End_Date datetime)
insert into @t
values
('1 2 3 4', '5/10/2010 4PM',  '5/10/2010 4PM'),
('1 2 3 4', '5/11/2010 4PM',  '5/10/2010 4PM'),
('1 2 3 4', '5/12/2010 4PM',  '5/13/2010 4PM')

;with cte as (select *, row_number() over (partition by Keys order by Start_Date) as Row from @t),
ct1 as (select c1.Keys, c1.Start_Date, c2.Start_Date as End_Date from cte c1 
inner join cte c2 on c1.Keys = c2.Keys and c1.Row = c2.Row - 1 where c1.End_Date <> c2.Start_Date)

--select * from ct1

update Test set End_Date = c1.End_Date 
from @t Test inner join ct1 c1 on Test.Keys = c1.Keys and Test.Start_Date = c1.Start_Date 
where Test.End_Date <> c1.End_Date

select * from @t

PluralSight Learning Library

 

[spartan2010]

So close...Looks like the order of the rows went wrong

Start dt End dt

5/10/10 11:20 AM 5/27/10 10:58 AM
5/27/10 10:58 AM 5/27/10 10:47 AM
5/27/10 10:47 AM 5/27/10 10:47 AM

 

[markros]

Do you have unique key in your table? I joined based on Start_Date. If you have unique key, it may be better.

In my test case it worked just fine.

PluralSight Learning Library