SQL Server procedure not returning result expected

Marius Vasile

I have a procedure that suposed to return an agregate function but it does not

CREATE PROCEDURE [dbo].[RAMRL]

AS 
	 
	DECLARE @a1 int,@a2 int,@a3 int,@a4 int,@a5 int,@a6 int,@a7 int,@a8 int,@a9 int,@a10 int,@a11 int,@a12 int, @a13 int,@a14 int,@a15 int,@a16 int,@a17 int,@a18 int,@a19 int,@a20 int,@a21 int,@a22 int,@a23 int,@a24 int;
	DECLARE @b1 int,@b2 int,@b3 int,@b4 int,@b5 int,@b6 int,@b7 int,@b8 int,@b9 int,@b10 int,@b11 int,@b12 int, @b13 int,@b14 int,@b15 int,@b16 int,@b17 int,@b18 int,@b19 int,@b20 int,@b21 int,@b22 int,@b23 int,@b24 int;
	
	DECLARE @resultTOT1 decimal(6,2),@resultTOT2 decimal(6,2),@resultTOT3 decimal(6,2),@resultTOT4 decimal(6,2),@resultTOT5 decimal(6,2),@resultTOT6 decimal(6,2),@resultTOT7 decimal(6,2),@resultTOT8 decimal(6,2),@resultTOT9 decimal(6,2),@resultTOT10 decimal(6,2),@resultTOT11 decimal(6,2),@resultTOT12 decimal(6,2), @resultTOT13 decimal(6,2),@resultTOT14 decimal(6,2),@resultTOT15 decimal(6,2),@resultTOT16 decimal(6,2),@resultTOT17 decimal(6,2),@resultTOT18 decimal(6,2),@resultTOT19 decimal(6,2),@resultTOT20 decimal(6,2),@resultTOT21 decimal(6,2),@resultTOT22 decimal(6,2),@resultTOT23 decimal(6,2),@resultTOT24 decimal(6,2);
	

	DECLARE @i INT = 1;
	DECLARE @sumTOT int = 0;
	DECLARE @sumATOT int = 0;
	DECLARE @currentResultTOT DECIMAL(6, 2);
	
	DECLARE @CurrentDate AS Date = GETDATE();

	SET @a1 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, @CurrentDate) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, @CurrentDate) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a2 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-1,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-1,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a3 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-2,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-2,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a4 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-3,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-3,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a5 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-4,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-4,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a6 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-5,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-5,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a7 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-6,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-6,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a8 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-7,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-7,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a9 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-8,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-8,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a10 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-9,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-9,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a11 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-10,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-10,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a12 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-11,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-11,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a13 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-12,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-12,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a14 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-13,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-13,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a15 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-14,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-14,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a16 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-15,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-15,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a17 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-16,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-16,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a18 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-17,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-17,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a19 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-18,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-18,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a20 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-19,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-19,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a21 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-20,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-20,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a22 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-21,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-21,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a23 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-22,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-22,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a24 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-23,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-23,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');


	SET @b1 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, @CurrentDate) AND Month = 'January'),0);
	SET @b2 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-1,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-1,@CurrentDate)))),0);
	SET @b3 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-2,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-2,@CurrentDate)))),0);
	SET @b4 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-3,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-3,@CurrentDate)))),0);
	SET @b5 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-4,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-4,@CurrentDate)))),0);
	SET @b6 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-5,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-5,@CurrentDate)))),0);
	SET @b7 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-6,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-6,@CurrentDate)))),0);
	SET @b8 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-7,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-7,@CurrentDate)))),0);
	SET @b9 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-8,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-8,@CurrentDate)))),0);
	SET @b10 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-9,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-9,@CurrentDate)))),0);
	SET @b11 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-10,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-10,@CurrentDate)))),0);
	SET @b12 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-11,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-11,@CurrentDate)))),0);
	SET @b13 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-12,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-12,@CurrentDate)))),0);
	SET @b14 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-13,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-13,@CurrentDate)))),0);
	SET @b15 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-14,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-14,@CurrentDate)))),0);
	SET @b16 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-15,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-15,@CurrentDate)))),0);
	SET @b17 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-16,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-16,@CurrentDate)))),0);
	SET @b18 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-17,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-17,@CurrentDate)))),0);
	SET @b19 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-18,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-18,@CurrentDate)))),0);
	SET @b20 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-19,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-19,@CurrentDate)))),0);
	SET @b21 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-20,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-20,@CurrentDate)))),0);
	SET @b22 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-21,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-21,@CurrentDate)))),0);
	SET @b23 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-22,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-22,@CurrentDate)))),0);
	SET @b24 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-23,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-23,@CurrentDate)))),0);


	WHILE @i <= 24
	BEGIN
		-- Accumulate sums
		
		SET @sumATOT = @sumATOT + CASE @i
			WHEN 1 THEN @a1
			WHEN 2 THEN @a2
			WHEN 3 THEN @a3
			WHEN 4 THEN @a4
			WHEN 5 THEN @a5
			WHEN 6 THEN @a6
			WHEN 7 THEN @a7
			WHEN 8 THEN @a8
			WHEN 9 THEN @a9
			WHEN 10 THEN @a10
			WHEN 11 THEN @a11
			WHEN 12 THEN @a12
			WHEN 13 THEN @a13
			WHEN 14 THEN @a14
			WHEN 15 THEN @a15
			WHEN 16 THEN @a16
			WHEN 17 THEN @a17
			WHEN 18 THEN @a18
			WHEN 19 THEN @a19
			WHEN 20 THEN @a20
			WHEN 21 THEN @a21
			WHEN 22 THEN @a22
			WHEN 23 THEN @a23
			WHEN 24 THEN @a24
		END;

		SET @sumTOT = @sumTOT + CASE @i
			WHEN 1 THEN @b1
			WHEN 2 THEN @b2
			WHEN 3 THEN @b3
			WHEN 4 THEN @b4
			WHEN 5 THEN @b5
			WHEN 6 THEN @b6
			WHEN 7 THEN @b7
			WHEN 8 THEN @b8
			WHEN 9 THEN @b9
			WHEN 10 THEN @b10
			WHEN 11 THEN @b11
			WHEN 12 THEN @b12
			WHEN 13 THEN @b13
			WHEN 14 THEN @b14
			WHEN 15 THEN @b15
			WHEN 16 THEN @b16
			WHEN 17 THEN @b17
			WHEN 18 THEN @b18
			WHEN 19 THEN @b19
			WHEN 20 THEN @b20
			WHEN 21 THEN @b21
			WHEN 22 THEN @b22
			WHEN 23 THEN @b23
			WHEN 24 THEN @b24
		END;

		-- Perform calculation and store the result

		SET @currentResultTOT = CONVERT(DECIMAL(6,2), (@sumATOT * 200000) / @sumTOT);
				
		-- Dynamically assign the result to variables (based on the index)
		-- You could also store this in a temp table if dynamic assignment isn't needed

		IF @i = 1 SET @resultTOT1 = @currentResultTOT;
		IF @i = 2 SET @resultTOT2 = @currentResultTOT;
		IF @i = 3 SET @resultTOT3 = @currentResultTOT;
		IF @i = 4 SET @resultTOT4 = @currentResultTOT;
		IF @i = 5 SET @resultTOT5 = @currentResultTOT;
		IF @i = 6 SET @resultTOT6 = @currentResultTOT;
		IF @i = 7 SET @resultTOT7 = @currentResultTOT;
		IF @i = 8 SET @resultTOT8 = @currentResultTOT;
		IF @i = 9 SET @resultTOT9 = @currentResultTOT;
		IF @i = 10 SET @resultTOT10 = @currentResultTOT;
		IF @i = 11 SET @resultTOT11 = @currentResultTOT;
		IF @i = 12 SET @resultTOT12 = @currentResultTOT;
		IF @i = 13 SET @resultTOT13 = @currentResultTOT;
		IF @i = 14 SET @resultTOT14 = @currentResultTOT;
		IF @i = 15 SET @resultTOT15 = @currentResultTOT;
		IF @i = 16 SET @resultTOT16 = @currentResultTOT;
		IF @i = 17 SET @resultTOT17 = @currentResultTOT;
		IF @i = 18 SET @resultTOT18 = @currentResultTOT;
		IF @i = 19 SET @resultTOT19 = @currentResultTOT;
		IF @i = 20 SET @resultTOT20 = @currentResultTOT;
		IF @i = 21 SET @resultTOT21 = @currentResultTOT;
		IF @i = 22 SET @resultTOT22 = @currentResultTOT;
		IF @i = 23 SET @resultTOT23 = @currentResultTOT;
		IF @i = 24 SET @resultTOT24 = @currentResultTOT;

		-- Increment the loop
		SET @i = @i + 1;
	END;
		
	SELECT 
		@resultTOT1 AS ResultTOT1,
		@resultTOT2 AS ResultTOT2,
		@resultTOT3 AS ResultTOT3,
		@resultTOT4 AS ResultTOT4,
		@resultTOT5 AS ResultTOT5,
		@resultTOT6 AS ResultTOT6,
		@resultTOT7 AS ResultTOT7,
		@resultTOT8 AS ResultTOT8,
		@resultTOT9 AS ResultTOT9,
		@resultTOT10 AS ResultTOT10,
		@resultTOT11 AS ResultTOT11,
		@resultTOT12 AS ResultTOT12,
		@resultTOT13 AS ResultTOT13,
		@resultTOT14 AS ResultTOT14,
		@resultTOT15 AS ResultTOT15,
		@resultTOT16 AS ResultTOT16,
		@resultTOT17 AS ResultTOT17,
		@resultTOT18 AS ResultTOT18,
		@resultTOT19 AS ResultTOT19,
		@resultTOT20 AS ResultTOT20,
		@resultTOT21 AS ResultTOT21,
		@resultTOT22 AS ResultTOT22,
		@resultTOT23 AS ResultTOT23,
		@resultTOT24 AS ResultTOT24;

