Posted by Razvan Socol on 11/02/06 13:09

Hello, Ed

Sorry for not looking close enough at the sample data and expected
result. Here is my first attempt at the real problem:

SELECT (
SELECT MIN(Date) FROM TheTable d
WHERE d.Date<=a.Date
AND a.Price1=d.Price1 AND a.Price2=d.Price2 AND a.Price3=d.Price3
AND NOT EXISTS (
SELECT * FROM TheTable e
WHERE e.Date BETWEEN d.Date AND a.Date
AND (e.Price1<>d.Price1 OR e.Price2<>d.Price2 OR e.Price3<>d.Price3)
)
) as StartDate, Date as EndDate,
Price1, Price2, Price3
FROM TheTable a
WHERE NOT EXISTS (
SELECT * FROM TheTable b
WHERE a.Date<b.Date
AND a.Price1=b.Price1 AND a.Price2=b.Price2 AND a.Price3=b.Price3
AND NOT EXISTS (
SELECT * FROM TheTable c
WHERE c.Date BETWEEN a.Date AND b.Date
AND (c.Price1<>b.Price1 OR c.Price2<>b.Price2 OR c.Price3<>b.Price3)
)
)

The above query was inspired by reading (a few years ago) the following
article:
http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp

Razvan

[Back to original message]