Solved

How to write a case  statement in sql server for the status change

Posted on 2011-09-20
4
354 Views
Last Modified: 2012-06-27
Hi Please help me in writing the query for the following:

I have a scenario where i need to write the query for status change.

we have  2 columns one is Id column which populates the parent and child id c1, c2, c3, c4 values and Status with Yes or NO. If there is any change in parent Id value, then it populates with the  5 alpha numeric value like '1ad2h' in a row and if there is a change in child value ' then it populates with the value which contains alphabet 'v' in it in one of the 5 alpha numeric values  like for eg ' 2V3w3'. We need to identify the change in the child values and if there is any change as  the child value is identified by 'v' alphabet I guess in case statement we have to use wild characters to get the change value then the status column should be updated with Yes or Y.
Similiarly if Parent value and child values does not have any v value in it then it should return N or no

     P1                              c1                 c2                     c3                   status
    'a2bd3'                     'a2cdv'             2ah88'            'dk23k'               'Y'
     'd2ks2'                      'ekdkd'           'dkks2'           'd2kdk'                'N'

Like wise there are different elements like java , cobol etc each have different parent values and within the parent values we have 3 Child values. and the scenario will be the same as stated above....

Please gimme some idea how to write the query for this scenario...





0
Comment
Question by:srionline2k6
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 500 total points
ID: 36570763
try
select P1, c1, c2, c3, case when charindex('v', StatusStr, 1) > 0 then 'Y' else 'N' end [Status]
from
(
	select P1, c1, c2, c3, 
		ISNULL(P1, '') + ISNULL(c1, '') + ISNULL(c2, '') + ISNULL(c3, '') [StatusStr]
	from table1
) A

Open in new window

0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 36570765
if you prefer cte then


;with cte as
(
        select P1, c1, c2, c3, 
		ISNULL(P1, '') + ISNULL(c1, '') + ISNULL(c2, '') + ISNULL(c3, '') [StatusStr]
	from table1
)

select P1, c1, c2, c3, case when charindex('v', StatusStr, 1) > 0 then 'Y' else 'N' end [Status]
from cte

Open in new window

0
 
LVL 56

Expert Comment

by:HainKurt
ID: 36570884
read many times, but could not understand :)
are these two lines from your table? if yes, it already has status column... do you want select or update query?
0
 

Author Closing Comment

by:srionline2k6
ID: 36571439
Thanks for the answers
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question