That is In my select query I want to get the Startdate, StartDate, Enddate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]

In each set of same OutletId; same ProductId; same Price; same cost; same PromotionType; same AdType; same DisplayType; same [X Value]; and same [Y Value], I want to populate those new columns. I want to populate [Startdate] with the earliest WeekDate in the set and [EndDate] with the largest WeekDate in the set.

So there are two things that must be considerd.

a) It's a same Set (meaning everything is same except Weeklydate)[/b].

And b) the differnce between the WeeklyDate as compare to the previous WeeklyDate in the Set is 7 (be sure they are sort based on WeekDate).As long as those 2 criteria are met there should always be 1 record generated per set in the SELECT statement.

In the above example note the following three records.

WeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

Now lets check the 2 criteria. A)Its a same set; meaning it has same OutletId; same ProductId; same Price; same cost; same PromotionType; same AdType; same DisplayType; same [X Value]; and same [Y Value]. B) For the first two rows only the difference between the Weekdate is 7. So the SELECT statement should return the following 2 records:

StartDate, Enddate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-11-04, 2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL

In the 2nd row of the SELECT statement please note that since one of the criteria was not matched then the EndDate will be calculated as follows
Enddate=StartDate+7, so it will be Endate=2006-11-25 + 7, which is 2006-12-02.

Also not that in the SELECT the Enddate and StartDate cannot be same, neither can anyone of them be NULL.

So for the above example the SELECT should return the following results.

StartDate, Enddate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]
2006-10-21, 2006-10-28, 00370, 000-38000-31841, 3.02, 2.53, Price Promotion, NULL, NULL, 3, 2
2006-10-28, 2006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, 2, 2
2006-11-04, 2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL
2007-03-24, 2007-03-31, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKE, IN AISLE, NULL, NULL
2007-03-31, 2007-04-07, 00370, 000-38000-31841, 2.50, 1.97, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-07, 2007-04-14, 00370, 000-38000-31841, 2.50, 2.39, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-04-14, 2007-04-28, 00370, 000-38000-31841, 2.50, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-05-05, 2007-05-05, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL
2007-05-12, 2007-05-19, 00370, 000-38000-31841, 2.53, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL

CAN SOME PLEASE HELP....
THANKS A MILLION IN ADVANCE.....

Zee

I am not a SQL expert. Please guide me how I can achieve this. First of all do you fully understand the problem?

I have created a script to create a temperory table & then insert records for this example. You or other people can then test the script against it.

Here is the CREATE table & Insert script for you.

--Start of Script
Create table #PromoHistory
(
WeekDate datetime NOT NULL, OutletId nvarchar(50) NOT NULL, ProductId nvarchar(50) NOT NULL,Price decimal(18,2) NOT NULL,Cost decimal(18,2) NOT NULL,PromotionType nvarchar(50) NOT NULL,
AdType nvarchar(50),DisplayType nvarchar(50),[X Value] nvarchar(50),[Y Value] nvarchar(50))
GO

INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2006-10-21','00370','000-38000-31841',3.02,2.53,'Price Promotion',NULL,NULL,'3','2')
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2006-10-28','00370','000-38000-31841',2.99,2.53,'Price Promotion',NULL,NULL,2,2)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2006-11-04','00370','000-38000-31841',2.99,2.53,'Price Promotion',NULL,NULL,NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2006-11-11','00370','000-38000-31841',2.99,2.53,'Price Promotion',NULL,NULL,NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2006-11-25','00370','000-38000-31841',2.99,2.53,'Price Promotion',NULL,NULL,NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2007-03-24','00370','000-38000-31841',2.51,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2007-03-31','00370','000-38000-31841',2.50,1.97,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2007-04-07','00370','000-38000-31841',2.50,2.39,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2007-04-14','00370','000-38000-31841',2.50,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2007-04-21','00370','000-38000-31841',2.50,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2007-04-28','00370','000-38000-31841',2.50,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2007-05-05','00370','000-38000-31841',2.51,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
GO
INSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])
VALUES   ('2007-05-12','00370','000-38000-31841',2.53,2.53,'Price Promotion','SHELF TALKER','IN AISLE',NULL,NULL)
--End of Script

For this example the script that I am looking for should return the result that I have posted earlier.

Can you please help?

Desperatley waiting for help.

Zee

Indexing could help quite a bit, and/or a computed column with an index on the computation.  In particular, a outletid+productid+weekdate composite index.

--use a second table to identify where the breaks > 7 days are

create table #ptemp (WeekDate datetime NOT NULL, OutletId nvarchar(50) NOT NULL, ProductId nvarchar(50) NOT NULL)

insert #ptemp (outletid,productID,weekdate)

select p.outletid,p.productID,p.weekdate

from #PromoHistory p left outer join #PromoHistory p2

on p.outletid=p2.outletID and p.productID=p2.productid and p2.weekdate=dateadd(dd,-7,p.weekdate)

where p2.weekdate is null

--add an index to help performance some

create index ptmp on #ptemp(outletid,productid)

--get the data

select ranktmp,min(WeekDate) startdate , max(dateadd(dd,7,weekdate)) endweek, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value]

from (select ph.*, count(pt.weekdate) ranktmp from #promohistory ph inner join #ptemp pt on ph.OutletId=pt.outletid and ph.ProductId=pt.productid

where pt.weekdate<=ph.weekdate

group by

ph.WeekDate, ph.OutletId, ph.ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value]

) ptmp

group by OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value], ranktmp

order by outletid, productid,ranktmp, min(WeekDate),max(weekdate)

--clean up and remove secondary table

drop table #ptemp

select

    DateAdd( dd, 1 - DatePart(dw, WeekDate), WeekDate ) as StartDate,

    DateAdd( dd, 7 - DatePart(dw, WeekDate), WeekDate ) as EndDate,

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

from    #PromoHistory;