I run the procedure in SMSS and @a1 to @a24 and @b1 to @b24 are not null and result is integer. However, the @resultTOT1 to @resultTOT24 is returning 0. I can't figure out why, please help.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Prasad Raveendran1y ago

Accepted answer

ssues:

Integer Division Truncation:
Since @sumA and @sumB are integers, the division result is also an integer, losing decimal precision before converting to DECIMAL(6,2).
- Fix: Convert at least one operand to DECIMAL(6,2) before division:
```
???????SET @currentResultTOT = CONVERT(DECIMAL(6,2), @sumA) / NULLIF(@sumB, 0);
```
  Why NULLIF(@sumB, 0)? This prevents division by zero.

Repeated Accumulation in Loop:

Each iteration keeps adding values to @sumA and @sumB, leading to incorrect cumulative calculations.

Fix: Compute only the individual division at each iteration instead of cumulative sums:

???????SET @currentResultTOT = CONVERT(DECIMAL(6,2), CASE @counter WHEN 1 THEN @a1 / NULLIF(@b1, 0) WHEN 2 THEN @a2 / NULLIF(@b2, 0) WHEN 3 THEN @a3 / NULLIF(@b3, 0) WHEN 4 THEN @a4 / NULLIF(@b4, 0) WHEN 5 THEN @a5 / NULLIF(@b5, 0) WHEN 6 THEN @a6 / NULLIF(@b6, 0) WHEN 7 THEN @a7 / NULLIF(@b7, 0) WHEN 8 THEN @a8 / NULLIF(@b8, 0) WHEN 9 THEN @a9 / NULLIF(@b9, 0) WHEN 10 THEN @a10 / NULLIF(@b10, 0) WHEN 11 THEN @a11 / NULLIF(@b11, 0) WHEN 12 THEN @a12 / NULLIF(@b12, 0) WHEN 13 THEN @a13 / NULLIF(@b13, 0) WHEN 14 THEN @a14 / NULLIF(@b14, 0) WHEN 15 THEN @a15 / NULLIF(@b15, 0) WHEN 16 THEN @a16 / NULLIF(@b16, 0) WHEN 17 THEN @a17 / NULLIF(@b17, 0) WHEN 18 THEN @a18 / NULLIF(@b18, 0) WHEN 19 THEN @a19 / NULLIF(@b19, 0) WHEN 20 THEN @a20 / NULLIF(@b20, 0) WHEN 21 THEN @a21 / NULLIF(@b21, 0) WHEN 22 THEN @a22 / NULLIF(@b22, 0) WHEN 23 THEN @a23 / NULLIF(@b23, 0) WHEN 24 THEN @a24 / NULLIF(@b24, 0) END );

