Re: Query to provide range of repeat values

Joe Mellors wrote:

> I need a query to provide range data where rows have repeated data.
> I'll explain :
>
> Tables concerned are like this :
>
> Year Cost
> ---- ----
> 2000 10
> 2001 10
> 2002 20
> 2003 20
> 2004 20
> 2005 15
> 2006 10
>
> But I need to provide the costs in order of time but with the time as
> a range like this :
>
> Start End Cost
> ----- --- ----
> 2000 2001 10
> 2002 2004 20
> 2005 2005 15
> 2006 2006 10
>
> At first I thought I'd cracked it with :
>
> SELECT DISTINCT a.cost,
> (SELECT MIN(year) FROM mytable b WHERE b.cost = a.cost) AS start,
> (SELECT MAX(year) FROM mytable c WHERE c.cost = a.cost) AS end
> FROM mytable a
> ORDER BY start
>
> which would produce what I am looking for if none of the costs were
> repeated for a later time range (e.g. above where there are two ranges
> with a cost of 10)
>
> Is this possible ?

Write a stored procedure.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)