Using value from cloumns to generate a new value for new column

Question

Using value from cloumns to generate a new value for new column

sqlbaby2

Ten Centuries

Points: 1351
More actions
October 26, 2015 at 2:45 am

#305801

Hi
I've data as below
account period01 period02 period03 period04
1111 null null null null
1112 782 null null null
1113 null null null 345
1114 765 882 67 321
what i want to achieve is to get values from period1 till period04 and used the lasted value to code the value of accoutperiod,
if value is from period1 then code it as 01, period2 as 02, period03 as 03 and period04 as 04. so the output should be like this
account period01 period02 period03 period04 accoutPeriod
1111 null null null null null
1112 782 null null null 01
1113 null null null 345 04
1114 765 882 67 321 04

Viewing 3 posts - 1 through 2 (of 2 total)

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

sqlbaby2 (10/26/2015)
Hi
I've data as below
account period01 period02 period03 period04
1111 null null null null
1112 782 null null null
1113 null null null 345
1114 765 882 67 321
what i want to achieve is to get values from period1 till period04 and used the lasted value to code the value of accoutperiod,
if value is from period1 then code it as 01, period2 as 02, period03 as 03 and period04 as 04. so the output should be like this
account period01 period02 period03 period04 accoutPeriod
1111 null null null null null
1112 782 null null null 01
1113 null null null 345 04
1114 765 882 67 321 04

select AccountPeriod = (case when Period04 is not null then '04'

when Period03 is not null then '03'

when Period02 is not null then '02'

when Period01 is not null then '01'

else null

end);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

sqlbaby2 (10/26/2015)
Hi
I've data as below
account period01 period02 period03 period04
1111 null null null null
1112 782 null null null
1113 null null null 345
1114 765 882 67 321
what i want to achieve is to get values from period1 till period04 and used the lasted value to code the value of accoutperiod,
if value is from period1 then code it as 01, period2 as 02, period03 as 03 and period04 as 04. so the output should be like this
account period01 period02 period03 period04 accoutPeriod
1111 null null null null null
1112 782 null null null 01
1113 null null null 345 04
1114 765 882 67 321 04

You've told us what you want to do and got an answer that works but, if you told us why you want to do this, there could be an alternative you your what that you haven't considered. For example, what are you going to use the new AccountPeriod column for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)