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

/*********************************************************************************************************
-- Author:		Hans-Willi Jackmuth
-- Create date: 28.08.2025
-- Description:	Create and Calculate Riskmodels for Audit
-- Normal (unweighted): Write back key figures from RiskModelResults
-- Quantiles based on the cumulative frequencies
-- ModelCounter = Sum CountAggregation
-- Aggregation = Number of different aggregation values (Distinct Scores)
-- Weighting only = 0
*********************************************************************************************************/

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

-- #############################################
-- Calculations of the normal cases
-- #############################################	

    ;WITH Dist AS (
        SELECT
            R.InputId,
            R.Aggregation,
            R.CountAggregation,
            SUM(R.CountAggregation) OVER (PARTITION BY R.InputId) AS TotalCount
        FROM dbo.RiskModellResults R
        INNER JOIN dbo.RiskModellInput I
            ON I.InputId = R.InputId
        WHERE I.Weighting = 0
    ),
    Cum AS (
        SELECT
            InputId,
            Aggregation,
            CountAggregation,
            SUM(CountAggregation) OVER (PARTITION BY InputId ORDER BY Aggregation) AS CumCount,
            TotalCount
        FROM Dist
    ),
    Quant AS (
        SELECT
            InputId,
            MIN(CASE WHEN 1.0 * CumCount / NULLIF(TotalCount,0) >= 0.25 THEN Aggregation END) AS Quantil1,
            MIN(CASE WHEN 1.0 * CumCount / NULLIF(TotalCount,0) >= 0.50 THEN Aggregation END) AS Median,
            MIN(CASE WHEN 1.0 * CumCount / NULLIF(TotalCount,0) >= 0.75 THEN Aggregation END) AS Quantil3,
            SUM(CountAggregation) AS ModellCounter
        FROM Cum
        GROUP BY InputId
    ),
    Groups AS (
        -- Number of different aggregation values (= Distinct Scores)
        SELECT InputId, COUNT(*) AS DistinctAggs
        FROM Dist
        GROUP BY InputId
    ),
    Final AS (
        SELECT
            D.InputId,
            Q.Quantil1,
            Q.Median,
            Q.Quantil3,
            Q.ModellCounter,
            SUM(CASE WHEN D.Aggregation <= Q.Quantil1 THEN D.CountAggregation ELSE 0 END) AS CounterQ1,
            SUM(CASE WHEN D.Aggregation >  Q.Quantil1 AND D.Aggregation <= Q.Median   THEN D.CountAggregation ELSE 0 END) AS CounterQ2,
            SUM(CASE WHEN D.Aggregation >  Q.Median   AND D.Aggregation <= Q.Quantil3 THEN D.CountAggregation ELSE 0 END) AS CounterQ3,
            SUM(CASE WHEN D.Aggregation >  Q.Quantil3                              THEN D.CountAggregation ELSE 0 END) AS CounterQ4,
            SUM(CASE WHEN D.Aggregation >= Q.Quantil1 AND D.Aggregation <= Q.Quantil3 THEN D.CountAggregation ELSE 0 END) AS CounterQ2_Q3,
            G.DistinctAggs AS Aggregation 
        FROM Dist D
        JOIN Quant Q  ON Q.InputId = D.InputId
        JOIN Groups G ON G.InputId = D.InputId
        GROUP BY D.InputId, Q.Quantil1, Q.Median, Q.Quantil3, Q.ModellCounter, G.DistinctAggs
    )
    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 = 0;

    PRINT 'Normals (Weighting=0) calculated and written.';
END
