bfuchs
asked on
Need help constructing a conditional update query
Hi Experts,
I am looking to place the contents of 6 fields into 2 fields as follows.
We have Date1 Column (Datetime) and Results1 column (varchar(50), then Date2 column and Results2, and Date3 Column and Results3.
Now we would like to bring over these info into a table that only has one Date and Results column.
So the logic would be, if Date1 and Results1 column both has data then just take that, otherwise check if Date2 and Results2 both has data then take that, otherwise if Date3 and Results3 both has data then take that.
However if none of them have a full set of data, then look which has date, starting with Date1, Date2 and Date3..
Also one thing to keep in mind, they work as a pair, so if Date1 is null Results1 has data, and Date2 has data Results2 is null, in that case we want bring over only Date2 leaving out the results.
How can I do that with one update statement?
PS. I'm using the term update instead of an insert, as the records already exists with matching ID, so I will be doing something like
I am looking to place the contents of 6 fields into 2 fields as follows.
We have Date1 Column (Datetime) and Results1 column (varchar(50), then Date2 column and Results2, and Date3 Column and Results3.
Now we would like to bring over these info into a table that only has one Date and Results column.
So the logic would be, if Date1 and Results1 column both has data then just take that, otherwise check if Date2 and Results2 both has data then take that, otherwise if Date3 and Results3 both has data then take that.
However if none of them have a full set of data, then look which has date, starting with Date1, Date2 and Date3..
Also one thing to keep in mind, they work as a pair, so if Date1 is null Results1 has data, and Date2 has data Results2 is null, in that case we want bring over only Date2 leaving out the results.
How can I do that with one update statement?
PS. I'm using the term update instead of an insert, as the records already exists with matching ID, so I will be doing something like
Update NewTable set NewTable.DateColumn = OldTable.Date1, NewTable.ResultsColumn = OldTable.Results1 where NewTable.ID = OldTable.ID
ASKER
Hi,
First thanks for the suggestion.
Just looking at the first paragraph
Same applies for Date2.
BTW, in order to test it I guess will make a select statement first of those two columns, correct?
Thanks,
Ben
First thanks for the suggestion.
Just looking at the first paragraph
when
date1 is not null and len(isnull(Results1, '')) > 0
then date1
There is also another possibility when it should update to Date1, in the case when none of the other pairs have a full set filled in, e.g if only 3 Date columns are filled, in that case it should update to Date1 although results1 is null.Same applies for Date2.
BTW, in order to test it I guess will make a select statement first of those two columns, correct?
Thanks,
Ben
ASKER
@Kyle,
I will log out for now & will continue tom.
Thanks,
Ben
I will log out for now & will continue tom.
Thanks,
Ben
Will this work for you?
Update NewTable
set NewTable.DateColumn = ISNULL(OldTable.Date1, ISNULL(OldTable.Date2, OldTable.Date3))
, NewTable.ResultsColumn = ISNULL(OldTable.Results1, ISNULL(OldTable.Results2, OldTable.Results3))
where NewTable.ID = OldTable.ID
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Experts,
@Kyle,
So far the logic makes sense, will test this in a select statement as mentioned and let you know.
@Scott,
Your version is way more advanced than my current skills..however if it works I'm fine with.
Can you tell me how to test this as a select statement?
btw, do you see an issue with Kyle's suggestion?
Thanks,
Ben
@Kyle,
So far the logic makes sense, will test this in a select statement as mentioned and let you know.
@Scott,
Your version is way more advanced than my current skills..however if it works I'm fine with.
Can you tell me how to test this as a select statement?
btw, do you see an issue with Kyle's suggestion?
Thanks,
Ben
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Interesting solution Scott.
bfuchs: Just confirming you can not have a result without a date? If you can you need a few mroe when's for Scott's statement.
(
Essentially he's using 2 flags in one number. The "tens" placeholder is the date, the "ones" placeholder is the results flag. Do a little division / mod magic and you can pull what's null and what's not.
bfuchs: Just confirming you can not have a result without a date? If you can you need a few mroe when's for Scott's statement.
(
when results1 is not null then 1
when results2 is not null then 2
when results3 is not null then 3
else 0 end
)Essentially he's using 2 flags in one number. The "tens" placeholder is the date, the "ones" placeholder is the results flag. Do a little division / mod magic and you can pull what's null and what's not.
I took it that Results only went with its own paired Date, based on this from the original q:
Not to necessarily say that I interpreted that correctly, but that was my take on it.
Also one thing to keep in mind, they work as a pair, so if Date1 is null Results1 has data, and Date2 has data Results2 is null, in that case we want bring over only Date2 leaving out the results.
Not to necessarily say that I interpreted that correctly, but that was my take on it.
Ah, Yeah, I misread that. I was doing as a last resort take any date with any result. I believe you may be correct on that. Still a nice use of the divisor / mod.
I guess my question would be what if you have results and no date?
I guess my question would be what if you have results and no date?
ASKER
Just confirming you can not have a result without a date?Good point, we do have such instances, however in that case we dont want import the results.
What modification is needed in order to apply this?
Thanks,
Ben
for my solution take out the else coalesce with the results. For Scott's I believe you can leave it as is.
ASKER
I was doing as a last resort take any date with any result.Oh no, this will definitely not work, started already testing yours, didn't realized that would be the outcome..
So I guess will test now Scott's suggestion.
Thanks,
Ben
ASKER
@Scott,
wouldn't that result to null?
Anyway how do I check for that? (select results1 from mytable where results1 = '')?
if thats what you mean then no, just checked and nothing = ''
Thanks,
Ben
IF Results# columns can be blank instead of NULLNot sure what blank means, if user started entering some info and then used back space?
wouldn't that result to null?
Anyway how do I check for that? (select results1 from mytable where results1 = '')?
if thats what you mean then no, just checked and nothing = ''
Thanks,
Ben
ASKER
That seems to work, great Job!
you can then copy over the real data or you can modify it to directly update the other table.
Open in new window