I'm attempting to create some sales statistics based on weekly sales.



I've got my sales categorized by weeks, and I want to join it to a table
that has every week #, as well as the date of the monday that this
particular week falls on.



SO like

Week # Date

1 Jan-1-2003

2 Jan-8-2003



And so on and so forth. Any ideas?

(I want to use this table to outer join my table of sales it's getting
to be a headache)


--
Posted via http://dbforums.com

Something like the following - change the select to an insert

--
Declare @YearStart datetime
Declare @WeekStart int
Declare @MyDate datetime
Set @YearStart = '01/01/2003'
Set @WeekStart = 1
--
select @MyDate =
case DatePart(weekday,@YearStart)
when 1 then DateAdd(day,1,@YearStart)
when 2 then @YearStart
else
dateAdd(day,9-DatePart(weekday,@YearStart),@YearStart)
end

-- insert into table
While Datepart(year,@MyDate)<YEAR(@YearStart)+1
Begin
select @WeekStart,Convert(nvarchar(12),@MyDate,109)
set @WeekStart=@Weekstart+1
set @Mydate = DateAdd(day,7,@MyDate)
end
--


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!