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.
Prasad RaveendranPosted Mar 16, 2025, 5:41 AM
ssues:
Integer Division Truncation:
Since
@sumAand@sumBare integers, the division result is also an integer, losing decimal precision before converting toDECIMAL(6,2).DECIMAL(6,2)before division:Why
NULLIF(@sumB, 0)? This prevents division by zero.Repeated Accumulation in Loop:
@sumAand@sumB, leading to incorrect cumulative calculations.Better Alternative - Using a Table Variable:
Instead of many variables and
CASEstatements, a table variable simplifies the logic:This approach makes it much easier to manage and debug!
Marius VasilePosted Mar 16, 2025, 5:52 AM
Thank you Prasad, it is working well now
Marius VasilePosted Mar 15, 2025, 2:52 PM
I modiefied the stored procedure to check the calculation to
but the result is not corect
Emily FosterPosted Mar 14, 2025, 4:49 PM
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!