sql server - Create variable name from while loop value SQL -

first time poster on site , need advice:

is possible declare/create new variable base on while loop?

context:

financial calendar company assigns each date within financial year period (month) , week number. these can change each year depending on date of new financial year.

i want create function runs while loop week , period number specified date.

i want able along lines of

while > @enddate     if date between such , such week = @w(i) end 

i not sure how explain it.

i have function listing week , period numbers looks messy , not efficient.

can advise?

thank you

edit:

apologies

my code in sql server.

what have far messy , complete draft i'll pop in here now.

declare @startdate datetime ,   @enddate datetime ,   @thisdate datetime ,   @p1 datetime ,   @p2 datetime ,   @p3 datetime ,   @p4 datetime --and forth until p12 , @w1   datetime , @w2   datetime , @w3   datetime , @w4   datetime , @w5   datetime --and forth until w52  set @startdate = '2012-12-30'  set @p1 = @startdate + 35 set @p2 = @p1 + 28 set @p3 = @p2 + 28 set @p4 = @p3 + 35 set @p5 = @p4 + 28 set @p6 = @p5 + 28 set @p7 = @p6 + 35 set @p8 = @p7 + 28 set @p9 = @p8 + 28 set @p10 = @p9 + 35 set @p11 = @p10 + 28 set @p12 = @p11 + 28  set @w1 = @startdate set @w2 = @w1 + 7 set @w3 = @w2 + 7 set @w4 = @w3 + 7 set @w5 = @w4 + 7 set @w6 = @w5 + 7  --while @thisdate > @enddate  set = 1  --begin      if @thisdate between @startdate , (@w2 - 1)      @thisweek = w1, @thisperiod = p1  --end 

one way of doing have auxiliary table listing weeks , associated periods, , join on table tables containing dates checked.

ex:

select      periods.week, tbl.rdate     tbl, periods      tbl.rdate between periods.startdate , periods.enddate 

would give week periods.week associated date tbl.rdate, table periods having columns week, startdate, enddate week number , start , end dates. add supplementary (indexed) column financial year if database has keep records on several years, , add constraint on column in queries reduce scan single financial year. 1 such year being 52 rows in average, should not problematic keep 20 years worth of periods, or tune db keep table in memory.