Link to home
Start Free TrialLog in
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
Avatar of Russ Suter
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.
Avatar of need_solution

ASKER

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,
You still haven't defined how you determine whether an estatus is active or not.  Also, what version of SQL are you working with?
SOLUTION
Avatar of Koen Van Wielink
Koen Van Wielink
Flag of Netherlands 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
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.
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.
And yes, I am using SQL Server 2012.
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
need_solution, do you still need a solution?
Sorry, was away for last few days. I did accept Koen Van Wielink's solution.