This is known as finding islands (no gaps) in your data. If you enumerate the rows based on the values of (price1, price2, price3), ordered by the [date] and you substract that number as days from the [date], then you will have a grouping value or anchor
date for each sequence. The rest is to calculate the min and max [date] for each group (price1, price2, price3, grp).
USE tempdb;
GO
DECLARE @T TABLE (
[date] date NOT NULL,
price1 money NOT NULL,
price2 money NOT NULL,
price3 money NOT NULL
);
INSERT INTO @T ([date], price1, price2, price3)
VALUES
('01 Jan 2006', 100, 100, 100),
('02 Jan 2006', 100, 100, 100),
('03 Jan 2006', 100, 100, 100),
('04 Jan 2006', 115, 100, 100),
('05 Jan 2006', 115, 100, 100),
('06 Jan 2006', 115, 115, 115),
('07 Jan 2006', 115, 100, 100),
('08 Jan 2006', 100, 100, 100),
('09 Jan 2006', 100, 100, 100);
WITH rs AS (
SELECT
[date],
price1,
price2,
price3,
DATEADD([day], -1 * ROW_NUMBER() OVER(PARTITION BY price1, price2, price3 ORDER BY [date]), [date]) AS grp
FROM
@T
)
SELECT
MIN([date]) AS date_from,
MAX([date]) AS date_to,
price1, price2, price3
FROM
rs
GROUP BY
price1, price2, price3,
grp
ORDER BY
date_from
GO
You can learn more about finding islands in the last book by Itzik Ben-Gan, about T-SQL Querying.
Inside Microsoft SQL Server 2008: T-SQL Querying
AMB
Some guidelines for posting questions...
