Computing a date interval

[liuk]

I'm looking for another solution to my little problem.

In my db i have a date column (StartDate) that is the start date of a price.

Now i need a report (price list) with the valid date interval of each price.

For example:

PriceId StartDate
1 2006-05-01
2 2006-05-08
3 2006-05-16
...

i need this
PriceId StartDate EndDate
1 2006-05-01 2006-05-07
2 2006-05-08 2006-05-15
3 2006-05-16 --

the end date of each price is the day before the start date of the next one.

To get that i write :

UPDATE PriceList
SET EndDate= (Select dateadd(d, -1, Min(StartDate))
From #PriceList B
Where datediff(d, PriceList.StartDate, B.StartDate) >0)


Now, it works ! But i wondering if is there another way to do it.

thanks

 

[BrianB]

You could do aa subselect to get the next price startdate in the table instead of storing the enddate. A subselect and a top 1 expression would get the next record.

Brian Begy
BugSentry - Automatic error reporting for .NET and COM

http://www.bugsentry.com