Creating a periods table 1

[xxing]

Hi I have a periods table that has the following:

#periods
PeriodStart PeriodStop
null 2008-08-30
null 2008-07-30
null 2008-06-30

I would like to see the following

PeriodStart PeriodStop
2008-07-31 2008-08-30
2008-07-01 2008-07-30
2008-06-31 2008-06-30

The rule is:
Periodstart = add 1 day to the next periods periodstop

What is the code to populate the PeriodStart

Thank you for your assistence
Mark

 

[markros]

declare @PeriodStart datetime

update my Table set PeriodStart = @PeriodStart, @PeriodStart = PeriodStop + 1

not tested - make a backup first

 

[markros]

The solution I wrote was based on assumption that table is already ordered from first period to the last.

 

[xxing]

Hi Markos

I tried your code and it is not quite correct

It produces

PeriodStart PeriodStop
2008-08-31 2008-08-30
2008-07-31 2008-07-30
2008-07-01 2008-06-30

The expected result is
PeriodStart PeriodStop
2008-07-31 2008-08-30
2008-07-01 2008-07-30
2008-06-31 2008-06-30

i.e the periodstart is the next periodstop + 1 day

I would like to thank you for replying

Regards
Mark

 

[markros]

I almost solved it in SSMS, but right now I'm on a different computer. Basically, if you add an id field, you can just join with itself based on id = id+1 and then update your columns.

 

[xxing]

Hi Markos

I used the technique you mention of self join on the same table on id=id+1 and it works like a charm

Thank you very much. I give you a star for your expertise

Mark

 

[markros]

Sorry, that was my solution

if OBJECT_ID('#Periods') IS NOT NULL
   begin
     drop table #Periods   
   end
go
     
create table #Periods (id int identity(1,1),PeriodStart datetime null, PeriodStop datetime)
insert into #Periods (PeriodStop) values ('2008-08-30'),('2008-07-30'),('2008-06-30')

declare @PeriodStart datetime
update P set PeriodStart = P1.PeriodStop + 1 from #Periods P inner join #Periods P1 on P.id = P1.id -1
select * from #Periods