USE [Risikomodell]
GO
/****** Object:  StoredProcedure [dbo].[pRiskModell_22_ResultsWeightedCalc]    Script Date: 29.08.2025 15:17:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*********************************************************************************************************
-- Author:		Hans-Willi Jackmuth
-- Create date: 28.08.2025
-- Description:	Calculate Riskmodels for Audit - weighted 8*6
*********************************************************************************************************/

ALTER PROCEDURE [dbo].[pRiskModell_22_ResultsWeightedCalc]
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH D AS (
        -- Consider only weighted 8x6 models
        SELECT  R.InputId,
                R.Aggregation,
                R.CountAggregation AS cnt
        FROM dbo.RiskModellResults R
        INNER JOIN dbo.RiskModellInput I
            ON I.InputId = R.InputId
        WHERE I.Weighting = 1
          AND I.Questions = 8
          AND I.Answers   = 6
    ),
    S AS (
        SELECT InputId, SUM(cnt) AS N
        FROM D
        GROUP BY InputId
    ),
    W AS (
        SELECT  D.InputId,
                D.Aggregation,
                D.cnt,
                S.N,
                SUM(D.cnt) OVER (
                    PARTITION BY D.InputId
                    ORDER BY D.Aggregation
                    ROWS UNBOUNDED PRECEDING
                ) AS CumCnt
        FROM D
        JOIN S ON S.InputId = D.InputId
    ),
    Q AS (
        -- Discrete quartiles (no interpolation)
        SELECT  S.InputId,
                (SELECT TOP (1) Aggregation FROM W w1
                 WHERE w1.InputId = S.InputId AND w1.CumCnt >= 0.25*S.N
                 ORDER BY w1.Aggregation) AS Q1,
                (SELECT TOP (1) Aggregation FROM W w2
                 WHERE w2.InputId = S.InputId AND w2.CumCnt >= 0.50*S.N
                 ORDER BY w2.Aggregation) AS Q2,  -- Median
                (SELECT TOP (1) Aggregation FROM W w3
                 WHERE w3.InputId = S.InputId AND w3.CumCnt >= 0.75*S.N
                 ORDER BY w3.Aggregation) AS Q3
        FROM S
    ),
    Buckets AS (
        -- Buckets with <= boundaries 
        SELECT  D.InputId,
                SUM(CASE WHEN D.Aggregation <= Q.Q1 THEN D.cnt ELSE 0 END) AS CounterQ1,
                SUM(CASE WHEN D.Aggregation >  Q.Q1 AND D.Aggregation <= Q.Q2 THEN D.cnt ELSE 0 END) AS CounterQ2,
                SUM(CASE WHEN D.Aggregation >  Q.Q2 AND D.Aggregation <= Q.Q3 THEN D.cnt ELSE 0 END) AS CounterQ3,
                SUM(CASE WHEN D.Aggregation >  Q.Q3 THEN D.cnt ELSE 0 END) AS CounterQ4
        FROM D
        JOIN Q ON Q.InputId = D.InputId
        GROUP BY D.InputId
    ),
    Distincts AS (
        -- Number of different aggregation values (= Distinct Scores)
        SELECT InputId, COUNT(*) AS DistinctAggs
        FROM D
        GROUP BY InputId
    ),
    Final AS (
        SELECT
            S.InputId,
            Q.Q1        AS Quantil1,
            Q.Q2        AS Median,
            Q.Q3        AS Quantil3,
            B.CounterQ1,
            B.CounterQ2,
            B.CounterQ3,
            B.CounterQ4,
            (B.CounterQ2 + B.CounterQ3) AS CounterQ2_Q3,
            S.N         AS ModellCounter,
            G.DistinctAggs AS Aggregation   
        FROM S
        JOIN Q ON Q.InputId = S.InputId
        JOIN Buckets  B ON B.InputId = S.InputId
        JOIN Distincts G ON G.InputId = S.InputId
    )

    UPDATE I
    SET I.Quantil1       = F.Quantil1,
        I.Median         = F.Median,
        I.Quantil3       = F.Quantil3,
        I.CounterQ1      = F.CounterQ1,
        I.CounterQ2      = F.CounterQ2,
        I.CounterQ3      = F.CounterQ3,
        I.CounterQ4      = F.CounterQ4,
        I.CounterQ2_Q3   = F.CounterQ2_Q3,
        I.ModellCounter  = F.ModellCounter,
        I.Aggregation    = F.Aggregation   
    FROM dbo.RiskModellInput I
    JOIN Final F ON F.InputId = I.InputId
    WHERE I.Weighting = 1
      AND I.Questions = 8
      AND I.Answers   = 6;

    PRINT 'Weighted (8x6) statistics calculated and written.';
END
