How to Loop Through Specific rows in T-SQL

Question

I have a table with 2 columns - server_name, status_name. I'm trying to target specific server/status combinations

Example:

 server_name     status_name
 server01        decommissioned
 server01        archive
 server02        decommissioned
 server02        production
 server03        decommissioned
 server03        test
 server04        decommissioned
 server04        archive

I want to return only the server/status combination of decommissioned/archive

Results:

 server_name     status_name
 server01        decommissioned
 server01        archive
 server04        decommissioned
 server04        archive

I've tried using a cursor but because at least one set of status_name is decommissioned, it returns all rows.

Is there a way I can retrieve only the pair status_name I'm looking for?

CREATE TABLE TEST (
SERVER_NAME nvarchar(50),
STATUS_NAME nvarchar(50)
)

INSERT INTO TEST (SERVER_NAME, STATUS_NAME)
VALUES(N'SERVER01', N'DECOMMISSIONED'), (N'SERVER01', N'ARCHIVE'),
 (N'SERVER02', N'DECOMMISSIONED'), (N'SERVER02', N'PRODUCTION'), 
 (N'SERVER03', N'DECOMMISSIONED')
 ,(N'SERVER03', N'ARCHIVE'), (N'SERVER04', N'DECOMMISSIONED'),  
 (N'SERVER04', N'TEST')

 DECLARE DECOM_Cursor CURSOR FOR
 SELECT [SERVER_NAME],
 [STATUS_NAME]

 FROM TEST
 WHERE [STATUSNAME] IN ('DECOMMISSIONED', 'ARCHIVE');
 OPEN DECOM_Cursor;

 FETCH NEXT FROM DECOM_Cursor;

 WHILE @@FETCH_STATUS = 0

 BEGIN
    FETCH NEXT FROM DECOM_Cursor;
 END;
 CLOSE DECOM_Cursor;
 DEALLOCATE DECOM_Cursor;
 GO

M.Ali · Accepted Answer · 2015-05-06 21:15:48Z

1

SELECT *
FROM TableName t
WHERE EXISTS (SELECT 1 
              FROM TableName 
              WHERE t.Server_name = Server_Name 
               AND Status_name = 'decommissioned')
 AND EXISTS (SELECT 1 
              FROM TableName 
              WHERE t.Server_name = Server_Name 
               AND Status_name = 'archive')

answered May 6, 2015 at 21:15

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ronniej962 Over a year ago

Thank you M.Ali!! That solved my issue. Also thank you for the quick response. I really appreciate it.

Stephan · Accepted Answer · 2015-05-07 05:48:52Z

I know this question has already been answered, but my answer is a little less complicated than M.Ali's. I don't have a subquery for each value. Secondly, because it doesn't have to access the table twice(once for each subquery), it seems to perform better than M.Ali's solution. I was getting less execution time and less logical reads when I ran my query versus his. Of course, it may different for your full data set, but I would recommend trying my query out.

SELECT *
FROM Test A
CROSS APPLY (
                SELECT COUNT(STATUS_NAME)
                FROM Test B
                WHERE A.SERVER_NAME = B.SERVER_NAME
                AND B.STATUS_NAME IN ('DECOMMISSIONED', 'ARCHIVE')
                GROUP BY B.SERVER_NAME
            ) CA(match_cnt)
WHERE match_cnt = 2

Collectives™ on Stack Overflow

How to Loop Through Specific rows in T-SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related