Updating variable columns with variable value and derived values from variable
-
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
-
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 ShawFor 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 -
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.
-
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
-
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
-
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.
-
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply