Grouping dates cause overlap

[hkaing79]

I have a table with tblPrice having fields:
StartDate ProductID Price
1/1/06 1 $10
1/2/06 1 $10
1/3/06 1 $5
1/4/06 1 $10

Is it possible to query this table so the result would look like:

ProductID Price MinDate MaxDate
1 $10 1/1/06 1/2/06
1 $5 1/3/06 1/3/06
1 $10 1/4/06 1/4/06

Using Min and Max will give me only two lines:
1, $10, 1/1/06-1/4/06
1, $5, 1/3/06

Any ideas would be greatly appreciated.

Thanks.

 

[lespaul]

maybe something like:

SELECT A.PRODUCTID, A.PRICE, A.STARTDATE AS MINDATE, B.STARTDATE AS MAXDATE
FROM TBLPRICE A
INNER JOIN TBLPRICE B ON A.PRODUCTID < B.PRODUCTID



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lespaul]

nope that's not right (joined on the wrong field!), try this:

SELECT A.PRODUCTID, A.PRICE, A.STARTDATE AS MINDATE, B.STARTDATE AS MAXDATE
FROM TBLPRICE A
INNER JOIN TBLPRICE B ON A.STARTDATE < B.STARTDAT

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi