Mauro Cazabonnet
asked on
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].[tblHost Alerts] 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
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
LEFT JOIN (SELECT DISTINCT [DataCenter], [AlertType] FROM [Reporting].[dbo].[tblHost
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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].[tblHost Alerts] 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
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
LEFT JOIN (SELECT DISTINCT [DataCenter], [AlertType] FROM [Reporting].[dbo].[tblHost
WHERE tgh.[ENVIRONMENT] NOT LIKE '%DEV%' AND tgh.[REGION] = 'AMRS' AND tgh.[ENVIRONMENT] NOT IN (SELECT [VCENTER] FROM @AMRSVCentersIDX)
) x
WHERE x.rn = 1
ASKER
If an alert and warning exists for both I would only want to show the alert