[SOLUTION] 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

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

Note:
If an alert and warning exists for both I would only want to show the alert

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

SQL Where not exists in same table

Who is Participating?

Already a member? Login.