Alan.B (3/21/2013)

---

The query below should do the trick. I don't know what day counts as "Media Start of Week" but I went with monday (isowk). Also, I don't know what you want if someone passed 1/10/2010 as the start date... Would the first record (assuming order by date) to be 1/4/2010 or 1/11/2010... my logic will return 1/4/2010.

DECLARE @startDate AS date='1/10/2010',

@endDate AS date='6/1/2011'

SELECT @startDate=DATEADD(WEEK,-1,@startDate) -- lazy logic to handle the first week_start...

;WITH

tally(n) AS

(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1

FROM master.dbo.spt_values),

allDates AS

(SELECTn, DATEADD(DAY,n,@startDate) as [date]

FROM tally

WHERE n<=DATEDIFF(DAY,@startdate,@endDate)),

FilteredCalendar AS

(SELECTn as spoon,

[date] AS Media_Week_Start_Date,

DATEPART(MONTH,([date])) AS Media_Month,

DATEPART(isowk,([date])) AS Media_Week,

DATENAME(M,([date])) AS Media_Month_Name,

DATEPART(QUARTER,[date]) AS Media_Quarter,

DATEPART(YEAR,[date]) AS Media_Year

FROM allDates),

top1 AS

(SELECT TOP 2000000 RANK() OVER (PARTITION BY Media_Week ORDER BY Media_Week_Start_Date) AS top1,*

FROM FilteredCalendar

ORDER BY Media_Week_Start_Date)

SELECT--top1, n, --uncomment to tinker with the logic

Media_Week_Start_Date, Media_Month, Media_Week, Media_Month_Name, Media_Quarter, Media_Year

FROM top1

WHERE top1=1 AND spoon>0

ORDER BY spoon

PS See Lowell's signature if you are wondering why I named the sort key "spoon".

PS.PS... Take a look at that query plan... nice and linear :cool::cool::cool::cool::cool: