USE [Risikomodell]
GO
/****** Object:  StoredProcedure [dbo].[pRiskModell_11_ResultsNormal]    Script Date: 29.08.2025 15:17:19 ******/
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
*********************************************************************************************************/

ALTER PROCEDURE [dbo].[pRiskModell_11_ResultsNormal]
AS
BEGIN
	SET NOCOUNT ON;	
-- #############################################
-- Go through all active input models
-- #############################################
	DECLARE @InputId int,
			@Questions int,
			@Answers int,
			@SQL nvarchar(max);

    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT InputId, Questions, Answers
        FROM dbo.RiskModellInput
        WHERE Weighting = 0;

    OPEN cur;
    FETCH NEXT FROM cur INTO @InputId, @Questions, @Answers;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- #############################################
        -- Dynamically generate the given number of questions via SQL
        -- #############################################
        DECLARE @CrossApply nvarchar(max) = N'';
        DECLARE @SumExpr nvarchar(max) = N'';
		DECLARE @SumParts nvarchar(max) = N'';
        DECLARE @i int = 1;

        WHILE @i <= @Questions
        BEGIN
            SET @CrossApply += '
				CROSS APPLY (SELECT TOP (' + CAST(@Answers AS nvarchar) + ') 
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
                FROM master..spt_values) A' + CAST(@i AS nvarchar);

			SET @SumParts += CASE WHEN @i > 1 THEN ' + ' ELSE '' END
							  + 'A' + CAST(@i AS nvarchar) + '.n';

            SET @i += 1;
        END

		-- #############################################
        -- Assemble final SQL
        -- #############################################

			SET @SQL = '
			;WITH Combos AS (
				SELECT ' + CAST(@InputId AS nvarchar) + ' AS InputId,
					   CAST( ( ' + @SumParts + ' ) * 1.0 / ' + CAST(@Questions AS nvarchar) + ' AS decimal(10,6) ) AS Aggregation
				FROM (SELECT 1 AS dummy) X
				' + @CrossApply + '
			),
			Agg AS (
				SELECT InputId, Aggregation, COUNT(*) AS CountAggregation
				FROM Combos
				GROUP BY InputId, Aggregation
			)
			INSERT INTO dbo.RiskModellResults (InputId, Aggregation, CountAggregation)
			SELECT InputId, Aggregation, CountAggregation
			FROM Agg;';

        PRINT 'Start calculation for InputId = ' + CAST(@InputId AS nvarchar) 
              + ' (' + CAST(@Questions AS nvarchar) + ' Fragen, ' 
              + CAST(@Answers AS nvarchar) + ' Answers)';
		
		DELETE FROM dbo.RiskModellResults WHERE InputId = @InputId;

        EXEC sys.sp_executesql @SQL;

        FETCH NEXT FROM cur INTO @InputId, @Questions, @Answers;
    END

    CLOSE cur;
    DEALLOCATE cur;

END