Avatar of need_solution
need_solution asked on

need to convert all the values in the status column to another status based on a condition

i have a resultset in sql with mth, employee no., status and order.
mth               emp#    status         order
1/1/2012      12345     AA              2
2/1/2012       12345     AA              2
3/1/2012       12345     AA              2
.                         .               .                .
.                         .               .                .
.                         .               .                .  
1/1/2014      12345      BB             4
2/1/2014       12345     BB             4
.                         .               .                .
.                         .               .                .
6/1/2015      12345       CC             5
.                         .               .                .
.                         .               .                .
12/1/2015    12345     DD              6

We have a list of status codes which we know are valid ones and others are invalid, so, here, I know AA is a valid code, whenever there is AA in the status column, i need to convert all the invalid statuses in that column AA, till i see a next valid status in that column. So this is what I need,  AA is a valid status, BB and CC are not. And then DD is the next valid status.

mth               emp#    status         order      status1
1/1/2012      12345     AA              2                AA
2/1/2012       12345     AA              2               AA
3/1/2012       12345     AA              2               AA
.                         .               .                .
.                         .               .                .
.                         .               .                .  
1/1/2014      12345     BB        4                 AA
2/1/2014       12345    BB         4               AA
.                         .               .                .
.                         .               .                .
6/1/2015      12345     CC             5                 AA
.                         .               .                .
.                         .               .                .
12/1/2015    12345     DD     6                 DD
Microsoft SQL Server

Avatar of undefined
Last Comment
need_solution

8/22/2022 - Mon
Russ Suter

I'm not sure where the value of status1 is coming from but assuming it's all in the same table you could do it this way.
UPDATE
  [Table_1]
SET
  [status] = [status1]
WHERE
  [status] <> [status1]

Open in new window

If you are updating values in one table based on values in another table then you need to do an update with join.
http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql
In your example above it's not clear if that's what you're doing and if it is it's unclear what you're joining on.
ASKER
need_solution

I don;t want to update any table, this is in a sql script. May be i did not explain it properly.

I am writing my code in a temp table, i need to create a estatus1 field based on the following logic:
some of the status codes from the estatus field are active and some are inactive. As long as I see the active estatus i should display as it is in the estatus1 field. If there is an inactive code, i should replace that with the prev active estatus and display in the estatus1 field, till i see the next active status. This is how I want

current result set:

mth                     emp#          estatus               order            
1/1/2012                  12345           AA                    2            
2/1/2012                   12345           AA                    2            
3/1/2012                   12345           AA                    2            
.                         .             .                      .
.                         .             .                      .
.                         .             .                      .  
1/1/2014                  12345           BB                       4            
2/1/2014                   12345           BB                       4            
.                         .              .                      .
.                         .              .                      .
6/1/2015                  12345           CC                        5            
.                         .              .                      .
.                         .              .                      .
12/1/2015                12345           DD                         6            
1/1/2016            12345           EE                         7            

Here AA and DD are the active codes and BB, CC and EE are inactive codes. Unfortunately, there is no Active/Inactive flag which can be used.


this is what I need:

mth                     emp#          estatus               order            estatus1
1/1/2012                  12345           AA                    2            AA
2/1/2012                   12345           AA                    2            AA
3/1/2012                   12345           AA                    2            AA
.                         .             .                      .
.                         .             .                      .
.                         .             .                      .  
1/1/2014                  12345           BB                       4            AA
2/1/2014                   12345           BB                       4            AA
.                         .              .                      .
.                         .              .                      .
6/1/2015                  12345           CC                        5            AA
.                         .              .                      .
.                         .              .                      .
12/1/2015                12345           DD                         6            DD
1/1/2016            12345           EE                         7            DD

Thanks in advance,
Brian Crowe

You still haven't defined how you determine whether an estatus is active or not.  Also, what version of SQL are you working with?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Koen Van Wielink

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Vitor Montalvão

Without an Active/Inactive field I'm afraid you can't do this.
How if 'BB' or 'CC' become Active in the future? Or if 'AA' or 'DD' become Inactive? There should have a way to keep track on those status.
ASKER
need_solution

these are the pay code statuses, for eg. term/leave etc. these will not change the active/inactive status.
I was thinking on the same lines what Koen has suggested, but little different.
I thought if i create a temp table with all the active statuses in it
select
distinct estatus
into #active
from
table1
where
estatus in ('AA', 'DD',....)

then I'll get a list of only active statuses and with that list and the mth field associate with each status i can create a estatus1 field.
ASKER
need_solution

And yes, I am using SQL Server 2012.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vitor Montalvão

need_solution, do you still need a solution?
ASKER
need_solution

Sorry, was away for last few days. I did accept Koen Van Wielink's solution.