Click here to Skip to main content
14,870,616 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How to get validation as yes if 'Valid Lvl 1' falls in either of the columns as shown below.

ABC	      [Check at LVL 1][Check_at_LVL 2] validation
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes
2342AV#AC8	Invalid Lvl 1	Valid Lvl 1	    yes
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes
2342AV#AC8	Invalid Lvl 1	Invalid Lvl 1	yes


What I have tried:

i tried with case when [Check at LVL 1]='Valid Lvl 1' or [Check_at_LVL 2]='Valid Lvl 1' then 'Yes' else 'No', but getting Yes to row only where valid lvl 1 other rows are becoming no
Posted
Updated 22-Mar-21 0:19am
v2
Comments
Maciej Los 22-Mar-21 6:13am
   
What???

1 solution

Well.. This:
[Check at LVL 1]='Valid Lvl 1' or [Check_at_LVL 2]='Valid Lvl 1'

is "translated" to: [Boolean] Or [Boolean]

Take a look at below logic operations:
True and True = True
True and False = False
True or True = True
True or False = True

Do you know what to change, now?
   
v3
Comments
Member 14936854 22-Mar-21 6:42am
   
Yes query
Member 14936854 22-Mar-21 6:44am
   
update d set d.[validation]=case when d3.Output >0 then 'Yes' else 'No' end
from #tempx1 d
outer apply ( select top 1 count(*) as Output
from #tempx1 d2
where d2.abc= d.abc
and d2.id=d.id
and [Check at LVL 1]='Valid Lvl 1'
or [Check_at_LVL 2]='Valid Lvl 1'
group by d2.id,d2.abc
order by d2.Id desc) d3
Maciej Los 22-Mar-21 6:49am
   
You've got you wrote: update d set d.[validation]=case when d3.Output >0 then 'Yes' else 'No' end
Member 14936854 22-Mar-21 6:44am
   
tried this but not getting correct result
Member 14936854 22-Mar-21 6:49am
   
if any one of the column is having Valid Lvl 1 then update validation as Yes to a set

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900