Better Alternative - Using a Table Variable:
Instead of many variables and CASE statements, a table variable simplifies the logic:

DECLARE @Data TABLE ( RowNum INT PRIMARY KEY, A INT, B INT, Result DECIMAL(6,2) ); -- Insert all values into the table INSERT INTO @Data (RowNum, A, B) VALUES (1,4,1), (2,3,2), (3,4,1), (4,4,2), (5,0,0), (6,3,1), (7,0,0), (8,2,1), (9,1,0), (10,2,1), (11,4,1), (12,3,4), (13,6,2), (14,5,1), (15,3,0), (16,3,2), (17,9,8), (18,6,3), (19,6,3), (20,4,2), (21,0,0), (22,2,1), (23,6,3), (24,9,4); -- Compute results UPDATE @Data SET Result = CONVERT(DECIMAL(6,2), A) / NULLIF(B, 0); -- Return results SELECT * FROM @Data;

This approach makes it much easier to manage and debug!

Marius Vasile1y ago
Thank you Prasad, it is working well now
0

Marius Vasile1y ago

I modiefied the stored procedure to check the calculation to

CREATE PROCEDURE [dbo].[Test2]

AS 
	 
	DECLARE @a1 int,@a2 int,@a3 int,@a4 int,@a5 int,@a6 int,@a7 int,@a8 int,@a9 int,@a10 int,@a11 int,@a12 int, @a13 int,@a14 int,@a15 int,@a16 int,@a17 int,@a18 int,@a19 int,@a20 int,@a21 int,@a22 int,@a23 int,@a24 int;
	DECLARE @b1 int,@b2 int,@b3 int,@b4 int,@b5 int,@b6 int,@b7 int,@b8 int,@b9 int,@b10 int,@b11 int,@b12 int, @b13 int,@b14 int,@b15 int,@b16 int,@b17 int,@b18 int,@b19 int,@b20 int,@b21 int,@b22 int,@b23 int,@b24 int;

	DECLARE @resultTOT1 decimal(6,2),@resultTOT2 decimal(6,2),@resultTOT3 decimal(6,2),@resultTOT4 decimal(6,2),@resultTOT5 decimal(6,2),@resultTOT6 decimal(6,2),@resultTOT7 decimal(6,2),@resultTOT8 decimal(6,2),@resultTOT9 decimal(6,2),@resultTOT10 decimal(6,2),@resultTOT11 decimal(6,2),@resultTOT12 decimal(6,2), @resultTOT13 decimal(6,2),@resultTOT14 decimal(6,2),@resultTOT15 decimal(6,2),@resultTOT16 decimal(6,2),@resultTOT17 decimal(6,2),@resultTOT18 decimal(6,2),@resultTOT19 decimal(6,2),@resultTOT20 decimal(6,2),@resultTOT21 decimal(6,2),@resultTOT22 decimal(6,2),@resultTOT23 decimal(6,2),@resultTOT24 decimal(6,2);
	

	DECLARE @counter INT;
	SET @counter = 1;
	DECLARE @sumA int;
	SET @sumA = 0;
	DECLARE @sumB int;
	SET @sumB = 0;
	DECLARE @currentResultTOT DECIMAL(6, 2);
	SET @currentResultTOT = 0;
	
	SET @a1 = 4;
	SET @a2 = 3;
	SET @a3 = 4;
	SET @a4 = 4
	SET @a5 = 0
	SET @a6 = 3;
	SET @a7 = 0;
	SET @a8 = 2;
	SET @a9 = 1;
	SET @a10 = 2;
	SET @a11 = 4;
	SET @a12 = 3;
	SET @a13 = 6;
	SET @a14 = 5;
	SET @a15 = 3;
	SET @a16 = 3;
	SET @a17 = 9;
	SET @a18 = 6;
	SET @a19 = 6;
	SET @a20 = 4;
	SET @a21 = 0;
	SET @a22 = 2;
	SET @a23 = 6;
	SET @a24 = 9;

	SET @b1 = 1;
	SET @b2 = 2;
	SET @b3 = 1;
	SET @b4 = 2
	SET @b5 = 0
	SET @b6 = 1;
	SET @b7 = 0;
	SET @b8 = 1;
	SET @b9 = 0;
	SET @b10 = 1;
	SET @b11 = 1;
	SET @b12 = 4;
	SET @b13 = 2;
	SET @b14 = 1;
	SET @b15 = 0;
	SET @b16 = 2;
	SET @b17 = 8;
	SET @b18 = 3;
	SET @b19 = 3;
	SET @b20 = 2;
	SET @b21 = 0;
	SET @b22 = 1;
	SET @b23 = 3;
	SET @b24 = 4;

	WHILE (@counter <= 24)
	BEGIN
		-- Accumulate sums
		
		SET @sumA = @sumA + CASE @counter
			WHEN 1 THEN @a1
			WHEN 2 THEN @a2
			WHEN 3 THEN @a3
			WHEN 4 THEN @a4
			WHEN 5 THEN @a5
			WHEN 6 THEN @a6
			WHEN 7 THEN @a7
			WHEN 8 THEN @a8
			WHEN 9 THEN @a9
			WHEN 10 THEN @a10
			WHEN 11 THEN @a11
			WHEN 12 THEN @a12
			WHEN 13 THEN @a13
			WHEN 14 THEN @a14
			WHEN 15 THEN @a15
			WHEN 16 THEN @a16
			WHEN 17 THEN @a17
			WHEN 18 THEN @a18
			WHEN 19 THEN @a19
			WHEN 20 THEN @a20
			WHEN 21 THEN @a21
			WHEN 22 THEN @a22
			WHEN 23 THEN @a23
			WHEN 24 THEN @a24
		END;

		SET @sumB = @sumB + CASE @counter
			WHEN 1 THEN @b1
			WHEN 2 THEN @b2
			WHEN 3 THEN @b3
			WHEN 4 THEN @b4
			WHEN 5 THEN @b5
			WHEN 6 THEN @b6
			WHEN 7 THEN @b7
			WHEN 8 THEN @b8
			WHEN 9 THEN @b9
			WHEN 10 THEN @b10
			WHEN 11 THEN @b11
			WHEN 12 THEN @b12
			WHEN 13 THEN @b13
			WHEN 14 THEN @b14
			WHEN 15 THEN @b15
			WHEN 16 THEN @b16
			WHEN 17 THEN @b17
			WHEN 18 THEN @b18
			WHEN 19 THEN @b19
			WHEN 20 THEN @b20
			WHEN 21 THEN @b21
			WHEN 22 THEN @b22
			WHEN 23 THEN @b23
			WHEN 24 THEN @b24
		END;

		-- Perform calculation and store the result
    SET @currentResultTOT = CONVERT(DECIMAL(6,2), @sumA / @sumB);

    -- Dynamically assign the result to variables (based on the index)
    -- You could also store this in a temp table if dynamic assignment isn't needed
		IF @counter = 1 SET @resultTOT1 = @currentResultTOT;
		IF @counter = 2 SET @resultTOT2 = @currentResultTOT;
		IF @counter = 3 SET @resultTOT3 = @currentResultTOT;
		IF @counter = 4 SET @resultTOT4 = @currentResultTOT;
		IF @counter = 5 SET @resultTOT5 = @currentResultTOT;
		IF @counter = 6 SET @resultTOT6 = @currentResultTOT;
		IF @counter = 7 SET @resultTOT7 = @currentResultTOT;
		IF @counter = 8 SET @resultTOT8 = @currentResultTOT;
		IF @counter = 9 SET @resultTOT9 = @currentResultTOT;
		IF @counter = 10 SET @resultTOT10 = @currentResultTOT;
		IF @counter = 11 SET @resultTOT11 = @currentResultTOT;
		IF @counter = 12 SET @resultTOT12 = @currentResultTOT;
		IF @counter = 13 SET @resultTOT13 = @currentResultTOT;
		IF @counter = 14 SET @resultTOT14 = @currentResultTOT;
		IF @counter = 15 SET @resultTOT15 = @currentResultTOT;
		IF @counter = 16 SET @resultTOT16 = @currentResultTOT;
		IF @counter = 17 SET @resultTOT17 = @currentResultTOT;
		IF @counter = 18 SET @resultTOT18 = @currentResultTOT;
		IF @counter = 19 SET @resultTOT19 = @currentResultTOT;
		IF @counter = 20 SET @resultTOT20 = @currentResultTOT;
		IF @counter = 21 SET @resultTOT21 = @currentResultTOT;
		IF @counter = 22 SET @resultTOT22 = @currentResultTOT;
		IF @counter = 23 SET @resultTOT23 = @currentResultTOT;
		IF @counter = 24 SET @resultTOT24 = @currentResultTOT;

    -- Increment the loop
    SET @counter = @counter + 1;
	END;
		SELECT @a1,@a2,@a3,@a4,@a5,@a6,@a7,@a8,@a9,@a10,@a11,@a12,@a13,@a14,@a15,@a16,@a17,@a18,@a19,@a20,@a21,@a22,@a23,@a24;
		SELECT @b1,@b2,@b3,@b4,@b5,@b6,@b7,@b8,@b9,@b10,@b11,@b12,@b13,@b14,@b15,@b16,@b17,@b18,@b19,@b20,@b21,@b22,@b23,@b24;
		SELECT 
				@resultTOT1 AS ResultTOT1,
				@resultTOT2 AS ResultTOT2,
				@resultTOT3 AS ResultTOT3,
				@resultTOT4 AS ResultTOT4,
				@resultTOT5 AS ResultTOT5,
				@resultTOT6 AS ResultTOT6,
				@resultTOT7 AS ResultTOT7,
				@resultTOT8 AS ResultTOT8,
				@resultTOT9 AS ResultTOT9,
				@resultTOT10 AS ResultTOT10,
				@resultTOT11 AS ResultTOT11,
				@resultTOT12 AS ResultTOT12,
				@resultTOT13 AS ResultTOT13,
				@resultTOT14 AS ResultTOT14,
				@resultTOT15 AS ResultTOT15,
				@resultTOT16 AS ResultTOT16,
				@resultTOT17 AS ResultTOT17,
				@resultTOT18 AS ResultTOT18,
				@resultTOT19 AS ResultTOT19,
				@resultTOT20 AS ResultTOT20,
				@resultTOT21 AS ResultTOT21,
				@resultTOT22 AS ResultTOT22,
				@resultTOT23 AS ResultTOT23,
				@resultTOT24 AS ResultTOT24;

but the result is not corect

only the first one is correct, the rest are not. Please help!!!

Emily Foster1y ago
It seems that the issue causing @resultTOT1 to @resultTOT24 to return 0 in your SQL Server procedure lies within the aggregation logic and variable assignments. Here are some aspects to consider in troubleshooting this problem:

1. Debugging the Calculation:

- Verify that your calculation for `@currentResultTOT` is correctly computing the result. Check if the values of `@sumATOT` and `@sumTOT` are being computed accurately to ensure the division yields the expected outcome. You can use `PRINT` or `SELECT` statements within the loop to inspect these intermediate values.

2. Data Availability:

- Double-check the data retrieved by queries assigning values to variables like @a1 to @a24 and @b1 to @b24. Ensure that the queries are fetching the necessary data and that there are no conditions causing unexpected results, such as missing data or incorrect join conditions.

3. Month-Handling:

- Review the logic concerning month calculations, particularly how you are handling month offsets and ensuring that the correct months are considered in your date calculations. An error in month calculation could lead to unexpected results.

4. Data Types and Conversions:

- Confirm that the data types used in computations and assignments align with the expected result types. Check whether any implicit conversions may be altering the results unintentionally.

5. Scope and Visibility:

- Ensure that the variables are correctly scoped throughout the procedure. Variables declared outside the loop should retain their values as intended, and the calculations should accumulate properly.

6. Error Handling:

- Implement error handling within your stored procedure to catch any potential issues that might be occurring during data retrieval or calculations. This can help in identifying and addressing issues promptly.

By thoroughly reviewing these aspects and potentially adding some diagnostic messages within the stored procedure to output intermediate results, you can pinpoint the root cause of why @resultTOT1 to @resultTOT24 is returning 0. Once you identify the specific stage where the unexpected behavior occurs, it will be easier to refine the procedure and correct the issue. Let me know if you need further assistance or more specific guidance on any of these points!
0

SQL Server procedure not returning result expected

I have a procedure that suposed to return an agregate function but it does not

CREATE PROCEDURE [dbo].[RAMRL]

AS 
	 
	DECLARE @a1 int,@a2 int,@a3 int,@a4 int,@a5 int,@a6 int,@a7 int,@a8 int,@a9 int,@a10 int,@a11 int,@a12 int, @a13 int,@a14 int,@a15 int,@a16 int,@a17 int,@a18 int,@a19 int,@a20 int,@a21 int,@a22 int,@a23 int,@a24 int;
	DECLARE @b1 int,@b2 int,@b3 int,@b4 int,@b5 int,@b6 int,@b7 int,@b8 int,@b9 int,@b10 int,@b11 int,@b12 int, @b13 int,@b14 int,@b15 int,@b16 int,@b17 int,@b18 int,@b19 int,@b20 int,@b21 int,@b22 int,@b23 int,@b24 int;
	
	DECLARE @resultTOT1 decimal(6,2),@resultTOT2 decimal(6,2),@resultTOT3 decimal(6,2),@resultTOT4 decimal(6,2),@resultTOT5 decimal(6,2),@resultTOT6 decimal(6,2),@resultTOT7 decimal(6,2),@resultTOT8 decimal(6,2),@resultTOT9 decimal(6,2),@resultTOT10 decimal(6,2),@resultTOT11 decimal(6,2),@resultTOT12 decimal(6,2), @resultTOT13 decimal(6,2),@resultTOT14 decimal(6,2),@resultTOT15 decimal(6,2),@resultTOT16 decimal(6,2),@resultTOT17 decimal(6,2),@resultTOT18 decimal(6,2),@resultTOT19 decimal(6,2),@resultTOT20 decimal(6,2),@resultTOT21 decimal(6,2),@resultTOT22 decimal(6,2),@resultTOT23 decimal(6,2),@resultTOT24 decimal(6,2);
	

	DECLARE @i INT = 1;
	DECLARE @sumTOT int = 0;
	DECLARE @sumATOT int = 0;
	DECLARE @currentResultTOT DECIMAL(6, 2);
	
	DECLARE @CurrentDate AS Date = GETDATE();

	SET @a1 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, @CurrentDate) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, @CurrentDate) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a2 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-1,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-1,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a3 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-2,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-2,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a4 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-3,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-3,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a5 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-4,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-4,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a6 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-5,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-5,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a7 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-6,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-6,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a8 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-7,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-7,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a9 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-8,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-8,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a10 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-9,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-9,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a11 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-10,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-10,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a12 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-11,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-11,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a13 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-12,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-12,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a14 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-13,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-13,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a15 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-14,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-14,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a16 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-15,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-15,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a17 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-16,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-16,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a18 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-17,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-17,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a19 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-18,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-18,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a20 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-19,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-19,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a21 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-20,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-20,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a22 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-21,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-21,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a23 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-22,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-22,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');
	SET @a24 = (SELECT SUM(CASE When tc.Subcategorie LIKE 'Accident de munca %' AND DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-23,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-23,@CurrentDate)) Then 1 Else 0 End) FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = 'SSM');


	SET @b1 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, @CurrentDate) AND Month = 'January'),0);
	SET @b2 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-1,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-1,@CurrentDate)))),0);
	SET @b3 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-2,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-2,@CurrentDate)))),0);
	SET @b4 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-3,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-3,@CurrentDate)))),0);
	SET @b5 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-4,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-4,@CurrentDate)))),0);
	SET @b6 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-5,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-5,@CurrentDate)))),0);
	SET @b7 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-6,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-6,@CurrentDate)))),0);
	SET @b8 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-7,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-7,@CurrentDate)))),0);
	SET @b9 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-8,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-8,@CurrentDate)))),0);
	SET @b10 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-9,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-9,@CurrentDate)))),0);
	SET @b11 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-10,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-10,@CurrentDate)))),0);
	SET @b12 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-11,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-11,@CurrentDate)))),0);
	SET @b13 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-12,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-12,@CurrentDate)))),0);
	SET @b14 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-13,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-13,@CurrentDate)))),0);
	SET @b15 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-14,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-14,@CurrentDate)))),0);
	SET @b16 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-15,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-15,@CurrentDate)))),0);
	SET @b17 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-16,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-16,@CurrentDate)))),0);
	SET @b18 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-17,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-17,@CurrentDate)))),0);
	SET @b19 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-18,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-18,@CurrentDate)))),0);
	SET @b20 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-19,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-19,@CurrentDate)))),0);
	SET @b21 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-20,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-20,@CurrentDate)))),0);
	SET @b22 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-21,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-21,@CurrentDate)))),0);
	SET @b23 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-22,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-22,@CurrentDate)))),0);
	SET @b24 = ISNULL((SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-23,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-23,@CurrentDate)))),0);


	WHILE @i <= 24
	BEGIN
		-- Accumulate sums
		
		SET @sumATOT = @sumATOT + CASE @i
			WHEN 1 THEN @a1
			WHEN 2 THEN @a2
			WHEN 3 THEN @a3
			WHEN 4 THEN @a4
			WHEN 5 THEN @a5
			WHEN 6 THEN @a6
			WHEN 7 THEN @a7
			WHEN 8 THEN @a8
			WHEN 9 THEN @a9
			WHEN 10 THEN @a10
			WHEN 11 THEN @a11
			WHEN 12 THEN @a12
			WHEN 13 THEN @a13
			WHEN 14 THEN @a14
			WHEN 15 THEN @a15
			WHEN 16 THEN @a16
			WHEN 17 THEN @a17
			WHEN 18 THEN @a18
			WHEN 19 THEN @a19
			WHEN 20 THEN @a20
			WHEN 21 THEN @a21
			WHEN 22 THEN @a22
			WHEN 23 THEN @a23
			WHEN 24 THEN @a24
		END;

		SET @sumTOT = @sumTOT + CASE @i
			WHEN 1 THEN @b1
			WHEN 2 THEN @b2
			WHEN 3 THEN @b3
			WHEN 4 THEN @b4
			WHEN 5 THEN @b5
			WHEN 6 THEN @b6
			WHEN 7 THEN @b7
			WHEN 8 THEN @b8
			WHEN 9 THEN @b9
			WHEN 10 THEN @b10
			WHEN 11 THEN @b11
			WHEN 12 THEN @b12
			WHEN 13 THEN @b13
			WHEN 14 THEN @b14
			WHEN 15 THEN @b15
			WHEN 16 THEN @b16
			WHEN 17 THEN @b17
			WHEN 18 THEN @b18
			WHEN 19 THEN @b19
			WHEN 20 THEN @b20
			WHEN 21 THEN @b21
			WHEN 22 THEN @b22
			WHEN 23 THEN @b23
			WHEN 24 THEN @b24
		END;

		-- Perform calculation and store the result

		SET @currentResultTOT = CONVERT(DECIMAL(6,2), (@sumATOT * 200000) / @sumTOT);
				
		-- Dynamically assign the result to variables (based on the index)
		-- You could also store this in a temp table if dynamic assignment isn't needed

		IF @i = 1 SET @resultTOT1 = @currentResultTOT;
		IF @i = 2 SET @resultTOT2 = @currentResultTOT;
		IF @i = 3 SET @resultTOT3 = @currentResultTOT;
		IF @i = 4 SET @resultTOT4 = @currentResultTOT;
		IF @i = 5 SET @resultTOT5 = @currentResultTOT;
		IF @i = 6 SET @resultTOT6 = @currentResultTOT;
		IF @i = 7 SET @resultTOT7 = @currentResultTOT;
		IF @i = 8 SET @resultTOT8 = @currentResultTOT;
		IF @i = 9 SET @resultTOT9 = @currentResultTOT;
		IF @i = 10 SET @resultTOT10 = @currentResultTOT;
		IF @i = 11 SET @resultTOT11 = @currentResultTOT;
		IF @i = 12 SET @resultTOT12 = @currentResultTOT;
		IF @i = 13 SET @resultTOT13 = @currentResultTOT;
		IF @i = 14 SET @resultTOT14 = @currentResultTOT;
		IF @i = 15 SET @resultTOT15 = @currentResultTOT;
		IF @i = 16 SET @resultTOT16 = @currentResultTOT;
		IF @i = 17 SET @resultTOT17 = @currentResultTOT;
		IF @i = 18 SET @resultTOT18 = @currentResultTOT;
		IF @i = 19 SET @resultTOT19 = @currentResultTOT;
		IF @i = 20 SET @resultTOT20 = @currentResultTOT;
		IF @i = 21 SET @resultTOT21 = @currentResultTOT;
		IF @i = 22 SET @resultTOT22 = @currentResultTOT;
		IF @i = 23 SET @resultTOT23 = @currentResultTOT;
		IF @i = 24 SET @resultTOT24 = @currentResultTOT;

		-- Increment the loop
		SET @i = @i + 1;
	END;
		
	SELECT 
		@resultTOT1 AS ResultTOT1,
		@resultTOT2 AS ResultTOT2,
		@resultTOT3 AS ResultTOT3,
		@resultTOT4 AS ResultTOT4,
		@resultTOT5 AS ResultTOT5,
		@resultTOT6 AS ResultTOT6,
		@resultTOT7 AS ResultTOT7,
		@resultTOT8 AS ResultTOT8,
		@resultTOT9 AS ResultTOT9,
		@resultTOT10 AS ResultTOT10,
		@resultTOT11 AS ResultTOT11,
		@resultTOT12 AS ResultTOT12,
		@resultTOT13 AS ResultTOT13,
		@resultTOT14 AS ResultTOT14,
		@resultTOT15 AS ResultTOT15,
		@resultTOT16 AS ResultTOT16,
		@resultTOT17 AS ResultTOT17,
		@resultTOT18 AS ResultTOT18,
		@resultTOT19 AS ResultTOT19,
		@resultTOT20 AS ResultTOT20,
		@resultTOT21 AS ResultTOT21,
		@resultTOT22 AS ResultTOT22,
		@resultTOT23 AS ResultTOT23,
		@resultTOT24 AS ResultTOT24;

