USE XYZ
GO
CREATE PROCEDURE sp_Final_Values
AS
--check and drop only an existing table
IF OBJECT_ID('tempdb.dbo.#Temp_Group_Table', 'U') IS NOT NULL
DROP TABLE #Temp_Group_Table;
IF OBJECT_ID('tempdb.dbo.#TEMP_GC', 'U') IS NOT NULL
DROP TABLE #TEMP_GC;
IF OBJECT_ID('tempdb.dbo.#TEMP_EN', 'U') IS NOT NULL
DROP TABLE #TEMP_EN;
IF OBJECT_ID('tempdb.dbo.#Final_BC', 'U') IS NOT NULL
DROP TABLE #Final_BC;
IF OBJECT_ID('tempdb.dbo.#Final_EN', 'U') IS NOT NULL
DROP TABLE #Final_EN;
IF OBJECT_ID('dbo.Final_BC_EN', 'U') IS NOT NULL
DROP TABLE dbo.Final_BC_EN;
Select g.Group, s.Application, s.Server, s.BC, s.EN into #Temp_Group_Table from dbo.Grouping_Sample g INNER JOIN dbo.Test_Data s on g.Server = s.Server
SELECT DISTINCT Group, BC INTO #TEMP_GC from #Temp_Group_Table where Server in( select Server from #Temp_Group_Table where Group in ( select Group from #Temp_Group_Table))
select Group, BC into #Final_BC from (select Group, BC, ROW_NUMBER() over (partition by Group order by [level] desc) [rn] from #TEMP_GC [t] join
--here we assign numeric values to severities to make it comparable
(values ('L', 1),('M', 2),('C', 3)) as BC([name], [level]) on [t].BC = [BC].name) a where rn = 1
SELECT DISTINCT Group, EN INTO #TEMP_EN from #Temp_Group_Table where Server in( select Server from #Temp_Group_Table where Group in (select Group from #Temp_Group_Table))
select Group, EN into #Final_EN from (select Group, EN, ROW_NUMBER() over (partition by Group order by [level] desc) [rn] from #TEMP_EN [t] join
--here we assign numeric values to severities to make it comparable
(values ('D', 1),('T', 2),('Q', 3),('NP',4),('P',5)) as EN([name], [level]) on [t].EN = [EN].name ) a where rn = 1
select c.Group, c.BC as [Final_BC], e.EN as [Final_EN] into Final_BC_EN from #Final_BC c, #Final_EN e where c.Group = e.Group
select * from dbo.Final_BC_EN
GO
... View more