SQLServerCentral / SQL Server 7,2000 / T-SQL / Help with TSQL, getting StartDate and EndDate from WeekDate !!! / Latest Posts

RE: Help with TSQL, getting StartDate and EndDate from WeekDate !!!

Matt Miller (#4) — Wed, 26 Sep 2007 08:13:20 GMT

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 recordsWeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]2006-10-21, 00370, a, b, c, d, e, f, g h2006-10-28, 00370, a, b, c, d, e, f, g, h2006-11-04, 00370, a, b, c, d, e, f, g, h2006-11-11, 00370, a, b, c, d, e, f, g, h2006-11-18, 00370, a, b, c, d, e, f, g, h2006-11-25, 00370, a, b, c, d, e, f, g, hSummarizes to one recordStart, 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 hwhereas thisWeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]2006-10-21, 00370, a, b, c, d, e, f, g h2006-10-28, 00370, a, b, c, d, e, f, g, h2006-11-04, 00370, a, b, c, d, e, f, g, h--notice the missing weeks in the here - more than 7 days between records2006-11-18, 00370, a, b, c, d, e, f, g, h2006-11-25, 00370, a, b, c, d, e, f, g, hSummarizes to 2 recordsStart, 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 h2006-11-18,2006-11-25, 00370, a, b, c, d, e, f, g hA 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?

RE: Help with TSQL, getting StartDate and EndDate from WeekDate !!!

SwePeso — Wed, 26 Sep 2007 07:31:40 GMT

Wait a minute? What is the objective here?1) Same week (sun-sat or mon-sun)2) Any "7-day period" starting with first non-sequential date?

RE: Help with TSQL, getting StartDate and EndDate from WeekDate !!!

Jeff Moden — Wed, 26 Sep 2007 06:32:38 GMT

Zee,The reason I asked the questions is because the solution you're so happy with (thank you for posting it) does 4 table scans... if the right indexes are present, it will still do at least 2 table scans. It'll work a lot better if you can get some code that groups things by week instead of 7 days. Tom Carr's code will beat the tar out of the code you posted.

RE: Help with TSQL, getting StartDate and EndDate from WeekDate !!!

SwePeso — Wed, 26 Sep 2007 04:57:14 GMT

And you got a LOT of help here!!!http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89705

RE: Help with TSQL, getting StartDate and EndDate from WeekDate !!!

Jeff Moden — Tue, 25 Sep 2007 20:56:09 GMT

Zee,Couple of questions... trying to help a bit, if needed...First, how long does it take your final code (thank you for posting it) to run on all of those rows? In the absense of any other info, it looks to me like it's gonna do 4 full table scans.Second, you never said what the first day of a week was (or, at least I missed that part)...Third, why do you think there's only 7 days between 11/04 and 11/11... there's actually 8 because you must count 11/04 as the first day...11/04 = 111/05 = 211/06 = 311/07 = 411/08 = 511/09 = 611/10 = 711/11 = 8

RE: Help with TSQL, getting StartDate and EndDate from WeekDate !!!

Tomm Carr — Tue, 25 Sep 2007 16:36:21 GMT

I think this does what you want.[code]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;[/code]The sample data you gave had all entries in a different week. If, however, you have two or more entries within the same week, this would generate duplicate rows in the result set if all the other info in the row was the same. To remove those, use SELECT DISTINCT instead of just SELECT.

RE: Help with TSQL, getting StartDate and EndDate from WeekDate !!!

zeeshanmajeedbutt — Tue, 25 Sep 2007 15:10:22 GMT

