469,890 Members | 2,164 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

Week Number In Loop

3
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 1872
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
declare @addthisweek 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
reesa
3
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.

how about now:

declare @yourdata as int
declare @addthisweek as int



select @yourdata = 200601, @addthisweek = 52

select @yourdata, @addthisweek

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),
(dateadd(week,@addthisweek,dateadd(week,cast(subst ring(cast(@yourdata as char(7)),5,3) as smallint)-1,cast('01/01/' + left(cast(@yourdata 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...
Aug 1 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by androtech | last post: by
7 posts views Thread by Shuffs | last post: by
2 posts views Thread by David Mitchell | last post: by
2 posts views Thread by Rustan | last post: by
3 posts views Thread by Mal | last post: by
18 posts views Thread by Jen | last post: by
6 posts views Thread by aarklon | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.