SQL Where not exists in same table

I'm trying to insert into table with the not in condition
but doesn't seem to work

Here is the data

VCENTER      ALERT
A-PROD      Alert
A-PROD      Warning
A-UAT      NULL
B-UAT      NULL
C-PROD      Warning
C-UAT      NULL
D-PROD      Alert
E-PROD      NULL

Here is my sql

DECLARE @AMRSVCentersIDX TABLE
            (            
                  [VCENTER] nvarchar(200),
                  [ALERT] varchar(20),      
                  [ID] int            
            )


            

            INSERT INTO @AMRSVCentersIDX
            SELECT DISTINCT tgh.[ENVIRONMENT], RIGHT(tha.AlertType, CHARINDEX(' ', REVERSE(tha.AlertType)) - 1) as [ALERT],
            ROW_NUMBER() OVER (Order by tgh.[ENVIRONMENT]) AS [ID]
            FROM [Reporting].[dbo].[tblGEN2_HOSTS] tgh WITH (NOLOCK)
            LEFT JOIN (SELECT DISTINCT [DataCenter], [AlertType] FROM [Reporting].[dbo].[tblHostAlerts] WITH (NOLOCK) WHERE [Duration] >= 10) tha ON tgh.[ENVIRONMENT] = tha.[DataCenter]
            WHERE tgh.[ENVIRONMENT] NOT LIKE '%DEV%' AND tgh.[REGION] = 'AMRS'
            AND tgh.[ENVIRONMENT] NOT IN (SELECT '''' + [VCENTER] + '''' as [ENVIRONMENT] FROM @AMRSVCentersIDX)

What I expect to be inserted into the temp table is

VCENTER      ALERT      ID
A-PROD      Alert      1
A-UAT      NULL      2
B-UAT      NULL      3
C-PROD      Warning      4
C-UAT      NULL      5
D-PROD      Alert      6
E-PROD      NULL      7

Any help would be most appreciated

Regards,
M
LVL 4
Mauro CazabonnetSenior Software EngineerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do it:


  INSERT INTO @AMRSVCentersIDX
    select ENVIRONMENT, ALERT, ID
     from (
            SELECT tgh.[ENVIRONMENT], RIGHT(tha.AlertType, CHARINDEX(' ', REVERSE(tha.AlertType)) - 1) as [ALERT]
,            ROW_NUMBER() OVER (partition by tgh.[ENVIRONMENT]   )   AS [RN ]
 ,           ROW_NUMBER() OVER (order tgh.[ENVIRONMENT]   )   AS [ID]
          FROM [Reporting].[dbo].[tblGEN2_HOSTS] tgh WITH (NOLOCK)
            LEFT JOIN (SELECT DISTINCT [DataCenter], [AlertType] FROM [Reporting].[dbo].[tblHostAlerts] WITH (NOLOCK) WHERE [Duration] >= 10) tha ON tgh.[ENVIRONMENT] = tha.[DataCenter]
            WHERE tgh.[ENVIRONMENT] NOT LIKE '%DEV%' AND tgh.[REGION] = 'AMRS'
            AND tgh.[ENVIRONMENT] NOT IN (SELECT '''' + [VCENTER] + '''' as [ENVIRONMENT] FROM @AMRSVCentersIDX)
  ) x
where x.rn = 1
0
 
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Note:
If an alert and warning exists for both I would only want to show the alert
0
 
Mauro CazabonnetSenior Software EngineerAuthor Commented:
Awesome thx!!!!!!!

needed order by with partition by

Final result

INSERT INTO @AMRSVCentersIDX
            SELECT ENVIRONMENT, ALERT, ROW_NUMBER() OVER (Order by [ENVIRONMENT]) [ID]
            FROM (
            SELECT tgh.[ENVIRONMENT], RIGHT(tha.AlertType, CHARINDEX(' ', REVERSE(tha.AlertType)) - 1) as [ALERT],
                   Row_number()
                   OVER (
                        partition by tgh.[ENVIRONMENT]
                        ORDER BY tha.[DATACENTER]) [RN]
             FROM [Reporting].[dbo].[tblGEN2_HOSTS] tgh WITH (NOLOCK)
             LEFT JOIN (SELECT DISTINCT [DataCenter], [AlertType] FROM [Reporting].[dbo].[tblHostAlerts] WITH (NOLOCK) WHERE [Duration] >= 10) tha ON tgh.[ENVIRONMENT] = tha.[DataCenter]
             WHERE tgh.[ENVIRONMENT] NOT LIKE '%DEV%' AND tgh.[REGION] = 'AMRS' AND tgh.[ENVIRONMENT] NOT IN (SELECT [VCENTER] FROM @AMRSVCentersIDX)

                   ) x
                   WHERE x.rn = 1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.