Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

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
Update NewTable set NewTable.DateColumn = OldTable.Date1, NewTable.ResultsColumn = OldTable.Results1 where NewTable.ID = OldTable.ID

Open in new window

Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Note that this is for the same table.  (Create to temporary columns)
you can then copy over the real data or you can modify it to directly update the other table.  
update table 
set 
RealDate = case when  
                                       date1 is not null and len(isnull(Results1, '')) > 0 
                                                 then date1
                             when  
                                       date2 is not null and len(isnull(Results2, '')) > 0 
                                                 then date2
                             when  
                                       date3 is not null and len(isnull(Results3, '')) > 0 
                                                 then date3
                              end,
realResult = case when  
                                       date1 is not null and len(isnull(Results1, '')) > 0 
                                                 then result1
                             when  
                                       date2 is not null and len(isnull(Results2, '')) > 0 
                                                 then result2
                             when  
                                       date3 is not null and len(isnull(Results3, '')) > 0 
                                                 then result3
                              end,

Open in new window

Avatar of bfuchs

ASKER

Hi,

First thanks for the suggestion.

Just looking at the first paragraph
when  
                                       date1 is not null and len(isnull(Results1, '')) > 0 
                                                 then date1

Open in new window

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
Avatar of bfuchs

ASKER

@Kyle,

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

Open in new window

SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.  

(
when results1 is not null then 1 
when results2 is not null then 2
when results3 is not null then 3
else 0 end

Open in new window

)

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:

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?
Avatar of bfuchs

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.
Avatar of bfuchs

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
Avatar of bfuchs

ASKER

@Scott,
IF Results# columns can be blank instead of NULL
Not 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
Avatar of bfuchs

ASKER

That seems to work, great Job!