SQLTeam.com | Weblogs | Forums

Updating variable columns with variable value and derived values from variable

tsql
sql2012

#1

Hi,

I want to update variable columns with a variable values.
The target range is row 6 of a temp table. See below:

---------------------Target range
Select * from #TempBHRNELFT where
id =6

which yields

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,

Quentin


#2

Would you please post, as easily copy/paste-able:

  1. CREATE TABLE statement for the table
  2. DECLARE statements for the variables
  3. INSERT INTO statement to populate the table

Please also explain why you do not have proper column names and why the dates are not standard date formats.


#3

Hi,

I am trying to post the SQL but it keeps saying bad gateway 502.

Q


#4

The table is loaded to by an SSIS package. There are no proper column names as it is a staging table ,which via temp tables, leads to a dataset which is loaded to a final table which has headers.

The dates are as received in the source file. I have to manipulate the text string full date in the file (@VarDate) to get @NewDate (which is a date format)).

I would happily post the script except i keep getting bad gateway messages when i place the code in here and try to submit.

Q


#5

Hi,

Does anyone know how i format SQL script so i don't get a bad gateway script when posting?
I tried Ctrl,k and Ctrl+K but that asks me to insert a hyperlink.

Q


#6

Try using crtl+c to copy and ctrl+v to paste.


#7

Thanks. There problem is that when i paste in SQL i always get a bad gateway notification. I thought it was because i am not formatting the SQL probably for insertion. I thought i saw instructions somewhere to press Ctrl K to format code. But that doesn't seem to work so i have been unable to post my code. :frowning:


#8

@graz is there anyway you can help @HarrisQ


#9

This is a hosted forum. Let me check with the hosting folks. So far I'm not seeing it anywhere else.