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

zeeshanmajeedbutt

SSC Enthusiast

Points: 158

September 25, 2007 at 3:10 pm

This is how I was able to do that :).

set nocount on

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-18','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)

GO

INSERT 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)

GO

selectSequenceStarts.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 EndDate

from--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.WeekDate

from#PromoHistory A

left 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.WeekDate

from#PromoHistory A

left 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) SequenceEnds

on SequenceStarts.OutletID = SequenceEnds.OutletID

and SequenceStarts.ProductID = SequenceEnds.ProductID

and SequenceStarts.Price = SequenceEnds.Price

and SequenceStarts.Cost = SequenceEnds.Cost

and SequenceStarts.PromotionType = SequenceEnds.PromotionType

and SequenceStarts.AdType = SequenceEnds.AdType

and SequenceStarts.DisplayType = SequenceEnds.DisplayType

and SequenceStarts.[X Value] = SequenceEnds.[X Value]

and SequenceStarts.[Y Value] = SequenceEnds.[Y Value]

whereSequenceStarts.WeekDate <= SequenceEnds.WeekDate

group by SequenceStarts.OutletID,

SequenceStarts.ProductID,

SequenceStarts.Price,

SequenceStarts.Cost,

SequenceStarts.PromotionType,

SequenceStarts.AdType,

SequenceStarts.DisplayType,

SequenceStarts.[X Value],

SequenceStarts.[Y Value],

SequenceStarts.WeekDate

order by SequenceStarts.WeekDate

drop table #PromoHistory