troubleshooting Question

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

Avatar of need_solution
need_solution asked on
Microsoft SQL Server
10 Comments2 Solutions139 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Koen Van Wielink
Business Intelligence Specialist

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros