well - unless I'm completely off of the boat - OP is looking to do some fancy aggregation. He's trying to get a summary of the promo's on a given item, based on continuous weeks of promos. doing this based on recording sale prices of given items by outlets each week.

So - these three records

WeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]

2006-10-21, 00370, a, b, c, d, e, f, g h

2006-10-28, 00370, a, b, c, d, e, f, g, h

2006-11-04, 00370, a, b, c, d, e, f, g, h

2006-11-11, 00370, a, b, c, d, e, f, g, h

2006-11-18, 00370, a, b, c, d, e, f, g, h

2006-11-25, 00370, a, b, c, d, e, f, g, h

Summarizes to one record

Start, end, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]

2006-10-21,2006-11-25, 00370, a, b, c, d, e, f, g h

whereas this

WeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]

2006-10-21, 00370, a, b, c, d, e, f, g h

2006-10-28, 00370, a, b, c, d, e, f, g, h

2006-11-04, 00370, a, b, c, d, e, f, g, h

--notice the missing weeks in the here - more than 7 days between records

2006-11-18, 00370, a, b, c, d, e, f, g, h

2006-11-25, 00370, a, b, c, d, e, f, g, h

Summarizes to 2 records

Start, end, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]

2006-10-21,2006-11-04, 00370, a, b, c, d, e, f, g h

2006-11-18,2006-11-25, 00370, a, b, c, d, e, f, g h

A break in the weeks sequence (by productid+outletID), or a change in ANY of the other data elements would engender another record.

Zee - is that right?