We help IT Professionals succeed at work.

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

need_solution
on
95 Views
Last Modified: 2016-01-25
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
Comment
Watch Question

Russ SuterSenior Software Developer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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 CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
You still haven't defined how you determine whether an estatus is active or not.  Also, what version of SQL are you working with?
Koen Van WielinkBusiness Intelligence Specialist
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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.

Author

Commented:
And yes, I am using SQL Server 2012.
Business Intelligence Specialist
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
need_solution, do you still need a solution?

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.