Metrics Composite Rule Extraction

Description

Extracts the Composite Rule and associated configuration for specific metrics by Program Item.


Query :

 

SELECT
    P.ProgramId,
    P.Name                                                        AS ProgramName,
    PRI.ProgramItemId,
    PRI.Name                                                      AS ProgramItemName,
    x.MetricsID,
    x.MetricsSequence,
    x.MetricsName,
    x.MetricsDescription,
    x.CompositeRule,

    -- ── ValidityRange → ProductGroupDefinition ──────────────────────────────
    PGD.Name                                                      AS PGDName,

    -- ── ValidityRange → SkuGroupDefinition → SKUIDs ─────────────────────────
    JSON_VALUE(sku.[value], '$.SKUIDs.label')                     AS SKU_Label,

    -- ── Targets → TargetDefinition ──────────────────────────────────────────
    JSON_VALUE(tgt.[value], '$.TargetDefinition.Select')          AS TargetDefinition_Select,

    -- ── Points → PointDefinition ────────────────────────────────────────────
    JSON_VALUE(pt.[value], '$.PointDefinition.Select')            AS PointDefinition_Select,

    -- ── Expressions → Expression ────────────────────────────────────────────
    exp.Expression,
    exp.ActionExpression

FROM PROGRAM P

JOIN PROGRAMITEM PRI
    ON PRI.ProgramId = P.ProgramId

JOIN programportfolio PP
    ON PP.ProgramItemId = PRI.ProgramItemId

-- ── Level 1: root keys of the JSON ───────────────────────────────────────────
CROSS APPLY OPENJSON(PP.ProgramPortfolioMappingData) j

-- ── Level 2: each element of the array ────────────────────────────────────────
CROSS APPLY OPENJSON(j.[value]) d

-- ── Fields of the Metrics node ─────────────────────────────────────────────────
CROSS APPLY (
    SELECT
        JSON_VALUE(d.[value], '$.MetricsID')                         AS MetricsID,
        JSON_VALUE(d.[value], '$.MetricsSequence')                   AS MetricsSequence,
        JSON_VALUE(d.[value], '$.MetricsName')                       AS MetricsName,
        JSON_VALUE(d.[value], '$.MetricsDescription')                AS MetricsDescription,
        JSON_VALUE(d.[value], '$.MetricsAreaName')                   AS MetricsAreaName,
        JSON_VALUE(d.[value], '$.statusId')                          AS MetricStatusId,
        JSON_VALUE(d.[value], '$.Status')                            AS MetricStatus,
        JSON_VALUE(
            d.[value],
            '$.MetricsDefinition.CompositeRule'
        )                                                           AS CompositeRule
) x

-- ── Targets: finds the node whose key is Targets and whose
--    Related_metrics matches the current MetricsID ────────────────────────
OUTER APPLY (
    SELECT TOP 1
        d2.[value]
    FROM OPENJSON(PP.ProgramPortfolioMappingData) j2
    CROSS APPLY OPENJSON(j2.[value]) d2
    WHERE j2.[key] = 'Targets'
      AND JSON_VALUE(d2.[value], '$.Related_metrics') = x.MetricsID
) tgt

-- ── Points ──────────────────────────────────────────────────────────────────
OUTER APPLY (
    SELECT TOP 1
        d3.[value]
    FROM OPENJSON(PP.ProgramPortfolioMappingData) j3
    CROSS APPLY OPENJSON(j3.[value]) d3
    WHERE j3.[key] = 'Points'
      AND JSON_VALUE(d3.[value], '$.Related_metrics') = x.MetricsID
) pt

-- ── Calculations → Levels → Expressions ────────────────────────────────────
OUTER APPLY (
    SELECT TOP 1
        JSON_VALUE(
            exp_item.[value],
            '$.Expression'
        )                                                           AS Expression,

        CAST(
            JSON_VALUE(
                exp_item.[value],
                '$.ActionExpression'
            ) AS NVARCHAR(MAX)
        )                                                           AS ActionExpression

    FROM OPENJSON(PP.ProgramPortfolioMappingData) jc
    CROSS APPLY OPENJSON(jc.[value], '$.Levels') lvl
    CROSS APPLY OPENJSON(lvl.[value], '$.Expressions') exp_item

    WHERE jc.[key] = 'Calculations'
      AND JSON_VALUE(
            exp_item.[value],
            '$.Related_metrics'
          ) = x.MetricsID
) exp

-- ── ValidityRange → ProductGroupDefinition ─────────────────────────────────
OUTER APPLY (
    SELECT TOP 1
        pgd_item.[value]
    FROM OPENJSON(d.[value], '$.ValidityRange') vr
    CROSS APPLY OPENJSON(
        vr.[value],
        '$.ProductGroupDefinition'
    ) pgd_item
) pgd_oa

-- ── ValidityRange → SkuGroupDefinition ─────────────────────────────────────
OUTER APPLY (
    SELECT TOP 1
        sku_item.[value]
    FROM OPENJSON(d.[value], '$.ValidityRange') vr
    CROSS APPLY OPENJSON(
        vr.[value],
        '$.SkuGroupDefinition'
    ) sku_item
) sku

LEFT JOIN ProductGroupDefinition PGD
    ON PGD.ProductGroupDefinitionId =
       JSON_VALUE(pgd_oa.[value], '$.PGDIDs')

WHERE
    P.IsDeleted = 0
    AND PRI.IsDeleted = 0
    AND P.ClientId = 408
    AND PRI.ProgramItemId IN (select programItemid from program p join programitem PRI on p.ProgramId = Pri.ProgramId where P.IsDeleted = 0 and Pri.IsDeleted = 0 and P.Name like '2026%TT%ICE%' 
	AND PRI.StatusId = 1 AND P.CreatedOn <= 2026-06-25="2026-06-25" --="--" and="AND" is="IS" not="NOT" null="NULL" like="LIKE" order="ORDER" by="BY">