sql server - Writing a CASE statement to return values based on dates in a single record -

i assuming solution kind of case statement feel free suggest alternatives.

our company works in 4 week periods , have table fields follows (apologies displaying way, tried few other methods , looked horrific):

• period
• startdate - start date of period
• enddate - end date of period
• w1_end - end date of 1st week of period
• w2_end - end date of 2nd week of period
• w3_end - end date of 3rd week of period

i have second table containing populated date field, , empty period , week fields need populate based on data in above field.

i can period field in second table updated enough can't work out how update put 1,2,3 or 4 in week field based on whether date falls in 1st, 2nd, 3rd or 4th week in period. i've started on few ideas becomes obvious write more it's not going work. makes sense. 1 option guess me ignore wx_end fields , use between start_date , start date + 7 week 1 kind of ideas?

please suggest way (redesigning period table isn't going possible!)?

update using join , case want... quick , dirty code... switch dates column names are

update table2    set table2.period = table1.period,       table2.weekfield = case          when table2.datefield between table1.date1 , table1.date2 1          when table2.datefield between table1.date2 , table1.date3 2          when table2.datefield between table1.date3 , table1.date4 3          else null          end   table2    inner join table1 on (whatever)   table2 (whatever)