Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-20
4
Medium Priority
?
356 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 2000 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 2000 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 59

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

688 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