472,784 Members | 1,020 Online

# Week Number In Loop

Hi All ,

I am having some problem in week number culculation.

What i have to do is I will get as set of data with ord _week eg.200728,200729,200730

Basically i will have a parameter to control this loop of order week.

let say parameter is 33 so i need to do looping for 33 weeks starting week number exp 200642.

so my week number will expand untill year 2007XX

since we have 52 weeks how actually i can add up the weeks until it reaches 33 weeks for my calculation.

Pls help to guide me.Thanks.
Jul 31 '07 #1
3 2090
ck9663
2,878 Expert 2GB
Hi All ,

I am having some problem in week number culculation.

What i have to do is I will get as set of data with ord _week eg.200728,200729,200730

Basically i will have a parameter to control this loop of order week.

let say parameter is 33 so i need to do looping for 33 weeks starting week number exp 200642.

so my week number will expand untill year 2007XX

since we have 52 weeks how actually i can add up the weeks until it reaches 33 weeks for my calculation.

Pls help to guide me.Thanks.
someone better have a shorter solution than this:

declare @yourdata as int

select @yourdata = 200642, @addthisweek = 33

select cast(datepart(year,dateadd(week,@addthisweek,datea dd(week,cast(substring(cast(@yourdata as char(7)),5,3) as smallint),cast('01/01/' + left(cast(@yourdata as char(7)),4) as datetime)))) as varchar(4)) +
cast(datepart(week,dateadd(week,@addthisweek,datea dd(week,cast(substring(cast(@yourdata as char(7)),5,3) as smallint),cast('01/01/' + left(cast(@yourdata as char(7)),4) as datetime)))) as varchar(4))
Jul 31 '07 #2
thank you.

i have tried this bid and its workking.But at the point it suppose to be 200701 it selects it self (weekno) = 200653...but for 200654 it become 200702 which is correct ..i have tried to modify...not much helpful to solve it.
Aug 1 '07 #3
ck9663
2,878 Expert 2GB
thank you.

i have tried this bid and its workking.But at the point it suppose to be 200701 it selects it self (weekno) = 200653...but for 200654 it become 200702 which is correct ..i have tried to modify...not much helpful to solve it.

declare @yourdata as int

select @yourdata = 200601, @addthisweek = 52

select cast(datepart(year,dateadd(week,@addthisweek,datea dd(week,cast(substring(cast(@yourdata as char(7)),5,3) as smallint)-1,cast('01/01/' + left(cast(@yourdata as char(7)),4) as datetime)))) as varchar(4))+
right('00' + cast(datepart(week,dateadd(week,@addthisweek,datea dd(week,cast(substring(cast(@yourdata as char(7)),5,3) as smallint)-1,cast('01/01/' + left(cast(@yourdata as char(7)),4) as datetime)))) as varchar(4)),2),

another thing, if you check datepart(week,'12/31/2006') it will return you 53. the reason is, it's the 53rd week of the year. so you might want to do an if @addthisweek = 53 @addthisweek = @addthisweek - 1 kind of condition...
Aug 1 '07 #4