Need help on SQL query on value check

Question

1.00/5 (1 vote)

See more:

Need help on SQL query on checking whether comp available or not available from the below table. If level column contains 1 and if any below row is having level value as 2 then make as comp available or not available.

ABC	      Level	 Check for Comp
1234Z2DD	0	Component Not available
1234Z2DD	1	Component Available
1234Z2DD	2	
1234Z2DD	2	
1234Z2DD	3	
1234Z2DD	3	
1234Z2DD	3	
1234Z2DD	3	
1234Z2DD	2	
1234Z2DD	3	
1234Z2DD	2	
1234Z2DD	3	
1234Z2DD	2	
1234Z2DD	3	
1234Z2DD	2	
1234Z2DD	3	
1234Z2DD	2	
1234Z2DD	3	
1234Z2DD	1	Component Available
1234Z2DD	2	
1234Z2DD	3	
1234Z2DD	2	
1234Z2DD	3	
1234Z2DD	2	
1234Z2DD	2	
1234Z2DD	3	
1234Z2DD	1	Component Not available
1234Z2DD	1	Component Available
1234Z2DD	2

What I have tried:

tried with max and group by but not getting correct results

Posted 16-Mar-21 21:45pm

Member 14936854

Updated 17-Mar-21 3:07am

Add a Solution

Comments

Maciej Los 17-Mar-21 3:56am

What you mean by saying: "below row"?!?

Member 14936854 17-Mar-21 4:04am

then next row value of level 1

Maciej Los 17-Mar-21 4:05am

Is there any primary key?

Member 14936854 17-Mar-21 4:05am

if it is 2 then update as comp available for level 1

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-16T22:14:00

I have no idea what "below row" means...

Assuming that you want to find next or previous values in a table, you can use: LEAD[^] or LAG[^] function.

See:

SQL

CREATE TABLE Components
(
  ABC varchar(30),
  [Level] int
);

INSERT INTO Components(ABC, [Level])
VALUES('1234Z2DD', 0), ('1234Z2DD',1),
('1234Z2DD',2), ('1234Z2DD',2),
('1234Z2DD',3), ('1234Z2DD',3),
('1234Z2DD',3), ('1234Z2DD',3),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',1), ('1234Z2DD',2),
('1234Z2DD',3), ('1234Z2DD',2),
('1234Z2DD',3), ('1234Z2DD',2),
('1234Z2DD',2), ('1234Z2DD',3),
('1234Z2DD',1), ('1234Z2DD',1),
('1234Z2DD',2);

SELECT ABC, [Level], Avail = CASE
  WHEN [Level] = 0 THEN 'NA' 
  WHEN [Level] = 1 AND LEAD([Level]) OVER(ORDER BY ABC) > 1 THEN 'Available' 
  ELSE '' END 
FROM COmponents;

SQL Server 2019 | db<>fiddle[^]