Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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

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
srionline2k6
Asked:
srionline2k6
  • 2
2 Solutions
 
Ephraim WangoyaCommented:
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
 
Ephraim WangoyaCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
srionline2k6Author Commented:
Thanks for the answers
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now