Solved

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

Posted on 2011-09-20
4
350 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
  • 2
4 Comments
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya 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:
ewangoya 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 51

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ServiceCenter IR Query Expressions 1 40
SQL Server: Unable to remove duplicate sets in Header/Detail 6 27
SQL Query assistance 16 36
sql server query 12 26
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 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