Posted by markc600 on 11/02/06 12:54

Try this

create table #mytable(dt datetime,Price1 int, Price2 int, Price3 int)
insert into #mytable(dt,Price1,Price2,Price3)
select '01 Jan 2006',100,100,100 union all
select '02 Jan 2006',100,100,100 union all
select '03 Jan 2006',100,100,100 union all
select '04 Jan 2006',115,100,100 union all
select '05 Jan 2006',115,100,100 union all
select '06 Jan 2006',115,115,115 union all
select '07 Jan 2006',115,100,100 union all
select '08 Jan 2006',100,100,100 union all
select '09 Jan 2006',100,100,100


select min(dt) as [From],
max(dt) as [To],
Price1,
Price2,
Price3
from (
select a.dt,
a.Price1,
a.Price2,
a.Price3,
a.dt-(select count(*)
from #mytable b
where b.Price1=a.Price1
and b.Price2=a.Price2
and b.Price3=a.Price3
and b.dt <= a.dt) as Num
from #mytable a
) X
group by Price1,Price2,Price3,Num
order by 1

• 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]