Dynamic update

[hanchilicious]

Hi,

I have a script which, depending on the week the job is bring run, will update a different column.

declare @varcol varchar(10)
declare @ex varchar (200)
SELECT @varcol =  
(CASE 
WHEN LoadWeek = 1 then 'week01'
WHEN LoadWeek = 2 then 'week02'
WHEN LoadWeek = 3 then 'week03'
WHEN LoadWeek = 4 then 'week04'
WHEN LoadWeek = 5 then 'week05'
WHEN LoadWeek = 6 then 'week06'
WHEN LoadWeek = 7 then 'week07'
WHEN LoadWeek = 8 then 'week08'
WHEN LoadWeek = 9 then 'week09'
WHEN LoadWeek = 10 then 'week10'
WHEN LoadWeek = 11 then 'week11'
WHEN LoadWeek = 12 then 'week12'
WHEN LoadWeek >= 13 then 'week13'
END) 
from Bk_TSR_Forecast

select @ex = 'update tsrdb
set ' + @varcol + ' = b.' + @varcol + 
' from tsr_forecast b 
where tsrdb.trueid = b.customernumber 
and tsrdb.trueppn = b.product'

EXEC (@ex)

As you can see, this code will update a moving column, depending on the value of loadweek.

However, the business has decided that they need to populate ALL historical values, not just one. So, if LoadWeek = 10, then instead of just updating Week09, column Week01 through Week09 should be updated.

What is the best way to achieve this, please?

Thanks

 

[bborissov]

Personaly I would do something w/o dynamic SQL, like this:

update TsrDB
        set Week01 = CASE WHEN LoadWeek > 0
                          THEN b.Week01
                          ELSE TsrDB.Week1 END,
            Week02 = CASE WHEN LoadWeek > 1
                          THEN b.Week02
                          ELSE TsrDB.Week02 END,
            Week03 = CASE WHEN LoadWeek > 2
                          THEN b.Week03
                          ELSE TsrDB.Week03 END,
            Week04 = CASE WHEN LoadWeek > 3
                          THEN b.Week04
                          ELSE TsrDB.Week04 END,
....
            Week13 = CASE WHEN LoadWeek > 12
                          THEN b.Week13
                          ELSE TsrDB.Week13 END
FROM TsrDB
INNER JOIN Tsr_Forecast B ON tsrdb.trueid = b.customernumber
                         and tsrdb.trueppn = b.product

(not tested)


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP