Metric status based on Program Items

DECLARE @TargetPosition AS NVARCHAR(10)

DECLARE @TmpProgramItemId TABLE(
   Id int identity(1,1) not null primary key
  ,ProgramItemId varchar(100) not null
)

--DECLARE @TmpMetricPosition TABLE(
--   Id int identity(1,1) not null primary key
--  ,MetricPosition varchar(100) not null
--  ,ProgramItemId varchar(100)
--)

DECLARE @TmpResults TABLE(
   Id int identity(1,1) not null primary key
  ,ClientId varchar(100)
  ,ProgramItemId varchar(100)
  ,MetricsID varchar(100)
  ,statusId varchar(100) 
  ,Status varchar(100) 
)

INSERT INTO @TmpProgramItemId(ProgramItemId) VALUES
--(33580),(33581),(33582),(33583),(33584),(33585),(33586),(33587),(33588),(33589),
--(33590),(33591),(33592),(33593),(33594),(33595),(33596),(33597),(33598),(33599)
--(33600),(33601),(33602),(33603),(33604),(33605),(33606),(33607),(33608),(33609),
--(33610),(33611),(33612),(33613),(33614),(33615),(33616),(33617),(33618),(33619),
--(33620),(33621),(33622),(33623),(33624),(33625),(33626),(33627),(33628),(33629)
--(33630),(34665),(34666),(34667),(34668),(34669),(34670),(34671),(34674),(34675),
--(34678),(34679),(34680),(34681),(34682),(34685),(34686),(34690),(34691),(34692),
--(34693),(34696),(34697),(34698),(34699),(34700),(34703),(34704),(34705),(34706)
(34707),(34708),(34709),(34713),(34714),(34715),(34716),(34717),(34718),(34719),(34720)

DECLARE @nRecords INT -- Stores # records from the select statement.
    SET @nRecords=(SELECT COUNT(*) FROM @TmpProgramItemId)
DECLARE @nWhile INT --Stores # of times loop will happen
SET @nWhile=1

WHILE(@nRecords>0 AND @nWhile<=@nRecords)
BEGIN

CREATE TABLE #TmpMetricPosition(
   Id int identity(1,1) not null primary key
  ,MetricPosition varchar(100) not null
  ,ProgramItemId varchar(100)
)

DECLARE @ProgramItemId AS NVARCHAR(10)
SET @ProgramItemId=(SELECT ProgramItemId FROM @TmpProgramItemId WHERE Id=@nWhile) --Set currect program item id value

;WITH JSONRoot AS ( 
    SELECT 
        CAST(hierarchyid::GetRoot().ToString() + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NVARCHAR(4000)) + '/' AS NVARCHAR(4000)) as [HierarchyId], 
        [key],
        [value],
        CAST([type] AS INT) AS [type] 
    FROM 
        (SELECT ProgramPortfolioMappingData ,programitemid
         FROM programportfolio 
        WHERE programitemid IN (@ProgramItemId) )x
        CROSS APPLY OPENJSON(ProgramPortfolioMappingData,'$') 
    UNION ALL 
    SELECT 
        CAST(JSONRoot.[HierarchyId] + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NVARCHAR(4000)) + '/' AS NVARCHAR(4000)), 
        CASE WHEN JSONRoot.[type] = 4 THEN JSONRoot.[key]+'['+t.[key]+']' ELSE t.[key] END,
        t.[value],
        CAST(t.[type] AS INT) 
    FROM 
        JSONRoot 
        CROSS APPLY OPENJSON(JSONRoot.[value],'$') t 
    WHERE 
        JSONRoot.[type] > 3 /* Only parse complex data types */
),
FinalTable AS (
SELECT 
    CAST([HierarchyId] AS HierarchyId) AS [Hierarchy],
  [key],
  [value],
    [type]
FROM 
    JSONRoot x
  )
INSERT INTO #TmpMetricPosition (MetricPosition,ProgramItemId)
SELECT SUBSTRING([key],CHARINDEX('[',[key])+1,CHARINDEX(']',[key])-CHARINDEX('[',[key])-1),@ProgramItemId
  FROM FinalTable
WHERE [key] LIKE '%Metrics\[%' ESCAPE '\'

    DECLARE @nRecordsMetrics INT -- Stores # records from the select statement.
      SET @nRecordsMetrics=(SELECT COUNT(*) FROM #TmpMetricPosition)
    DECLARE @nWhileMetrics INT --Stores # of times loop will happen
    SET @nWhileMetrics= 1

    WHILE(@nRecordsMetrics>0 AND @nWhileMetrics<=@nRecordsMetrics)
    BEGIN
    DECLARE @MetricPosition AS NVARCHAR(10)
    SET @MetricPosition=(SELECT MetricPosition FROM #TmpMetricPosition WHERE Id=@nWhileMetrics) --Set currect program item id value

    INSERT INTO @TmpResults (ClientId,ProgramItemId,MetricsID,statusId,Status)
    SELECT JSON_VALUE(ProgramPortfolioMappingData,'$.ClientId') AS ClientId,
         JSON_VALUE(ProgramPortfolioMappingData,'$.ProgramItemId') AS ProgramItemId,
         JSON_VALUE(ProgramPortfolioMappingData,'$.Metrics['+@MetricPosition+'].MetricsID') AS MetricsID,
		 JSON_VALUE(ProgramPortfolioMappingData,'$.Metrics['+@MetricPosition+'].statusId') AS statusId,
		 JSON_VALUE(ProgramPortfolioMappingData,'$.Metrics['+@MetricPosition+'].Status') AS [Status]
      FROM programportfolio 
     WHERE programitemid = (@ProgramItemId) 
      PRINT 'INSERTANDO METRICA ' +CONVERT(varchar(10), @nWhileMetrics)+' DE '+CONVERT(varchar(10), @nRecordsMetrics)
      SET @nWhileMetrics=@nWhileMetrics+1

    END

    PRINT 'PROGRAM ITEM INSERTADO. ' +CONVERT(varchar(10), @nWhile)+' DE '+CONVERT(varchar(10), @nRecords)

SET @nWhile=@nWhile+1
DROP TABLE #TmpMetricPosition
END

SELECT * FROM @TmpResults

--------------------------------------------------