Posted by Dan Guzman on 11/02/06 12:58

One method:

CREATE TABLE dbo.TestTable
(
MyDate datetime
CONSTRAINT PK_TestTable PRIMARY KEY,
price1 int NOT NULL,
price2 int NOT NULL,
price3 int NOT NULL
)

INSERT INTO dbo.TestTable
SELECT '20060101', 100, 100, 100
UNION ALL SELECT '20060102', 100, 100, 100
UNION ALL SELECT '20060103', 100, 100, 100
UNION ALL SELECT '20060104', 115, 100, 100
UNION ALL SELECT '20060105', 115, 100, 100
UNION ALL SELECT '20060106', 115, 115, 115
UNION ALL SELECT '20060107', 115, 100, 100
UNION ALL SELECT '20060108', 100, 100, 100
UNION ALL SELECT '20060109', 100, 100, 100
GO

SELECT
FromDates.MyDate AS FromDate,
COALESCE(ToDates.MyDate, FromDates.MyDate) AS ToDate,
FromDates.Price1,
FromDates.Price2,
FromDates.Price3
FROM dbo.TestTable AS FromDates
LEFT JOIN dbo.TestTable AS ToDates ON
FromDates.MyDate = (
SELECT MIN(MyDate)
FROM dbo.TestTable AS b
WHERE
b.MyDate > FromDates.MyDate AND
b.Price1 = FromDates.Price1 AND
b.Price2 = FromDates.Price2 AND
b.Price3 = FromDates.Price3
)
WHERE
NOT EXISTS(
SELECT *
FROM dbo.TestTable AS b
WHERE
b.MyDate = FromDates.MyDate - 1 AND
b.Price1 = FromDates.Price1 AND
b.Price2 = FromDates.Price2 AND
b.Price3 = FromDates.Price3
)
ORDER BY
FromDates.MyDate


--
Hope this helps.

Dan Guzman
SQL Server MVP

<edouard.spooner@gmail.com> wrote in message
news:1162462283.727604.11730@h48g2000cwc.googlegroups.com...
> Hi,
>
> I have a tricky SQL query problem that I'm having probs with.
>
> I have a table which resembles something like this
>
> Date | Price1 | Price2 | Price3
> 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
>
> and I want to write a query/view that will return this
>
>>From | To | Price1 | Price2 | Price3
> 01 Jan 2006 | 03 Jan 2006 | 100 | 100 | 100
> 04 Jan 2006 | 05 Jan 2006 | 115 | 100 | 100
> 06 Jan 2006 | 06 Jan 2006 | 115 | 115 | 115
> 07 Jan 2006 | 07 Jan 2006 | 115 | 100 | 100
> 08 Jan 2006 | 09 Jan 2006 | 100 | 100 | 100
>
> Any ideas?
>
> I know how to write a routine that would do the same in VB but I am
> looking to do a lot of the same calculation/query so I need it to be
> fast (which VB wouldnt be)
>
> TIA
>
> Eddie
>

• Next in forum: Re: Tricky group by date problem
• Prev in forum: Re: Tricky group by date problem
• Thread view: Re: Tricky group by date problem

[Reply to this message]