Lynn,

Good eye. Thanks. 😉

Lowell,

Both your quotes are great. I actually wonder how many people who come to SSC google "There is no spoon". I know I did. It took me awhile to understand why Ben-Gan and others make a big deal about the fact that we are dealing with un-ordered sets of data.

I don't have a clue what a broadcast calendar is - I was just taking a wild stab at what I thoght sql_er was looking for and this was a good chance to show off the power of the tally table.

sql_er,

This is why you have to post some DLL (note the first link in Lynn's signature); all we can do is guess otherwise.

I saw Media_Week_Start_Date and guessed that you were looking only for the first day of the week for each week of the year (and that is why you are getting 52 records). You will get all the days if you remove the "top1=1" from my code. Note the truncated example result set below - this is basically what I thought you were looking for:

Media_Week_Start_DateMedia_Week

2010-01-041

2010-01-112

2010-01-183

2010-01-254

2010-02-015

2010-02-086

2010-02-157

......

2010-12-0649

2010-12-1350

2010-12-2051

2010-12-2752

Re: " I have to admit that it will take me some time to digest/understand it."

The technique I used involves a tally table. Some people call it a "numbers table" or "auxilary table of numbers"; I call it a tally table because I learned about it here: The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url].. Stuff like "ROW_NUMBER() OVER (ORDER BY (SELECT NULL))" used to baffle the heck out of me. Below is some code that will help you better understand what is going on in my this example code below. Note my comments.

DECLARE @TodayIs date=GETDATE(),--a date

@n int=3;--a number

-- A quick DATEADD demo

SELECT@todayIS AS '@today',

@n AS '@n',

DATEADD(DAY,@n,@TodayIs) AS '@today + @n';

--What set based programming looks like

WITH

UnorderedSet(n)AS

(SELECT 1 UNION ALL

SELECT 1 UNION ALL

SELECT 1),

tallyTable(n) AS

(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) --The fastest way to count to {whatever} in SQL

FROM UnorderedSet)

SELECTn AS [n wearing nothing],

DATEADD(DAY,n,GETDATE()) AS [n dressed in (today's date) + (n)days]

FROM tallyTable;