This is how I was able to do that :).[b]set nocount onCreate 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))GOINSERT 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')GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])VALUES ('2006-11-18','00370','000-38000-31841',2.99,2.53,'Price Promotion',NULL,NULL,NULL,NULL)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT INTO #PromoHistory(WeekDate, OutletId, ProductId, Price, Cost,PromotionType,AdType,DisplayType,[X Value],[Y Value])VALUES ('2007-05-19','00370','000-38000-31841',2.99,2.53,'Price Promotion',NULL,NULL,NULL,NULL)GOselectSequenceStarts.OutletID,SequenceStarts.ProductID,SequenceStarts.Price,SequenceStarts.Cost,SequenceStarts.PromotionType,SequenceStarts.AdType,SequenceStarts.DisplayType,SequenceStarts.[X Value],SequenceStarts.[Y Value],SequenceStarts.WeekDate as StartDate,case when min(SequenceEnds.WeekDate) = SequenceStarts.WeekDate then dateadd(week, 1, SequenceStarts.WeekDate) else min(SequenceEnds.WeekDate) end as EndDatefrom--SequenceStarts(selectA.OutletId,A.ProductId,A.Price,A.Cost,A.PromotionType,Coalesce(A.AdType, '') as AdType,Coalesce(A.DisplayType, '') as DisplayType,Coalesce(A.[X Value], '') as [X Value],Coalesce(A.[Y Value], '') as [Y Value],A.WeekDatefrom#PromoHistory Aleft outer join #PromoHistory B on A.OutletID = B.OutletID and A.ProductID = B.ProductID and A.Price = B.Price and A.Cost = b.Cost and A.PromotionType = B.PromotionType and Coalesce(A.AdType, '') = Coalesce(B.AdType, '') and Coalesce(A.DisplayType, '') = Coalesce(B.DisplayType, '') and Coalesce(A.[X Value], '') = Coalesce(B.[X value], '') and Coalesce(A.[Y Value], '') = Coalesce(B.[Y value], '') and A.WeekDate = dateadd(Week, 1, B.WeekDate)whereB.OutletID is null) SequenceStarts inner join --SequenceEnds(selectA.OutletId,A.ProductId,A.Price,A.Cost,A.PromotionType,Coalesce(A.AdType, '') as AdType,Coalesce(A.DisplayType, '') as DisplayType,Coalesce(A.[X Value], '') as [X Value],Coalesce(A.[Y Value], '') as [Y Value],A.WeekDatefrom#PromoHistory Aleft outer join #PromoHistory B on A.OutletID = B.OutletID and A.ProductID = B.ProductID and A.Price = B.Price and A.Cost = b.Cost and A.PromotionType = B.PromotionType and Coalesce(A.AdType, '') = Coalesce(B.AdType, '') and Coalesce(A.DisplayType, '') = Coalesce(B.DisplayType, '') and Coalesce(A.[X Value], '') = Coalesce(B.[X value], '') and Coalesce(A.[Y Value], '') = Coalesce(B.[Y value], '') and A.WeekDate = dateadd(Week, -1, B.WeekDate)whereB.OutletID is null) SequenceEndson SequenceStarts.OutletID = SequenceEnds.OutletIDand SequenceStarts.ProductID = SequenceEnds.ProductIDand SequenceStarts.Price = SequenceEnds.Priceand SequenceStarts.Cost = SequenceEnds.Costand SequenceStarts.PromotionType = SequenceEnds.PromotionTypeand SequenceStarts.AdType = SequenceEnds.AdTypeand SequenceStarts.DisplayType = SequenceEnds.DisplayTypeand SequenceStarts.[X Value] = SequenceEnds.[X Value]and SequenceStarts.[Y Value] = SequenceEnds.[Y Value]whereSequenceStarts.WeekDate <= SequenceEnds.WeekDategroup by SequenceStarts.OutletID,SequenceStarts.ProductID,SequenceStarts.Price,SequenceStarts.Cost,SequenceStarts.PromotionType,SequenceStarts.AdType,SequenceStarts.DisplayType,SequenceStarts.[X Value],SequenceStarts.[Y Value],SequenceStarts.WeekDateorder by SequenceStarts.WeekDatedrop table #PromoHistory[/b]

RE: Help with TSQL, getting StartDate and EndDate from WeekDate !!!

Matt Miller (#4) — Mon, 24 Sep 2007 13:42:53 GMT

This should give you correct results, but won't be the fastest thing on the planet on your large dataset.

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

RE: Help with TSQL, getting StartDate and EndDate from WeekDate !!!

zeeshanmajeedbutt — Mon, 24 Sep 2007 11:12:03 GMT

Steve,

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.[b]

--Start of ScriptCreate 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))GOINSERT 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')GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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)GOINSERT 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[/b]

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

RE: Help with TSQL, getting StartDate and EndDate from WeekDate !!!

Steve Jones - SSC Editor — Mon, 24 Sep 2007 09:32:56 GMT

Your question is a bit convoluted and so I'm not quite sure what you want from the data.Querying by week, you could use the datepart() function and use the week number to group rows together by week. Then you could easily use the min date from that week and it should be the startdate according to what I gather from your data. The enddate would be startdate + 7, no need to calculate.Not sure if this would help, but you might try writing a few queries with that function (need a group by as well) and see if that helps.

Help with TSQL, getting StartDate and EndDate from WeekDate !!!

zeeshanmajeedbutt — Mon, 24 Sep 2007 08:30:19 GMT

Hi All,I have a table called [b]PromoHistory[/b] with the following fields,types, and Null/NotNull attributes.[b]WeekDate (datetime) NOT NULL[/b][b]OutletId (nvarchar) NOT NULL[/b][b]ProductId (nvarchar) NOT NULL[/b][b]Price (decimal) NOT NULL[/b][b]cost (decimal) NOT NULL[/b][b]PromotionType (nvarchar) NOT NULL[/b][b]AdType (nvarchar)[/b][b]DisplayType (nvarchar)[/b][b][X Value] (nvarchar)[/b][b][Y Value] (nvarchar)[/b]This table has 721651 records. Following is an example of records for one of the ProductID in one of the OutletID (Although this table have thousands of Products and hundreds of Outlet) (please note that records are order by WeekDate asc).(each coulmn is seperated by comma)WeekDate, OutletId, ProductId, Price, cost, PromotionType, AdType, DisplayType, [X Value], [Y Value]2006-10-21, 00370, 000-38000-31841, 3.02, 2.53, Price Promotion, NULL, NULL, 3, 22006-10-28, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, 2, 22006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL2006-11-25, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL2007-03-24, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-03-31, 00370, 000-38000-31841, 2.5, 1.97, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-04-07, 00370, 000-38000-31841, 2.5, 2.39, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-04-14, 00370, 000-38000-31841, 2.5, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-04-21, 00370, 000-38000-31841, 2.5, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-04-28, 00370, 000-38000-31841, 2.5, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-05-05, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-05-12, 00370, 000-38000-31841, 2.53, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULLNow, based on PromoHistory table I want have a SELECT statement excluding WeekDate but want to include the following two columns:[b]StartDate (datetime) [/b][b]EndDate (datetime) [/b]

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.

[b]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, NULL2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL2006-11-25, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULLNow 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, NULL2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULLIn 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, 22006-10-28, 2006-11-04, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, 2, 22006-11-04, 2006-11-11, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL2006-11-25, 2006-12-02, 00370, 000-38000-31841, 2.99, 2.53, Price Promotion, NULL, NULL, NULL, NULL2007-03-24, 2007-03-31, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKE, IN AISLE, NULL, NULL2007-03-31, 2007-04-07, 00370, 000-38000-31841, 2.50, 1.97, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-04-07, 2007-04-14, 00370, 000-38000-31841, 2.50, 2.39, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-04-14, 2007-04-28, 00370, 000-38000-31841, 2.50, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-05-05, 2007-05-05, 00370, 000-38000-31841, 2.51, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULL2007-05-12, 2007-05-19, 00370, 000-38000-31841, 2.53, 2.53, Price Promotion, SHELF TALKER, IN AISLE, NULL, NULLCAN SOME PLEASE HELP....THANKS A MILLION IN ADVANCE.....Zee