Need help on SQL on count

Question

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 22-Mar-21 0:10am

Member 14936854

Updated 22-Mar-21 0:19am

v2

Add a Solution

Comments

Maciej Los 22-Mar-21 6:13am

What???

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2021-03-22T00:19:00

Solution 1

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?

Posted 22-Mar-21 0:19am

Maciej Los

Updated 22-Mar-21 0:26am

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