I run the procedure in SMSS and @a1 to @a24 and @b1 to @b24 are not null and result is integer. However, the @resultTOT1 to @resultTOT24 is returning 0. I can't figure out why, please help.

???????SET @currentResultTOT = CONVERT(DECIMAL(6,2), CASE @counter WHEN 1 THEN @a1 / NULLIF(@b1, 0) WHEN 2 THEN @a2 / NULLIF(@b2, 0) WHEN 3 THEN @a3 / NULLIF(@b3, 0) WHEN 4 THEN @a4 / NULLIF(@b4, 0) WHEN 5 THEN @a5 / NULLIF(@b5, 0) WHEN 6 THEN @a6 / NULLIF(@b6, 0) WHEN 7 THEN @a7 / NULLIF(@b7, 0) WHEN 8 THEN @a8 / NULLIF(@b8, 0) WHEN 9 THEN @a9 / NULLIF(@b9, 0) WHEN 10 THEN @a10 / NULLIF(@b10, 0) WHEN 11 THEN @a11 / NULLIF(@b11, 0) WHEN 12 THEN @a12 / NULLIF(@b12, 0) WHEN 13 THEN @a13 / NULLIF(@b13, 0) WHEN 14 THEN @a14 / NULLIF(@b14, 0) WHEN 15 THEN @a15 / NULLIF(@b15, 0) WHEN 16 THEN @a16 / NULLIF(@b16, 0) WHEN 17 THEN @a17 / NULLIF(@b17, 0) WHEN 18 THEN @a18 / NULLIF(@b18, 0) WHEN 19 THEN @a19 / NULLIF(@b19, 0) WHEN 20 THEN @a20 / NULLIF(@b20, 0) WHEN 21 THEN @a21 / NULLIF(@b21, 0) WHEN 22 THEN @a22 / NULLIF(@b22, 0) WHEN 23 THEN @a23 / NULLIF(@b23, 0) WHEN 24 THEN @a24 / NULLIF(@b24, 0) END );

