create table products (
    productid int identity(1,1),
    createdate datetime
)
go

declare @counter int
set @counter = 1

while @counter < 1000
begin
    insert products values (getdate() - 107)
    set @counter = @counter + 1
end
go

create table #reportingweeks (
    weeknumber tinyint,
    reportdate datetime
)

declare @counter int, @givendate datetime
set @counter = 1
set @givendate = getdate()

while @counter <= 15
begin
    insert #reportingweeks values ((16 - @counter), (@givendate - (7 * @counter)))
    set @counter = @counter + 1
end

select
    productid,
    createdate,
    weeknumber
from
    products
    inner join #reportingweeks
        on  datediff(dd, reportdate, dateadd(dd, 15*7, createdate))<= 7
go

quote:

---

I didn't mean to sound so blonde...

---

I just typed and deleted about 5 responses to that one . . . . . . . . .

Jay

create table #weeks (
    weeknumber tinyint,
    reportdate datetime
)
declare @counter int, @startdate datetime, @enddate datetime, @startSQL varchar(255), @datecounter datetime, @sqlStr varchar(8000)

select @startdate = cast('01/01/02' as datetime), @enddate = cast('12/31/02' as datetime), @sqlStr = 'INSERT INTO #weeks (givendate, wk1, wk2, wk3, wk4, wk5, wk6, wk7, wk8, wk9, wk10, wk11, wk12, wk13, wk14, wk15) values ('

select @datecounter = @startdate

while @datecounter <= @enddate begin
     select @counter = 1, @sqlStr = @startSQL + @datecounter
     
     while @counter <= 15 begin
          select @sqlStr = @sqlStr + ', ' + dateadd(wk, @datecounter, -(7 * @counter))
          select @counter = @counter + 1
     end

     select @sqlStr = @sqlStr + ')'

     exec @sqlStr
     select @datecounter = dateadd(dd, @datecounter, 1)
end