Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Where not exists in same table

Posted on 2016-09-07
3
Medium Priority
?
394 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:Mauro Cazabonnet
  • 2
3 Comments
 
LVL 4

Author Comment

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

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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:Mauro Cazabonnet
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

963 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