Week Number In Loop

[reesa]

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,20072 9,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.

[ck9663]

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,20072 9,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
declare @addthisweek as int

select @yourdata = 200642, @addthisweek = 33

select cast(datepart(y ear,dateadd(wee k,@addthisweek, dateadd(week,ca st(substring(ca st(@yourdata as char(7)),5,3) as smallint),cast( '01/01/' + left(cast(@your data as char(7)),4) as datetime)))) as varchar(4)) +
cast(datepart(w eek,dateadd(wee k,@addthisweek, dateadd(week,ca st(substring(ca st(@yourdata as char(7)),5,3) as smallint),cast( '01/01/' + left(cast(@your data as char(7)),4) as datetime)))) as varchar(4))

[reesa]

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.

[ck9663]

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.

how about now:

declare @yourdata as int
declare @addthisweek as int



select @yourdata = 200601, @addthisweek = 52

select @yourdata, @addthisweek

select cast(datepart(y ear,dateadd(wee k,@addthisweek, dateadd(week,ca st(substring(ca st(@yourdata as char(7)),5,3) as smallint)-1,cast('01/01/' + left(cast(@your data as char(7)),4) as datetime)))) as varchar(4))+
right('00' + cast(datepart(w eek,dateadd(wee k,@addthisweek, dateadd(week,ca st(substring(ca st(@yourdata as char(7)),5,3) as smallint)-1,cast('01/01/' + left(cast(@your data as char(7)),4) as datetime)))) as varchar(4)),2),
(dateadd(week,@ addthisweek,dat eadd(week,cast( substring(cast( @yourdata as char(7)),5,3) as smallint)-1,cast('01/01/' + left(cast(@your data as char(7)),4) as datetime))))



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...