DECLARE @Data TABLE ( RowNum INT PRIMARY KEY, A INT, B INT, Result DECIMAL(6,2) ); -- Insert all values into the table INSERT INTO @Data (RowNum, A, B) VALUES (1,4,1), (2,3,2), (3,4,1), (4,4,2), (5,0,0), (6,3,1), (7,0,0), (8,2,1), (9,1,0), (10,2,1), (11,4,1), (12,3,4), (13,6,2), (14,5,1), (15,3,0), (16,3,2), (17,9,8), (18,6,3), (19,6,3), (20,4,2), (21,0,0), (22,2,1), (23,6,3), (24,9,4); -- Compute results UPDATE @Data SET Result = CONVERT(DECIMAL(6,2), A) / NULLIF(B, 0); -- Return results SELECT * FROM @Data;

CREATE PROCEDURE [dbo].[Test2] AS DECLARE @a1 int,@a2 int,@a3 int,@a4 int,@a5 int,@a6 int,@a7 int,@a8 int,@a9 int,@a10 int,@a11 int,@a12 int, @a13 int,@a14 int,@a15 int,@a16 int,@a17 int,@a18 int,@a19 int,@a20 int,@a21 int,@a22 int,@a23 int,@a24 int; DECLARE @b1 int,@b2 int,@b3 int,@b4 int,@b5 int,@b6 int,@b7 int,@b8 int,@b9 int,@b10 int,@b11 int,@b12 int, @b13 int,@b14 int,@b15 int,@b16 int,@b17 int,@b18 int,@b19 int,@b20 int,@b21 int,@b22 int,@b23 int,@b24 int; DECLARE @resultTOT1 decimal(6,2),@resultTOT2 decimal(6,2),@resultTOT3 decimal(6,2),@resultTOT4 decimal(6,2),@resultTOT5 decimal(6,2),@resultTOT6 decimal(6,2),@resultTOT7 decimal(6,2),@resultTOT8 decimal(6,2),@resultTOT9 decimal(6,2),@resultTOT10 decimal(6,2),@resultTOT11 decimal(6,2),@resultTOT12 decimal(6,2), @resultTOT13 decimal(6,2),@resultTOT14 decimal(6,2),@resultTOT15 decimal(6,2),@resultTOT16 decimal(6,2),@resultTOT17 decimal(6,2),@resultTOT18 decimal(6,2),@resultTOT19 decimal(6,2),@resultTOT20 decimal(6,2),@resultTOT21 decimal(6,2),@resultTOT22 decimal(6,2),@resultTOT23 decimal(6,2),@resultTOT24 decimal(6,2); DECLARE @counter INT; SET @counter = 1; DECLARE @sumA int; SET @sumA = 0; DECLARE @sumB int; SET @sumB = 0; DECLARE @currentResultTOT DECIMAL(6, 2); SET @currentResultTOT = 0; SET @a1 = 4; SET @a2 = 3; SET @a3 = 4; SET @a4 = 4 SET @a5 = 0 SET @a6 = 3; SET @a7 = 0; SET @a8 = 2; SET @a9 = 1; SET @a10 = 2; SET @a11 = 4; SET @a12 = 3; SET @a13 = 6; SET @a14 = 5; SET @a15 = 3; SET @a16 = 3; SET @a17 = 9; SET @a18 = 6; SET @a19 = 6; SET @a20 = 4; SET @a21 = 0; SET @a22 = 2; SET @a23 = 6; SET @a24 = 9; SET @b1 = 1; SET @b2 = 2; SET @b3 = 1; SET @b4 = 2 SET @b5 = 0 SET @b6 = 1; SET @b7 = 0; SET @b8 = 1; SET @b9 = 0; SET @b10 = 1; SET @b11 = 1; SET @b12 = 4; SET @b13 = 2; SET @b14 = 1; SET @b15 = 0; SET @b16 = 2; SET @b17 = 8; SET @b18 = 3; SET @b19 = 3; SET @b20 = 2; SET @b21 = 0; SET @b22 = 1; SET @b23 = 3; SET @b24 = 4; WHILE (@counter <= 24) BEGIN -- Accumulate sums SET @sumA = @sumA + CASE @counter WHEN 1 THEN @a1 WHEN 2 THEN @a2 WHEN 3 THEN @a3 WHEN 4 THEN @a4 WHEN 5 THEN @a5 WHEN 6 THEN @a6 WHEN 7 THEN @a7 WHEN 8 THEN @a8 WHEN 9 THEN @a9 WHEN 10 THEN @a10 WHEN 11 THEN @a11 WHEN 12 THEN @a12 WHEN 13 THEN @a13 WHEN 14 THEN @a14 WHEN 15 THEN @a15 WHEN 16 THEN @a16 WHEN 17 THEN @a17 WHEN 18 THEN @a18 WHEN 19 THEN @a19 WHEN 20 THEN @a20 WHEN 21 THEN @a21 WHEN 22 THEN @a22 WHEN 23 THEN @a23 WHEN 24 THEN @a24 END; SET @sumB = @sumB + CASE @counter WHEN 1 THEN @b1 WHEN 2 THEN @b2 WHEN 3 THEN @b3 WHEN 4 THEN @b4 WHEN 5 THEN @b5 WHEN 6 THEN @b6 WHEN 7 THEN @b7 WHEN 8 THEN @b8 WHEN 9 THEN @b9 WHEN 10 THEN @b10 WHEN 11 THEN @b11 WHEN 12 THEN @b12 WHEN 13 THEN @b13 WHEN 14 THEN @b14 WHEN 15 THEN @b15 WHEN 16 THEN @b16 WHEN 17 THEN @b17 WHEN 18 THEN @b18 WHEN 19 THEN @b19 WHEN 20 THEN @b20 WHEN 21 THEN @b21 WHEN 22 THEN @b22 WHEN 23 THEN @b23 WHEN 24 THEN @b24 END; -- Perform calculation and store the result SET @currentResultTOT = CONVERT(DECIMAL(6,2), @sumA / @sumB); -- Dynamically assign the result to variables (based on the index) -- You could also store this in a temp table if dynamic assignment isn't needed IF @counter = 1 SET @resultTOT1 = @currentResultTOT; IF @counter = 2 SET @resultTOT2 = @currentResultTOT; IF @counter = 3 SET @resultTOT3 = @currentResultTOT; IF @counter = 4 SET @resultTOT4 = @currentResultTOT; IF @counter = 5 SET @resultTOT5 = @currentResultTOT; IF @counter = 6 SET @resultTOT6 = @currentResultTOT; IF @counter = 7 SET @resultTOT7 = @currentResultTOT; IF @counter = 8 SET @resultTOT8 = @currentResultTOT; IF @counter = 9 SET @resultTOT9 = @currentResultTOT; IF @counter = 10 SET @resultTOT10 = @currentResultTOT; IF @counter = 11 SET @resultTOT11 = @currentResultTOT; IF @counter = 12 SET @resultTOT12 = @currentResultTOT; IF @counter = 13 SET @resultTOT13 = @currentResultTOT; IF @counter = 14 SET @resultTOT14 = @currentResultTOT; IF @counter = 15 SET @resultTOT15 = @currentResultTOT; IF @counter = 16 SET @resultTOT16 = @currentResultTOT; IF @counter = 17 SET @resultTOT17 = @currentResultTOT; IF @counter = 18 SET @resultTOT18 = @currentResultTOT; IF @counter = 19 SET @resultTOT19 = @currentResultTOT; IF @counter = 20 SET @resultTOT20 = @currentResultTOT; IF @counter = 21 SET @resultTOT21 = @currentResultTOT; IF @counter = 22 SET @resultTOT22 = @currentResultTOT; IF @counter = 23 SET @resultTOT23 = @currentResultTOT; IF @counter = 24 SET @resultTOT24 = @currentResultTOT; -- Increment the loop SET @counter = @counter + 1; END; SELECT @a1,@a2,@a3,@a4,@a5,@a6,@a7,@a8,@a9,@a10,@a11,@a12,@a13,@a14,@a15,@a16,@a17,@a18,@a19,@a20,@a21,@a22,@a23,@a24; SELECT @b1,@b2,@b3,@b4,@b5,@b6,@b7,@b8,@b9,@b10,@b11,@b12,@b13,@b14,@b15,@b16,@b17,@b18,@b19,@b20,@b21,@b22,@b23,@b24; SELECT @resultTOT1 AS ResultTOT1, @resultTOT2 AS ResultTOT2, @resultTOT3 AS ResultTOT3, @resultTOT4 AS ResultTOT4, @resultTOT5 AS ResultTOT5, @resultTOT6 AS ResultTOT6, @resultTOT7 AS ResultTOT7, @resultTOT8 AS ResultTOT8, @resultTOT9 AS ResultTOT9, @resultTOT10 AS ResultTOT10, @resultTOT11 AS ResultTOT11, @resultTOT12 AS ResultTOT12, @resultTOT13 AS ResultTOT13, @resultTOT14 AS ResultTOT14, @resultTOT15 AS ResultTOT15, @resultTOT16 AS ResultTOT16, @resultTOT17 AS ResultTOT17, @resultTOT18 AS ResultTOT18, @resultTOT19 AS ResultTOT19, @resultTOT20 AS ResultTOT20, @resultTOT21 AS ResultTOT21, @resultTOT22 AS ResultTOT22, @resultTOT23 AS ResultTOT23, @resultTOT24 AS ResultTOT24;