Updating variable columns with variable value and derived values from variable

Question

Updating variable columns with variable value and derived values from variable

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Are you by any chance attempting to perform a pivot or unpivot (rows to columns or vice versa)? If so, there may be alternative methods which are a) "standard" and b) less complex. Can you attempt to describe the full process of which your post is a part?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Steve Jones - SSC Editor SSC Guru Points: 739541 More actions · Answer 2

It's not quite clear what you mean. I think you need to provide more DDL and sample data to show something.

If you're matching on 'Wed', then isn't F10 always Wednesday, so you just match for that column? Or is there something else?

Really, I think either you are trying to do some PIVOT, or you have some CASE that you need. I'd suggest you start with a query to try and determine the logic, not an update.

For example, if I wanted to match Wed, I could do.

select

'oldf9' = f9,

'f9' = case where @Weekday = 'Tue'

then @NewDate

end,

'oldf10' = f10,

'f10' = case where @Weekday = 'Wed'

then @NewDate

end

from target

You might play with the logic here, but I think you're better off structuring this rather than trying to build some dynamic SQL.

IF you want to do this:

update target

set @mycol = @newdate

where @somecol = @weekday

Where you can pass in @mycol as f1, f2, etc or @somecol as F8, f9, etc.

You can't.

This would show the values for some columns.

quentin.harris Ten Centuries Points: 1004 More actions · Answer 3

Hi,

Please find link to image of the input data, transformations and output.

I am taking a raw data input extracted via SQL statement in SSIS package and is loaded into a staging table. The SQL i am now trying to write will form a stored procedure that will clean and transform the data to then go into a main table.

I have to find the only full date in the file which is a text string (@VarDate) and convert to a date (@NewDate).

I then am looking through the row which has the abbreviated dates for the week listed as Sat through Fri in columns F6-F12. I want to update each of these abbreviated dates as dates 'yyyy-mm-dd'.

To do this i have to use their position relative to the, in this example, 'Wed' (which i put in a variable @WeekdayName).

I did this in VBA and had to write a long set of select case statements based on @WeekdayName to carry out the correct amendments to @NewDate to use to update each column F6-12.

The end stage is to pivot the dates with the data below split out e.g. Date in one column, then

(0/0) (0/0) becomes (0/0) in one column and (0/0) in another column. As shown in the image link.

Hope that helps.

Q

quentin.harris Ten Centuries Points: 1004 More actions · Answer 4

Hi,

I don't understand this bit:

select

'oldf9' = f9

I have the following:

Declare @Wed varchar(MAX)

Set @Wed =

(Select F10 from #TempBHRNELFT where F10 like '%Wed%')

Q

Steve Jones - SSC Editor SSC Guru Points: 739541 More actions · Answer 5

First, your link is broken. It doesn't matter what your SSIS process is. What we need is some test tables and DDL and data. Please create a table that mimics what you'll get as an input and insert some test data as a sample. Post the CREATE TABLE and INSERT statements. It helps if you surround code with the code-SQL formatted on the left of the edit box.

Second, I included an alias of oldF9 in the query, just to look at data. This way I can see the source and destination. I really have no idea what you are looking for, so I'm trying to give you a way to put together a query that helps you visualize what is happening.

You haven't explained the problem well. We can't see data, other than one sample row. We don't have anyone explaining what the output needs to be or where data goes, so we're guessing a bit.

This code

quentin.harris (6/28/2016)
Hi,
Declare @Wed varchar(MAX)
Set @Wed =
(Select F10 from #TempBHRNELFT where F10 like '%Wed%')
Q

Is problematic, because you're querying a table, which can be multiple rows, but you won't necessarily know what row will get assigned to the variable. Unless you guarantee that there is only one row.

quentin.harris Ten Centuries Points: 1004 More actions · Answer 6

Hi,

Thank you for the replies.

I have managed to write the script.

I will try and be more specific and give better examples in future.

I have had problems with links and pasting code today.

For future reference, how do i "surround code with the code-SQL formatted on the left of the edit box" ?

Thanks for your patience,

Q