Solved

SQL Where not exists in same table

Posted on 2016-09-07
3
67 Views
Last Modified: 2016-09-07
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
0
Comment
Question by:mcazabonnet
  • 2
3 Comments
 
LVL 4

Author Comment

by:mcazabonnet
ID: 41787677
Note:
If an alert and warning exists for both I would only want to show the alert
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41787712
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
 
LVL 4

Author Closing Comment

by:mcazabonnet
ID: 41787935
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question