SQL server query -- multiple row value check for the same request number-CodePudding

please help me to get solve this below scenario. I am new to the SQL server management

Table

Request	Obj
123	483
123	456
456	456

I have a table like this in the server

I need to get the result as below

Request	result
123	new
123	new
456	WIP

The logic is If the request have both 483 and 456 object then this will be "new" If the request have only 456 and not have 483 then it will be WIP

Can someone please help me to get the code for those requirements

Thank you for your time and help

Code [Resolved]

SELECT T.Request,(CASE WHEN (D.cn = 2 AND obj = 483 OR obj = 456) OR (D.cn = 1 AND obj = 456) THEN 'NEW'
                       WHEN (D.cn = 1 AND obj = 483) THEN 'WIP'
                       WHEN (D.cn = 2 AND obj = 256 OR obj = 283) OR (D.cn = 1 AND obj = 283) THEN 'Cancel'
                       WHEN (D.cn = 1 AND obj = 256) THEN 'Cancel - WIP'
                       ELSE 'NA' END) AS [Result]
FROM table_name T
LEFT JOIN
(
SELECT Request
,COUNT(DISTINCT CASE WHEN obj IN (483,456,283,256) THEN obj END) AS cn
FROM table_name
GROUP BY Request
) D
ON T.Request = D.Request
ORDER BY T.Request

CodePudding user response：

Supposing that you may have more than two rows for a request value, you may use conditional count on the distinct values of 'Obj' to check if a request has the two values (483, 456), then join this to your table to get the desired output.

SELECT T.Request,
           CASE 
             WHEN D.cn = 2
             THEN 'New'
             ELSE 'WIP'
           END AS result
FROM table_name T
JOIN
(
  SELECT Request, COUNT(DISTINCT CASE WHEN Obj IN (483, 456) THEN Obj END) AS cn
  FROM table_name
  GROUP BY Request
) D
ON T.Request = D.Request
ORDER BY T.Request

See a demo.

CodePudding user response：

SELECT T.Request
,(CASE WHEN (D.cn = 2 AND obj = 483 OR obj = 456) OR (D.cn = 1 AND obj = 456) THEN 'NEW'
WHEN (D.cn = 1 AND obj = 483) THEN 'WIP'
WHEN (D.cn = 2 AND obj = 256 OR obj = 283) OR (D.cn = 1 AND obj = 283) THEN 'Cancel'
WHEN (D.cn = 1 AND obj = 256) THEN 'Cancel - WIP'
ELSE 'NA' END) AS [Result]
FROM table_name T
LEFT JOIN
(
SELECT Request
,COUNT(DISTINCT CASE WHEN obj IN (483,456,283,256) THEN obj END) AS cn
FROM table_name
GROUP BY Request
) D
ON T.Request = D.Request
ORDER BY T.Request