Start & End Range Query 1

[psychoflea]

Hi, i'm trying to find the start date & end date for a range of like data for example

Turn this:

ProductNo Date Price
12345 10/7/10 £1.50
12345 11/7/10 £1.50
12345 12/7/10 £1.50
12345 13/7/10 £2.30
12345 14/7/10 £2.30
12345 15/7/10 £1.50
12345 16/7/10 £1.50
98765 10/7/10 £2.99
98765 10/7/10 £2.99

Into this:

ProductNo StartDate EndDate Price
12345 10/7/10 12/7/10 £1.50
12345 13/7/10 14/7/10 £2.30
12345 15/7/10 16/7/10 £1.50
98765 10/7/10 12/7/10 £2.99

A simple min/max on the Date won't work as the Price might be used more than once in different ranges of dates.

Any suggestions?
Many Thanks
James

 

[markros]

Take a look at this blog post explaining this exact problem

http://pratchev.blogspot.com/2010/02/refactoring-ranges.html

PluralSight Learning Library

 

[simian336]

select ProductNo , min(date), max(date)
from dbo.TimesheetEntry
group by ProductNo

Simi

 

[simian336]

oops... Missed the price

Simi

 

[psychoflea]

Thanks Markros, thats spot on what I was looking for!

Just out of interest this was my final code

Select Max(ProductNo) as ProductNo, Min(Date) as StartDate, Max(Date) as EndDate, Max(Price) as Price
from
(
SELECT TOP (100) PERCENT ROW_NUMBER() OVER(PARTITION BY [ProductNo],Price ORDER BY Date) AS rn1, ProductNo, Date, Price,
convert(varchar,convert(int,ProductNo)) + convert(varchar,DATEADD(DAY, -1 * ROW_NUMBER() OVER(PARTITION BY ProductNo,Price ORDER BY Date), Date),104)+ convert(varchar,Price) AS grp
FROM dbo.PricesTable
ORDER BY ProductNo,Date, rn1
) as T
GROUP BY grp
ORDER BY ProductNo, Min(Date)