I want to update variable columns with a variable values.
The target range is row 6 of a temp table. See below:
Select * from #TempBHRNELFT where
Id F4 F6 F7 F8 F9 F10 F11 F12
6 NULL Sat 28th Sun 29th Mon 30th Tues 31st Wed 1st Thur 2nd Fri 3rd
I also have two variables @NewDate and @WeekDayName that work with a variable string which is a string date e.g. Wednesday 1st June 2016 stored in a variable @Date
@NewDate holds the conversion of the string as a date e.g.'2016-06-01'
@WeekDay holds an abbreviation of the input variable string e.g. 'Wed'
I want something that matches the @Weekday value to the column where found. In this example it would be Column F10. I then want to update Column F10 to =@NewDate where id=6.
I want to use the position of @NewDate (in row where id =6) to set the other columns values to dates so the output would be:
Id F4 F6 F7 F8
6 NULL 2016-05-28 2016-05-29 2016-05-30 etc......
As the input string varies each day (sourced from SSIS load of daily Excel file) e.g. tomorrow might be 'Thursday 2nd June 2016', i need to have a dynamic SQL query/procedure to populate dates where id =6.
Column F6 is always a Saturday, F7 always a Sunday,F8 a Monday etc.
Any help gratefully received,