Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Help with TSQL, getting StartDate and EndDate from WeekDate !!! Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2007 8:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:27 PM
Points: 7,179, Visits: 15,781
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?


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #403042
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse