Get 24 months rollover value from database

Marius Vasile

I am using a Stored Procedure to get data from database. I want to get the values based on current date and move 23 months before current date.

In a Stored Procedure I declare current date with

DECLARE @CurrentDate AS DateTime = GetDate();

Data for current month with a formula from I will only mention the date part because is too long and I know the rest is correct

WHERE DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, @CurrentDate) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, @CurrentDate)

and I try to get previous values with

WHERE DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-1,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-1,@CurrentDate))

and so on until

WHERE DATEPART(YEAR, tr.DataInitierii)=DATEPART(YEAR, DATEADD(MONTH,-23,@CurrentDate)) AND DATEPART(MONTH, tr.DataInitierii)=DATEPART(MONTH, DATEADD(MONTH,-23,@CurrentDate))

but the result is NULL despite having data in the database.

What am I doing wrong within the condition?

10 Replies

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

Marius Vasile1y ago

Ok, now I get full data even 0 if value in database is null but the following code part is not working as intended, final result is null not matter values are retrieved from database. I guess this SET @currentResultTOT = CONVERT(DECIMAL(6,2), (@sumATOT * 200000) / @sumTOT); is not working as intended.

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;

Marius Vasile1y ago

I rewrite the query, simplified and ran in SSMS. I got some data but not the final SUM. Can anyone tell me please what am I doing wrong?

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 DECIMAL(6, 2);
	DECLARE @sumATOT DECIMAL(6, 2);
	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 = (SELECT SUM(COALESCE(ValueCNE,0)+COALESCE(ValueCON,0)) FROM tblOmOra WHERE Year=DATEPART(YEAR, @CurrentDate) AND Month = 'January');
	SET @b2 = (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))));
	SET @b3 = (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))));
	SET @b4 = (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))));
	SET @b5 = (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))));
	SET @b6 = (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))));
	SET @b7 = (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))));
	SET @b8 = (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))));
	SET @b9 = (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))));
	SET @b10 = (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))));
	SET @b11 = (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))));
	SET @b12 = (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))));
	SET @b13 = (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))));
	SET @b14 = (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))));
	SET @b15 = (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))));
	SET @b16 = (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))));
	SET @b17 = (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))));
	SET @b18 = (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))));
	SET @b19 = (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))));
	SET @b20 = (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))));
	SET @b21 = (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))));
	SET @b22 = (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))));
	SET @b23 = (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))));
	SET @b24 = (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))));


	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);
		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 @sumATOT, @sumTOT, @currentResultTOT;
		-- 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;

result from SSMS

Marius Vasile1y ago

Full Stored Procedure below

CREATE PROCEDURE [dbo].[RAMRL]

AS 
	 
	DECLARE @acne0 INT,@acne1 INT,@acne2 INT,@acne3 INT,@acne4 INT,@acne5 INT,@acne6 INT,@acne7 INT,@acne8 INT,@acne9 INT,@acne10 INT,@acne11 INT,@acne12 INT, @acne13 INT,@acne14 INT,@acne15 INT,@acne16 INT,@acne17 INT,@acne18 INT,@acne19 INT,@acne20 INT,@acne21 INT,@acne22 INT,@acne23 INT;
	DECLARE @acon0 INT,@acon1 INT,@acon2 INT,@acon3 INT,@acon4 INT,@acon5 INT,@acon6 INT,@acon7 INT,@acon8 INT,@acon9 INT,@acon10 INT,@acon11 INT,@acon12 INT, @acon13 INT,@acon14 INT,@acon15 INT,@acon16 INT,@acon17 INT,@acon18 INT,@acon19 INT,@acon20 INT,@acon21 INT,@acon22 INT,@acon23 INT;
	DECLARE @cne0 int,@cne1 int,@cne2 int,@cne3 int,@cne4 int,@cne5 int,@cne6 int,@cne7 int,@cne8 int,@cne9 int,@cne10 int,@cne11 int,@cne12 int, @cne13 int,@cne14 int,@cne15 int,@cne16 int,@cne17 int,@cne18 int,@cne19 int,@cne20 int,@cne21 int,@cne22 int,@cne23 int;
	DECLARE @con0 int,@con1 int,@con2 int,@con3 int,@con4 int,@con5 int,@con6 int,@con7 int,@con8 int,@con9 int,@con10 int,@con11 int,@con12 int, @con13 int,@con14 int,@con15 int,@con16 int,@con17 int,@con18 int,@con19 int,@con20 int,@con21 int,@con22 int,@con23 int;
	DECLARE @tot0 int,@tot1 int,@tot2 int,@tot3 int,@tot4 int,@tot5 int,@tot6 int,@tot7 int,@tot8 int,@tot9 int,@tot10 int,@tot11 int,@tot12 int, @tot13 int,@tot14 int,@tot15 int,@tot16 int,@tot17 int,@tot18 int,@tot19 int,@tot20 int,@tot21 int,@tot22 int,@tot23 int;
	DECLARE @resultTOT1 decimal(12,2),@resultTOT2 decimal(12,2),@resultTOT3 decimal(12,2),@resultTOT4 decimal(12,2),@resultTOT5 decimal(12,2),@resultTOT6 decimal(12,2),@resultTOT7 decimal(12,2),@resultTOT8 decimal(12,2),@resultTOT9 decimal(12,2),@resultTOT10 decimal(12,2),@resultTOT11 decimal(12,2),@resultTOT12 decimal(12,2), @resultTOT13 decimal(12,2),@resultTOT14 decimal(12,2),@resultTOT15 decimal(12,2),@resultTOT16 decimal(12,2),@resultTOT17 decimal(12,2),@resultTOT18 decimal(12,2),@resultTOT19 decimal(12,2),@resultTOT20 decimal(12,2),@resultTOT21 decimal(12,2),@resultTOT22 decimal(12,2),@resultTOT23 decimal(12,2),@resultTOT24 decimal(12,2);


	DECLARE @i INT = 1;
	DECLARE @sumACNE INT;
	DECLARE @sumACON INT;
	DECLARE @sumATOT INT;
	DECLARE @sumCNE INT;
	DECLARE @sumCON INT;
	DECLARE @sumTOT INT;
	DECLARE @currentResultTOT DECIMAL(12, 2);
	
	DECLARE @CurrentDate AS Date = GETDATE();

	SET @acne0 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne1 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne2 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne3 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne4 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne5 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne6 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne7 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne8 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne9 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne10 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne11 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne12 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne13 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne14 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne15 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne16 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne17 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne18 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne19 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne20 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne21 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne22 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);
	SET @acne23 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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'), 0);

	SET @acon0 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon1 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon2 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon3 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon4 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon5 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon6 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon7 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon8 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon9 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon10 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon11 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon12 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon13 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon14 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon15 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon16 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon17 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon18 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon19 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon20 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon21 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon22 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);
	SET @acon23 = ISNULL((SELECT SUM(CASE When tc.Subcategorie = 'Accident de munca CON' 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'), 0);

	SET @cne0 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, @CurrentDate) AND Month = 'January'),0);
	SET @cne1 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-1,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-1,@CurrentDate)))),0);
	SET @cne2 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-2,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-2,@CurrentDate)))),0);
	SET @cne3 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-3,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-3,@CurrentDate)))),0);
	SET @cne4 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-4,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-4,@CurrentDate)))),0);
	SET @cne5 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-5,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-5,@CurrentDate)))),0);
	SET @cne6 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-6,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-6,@CurrentDate)))),0);
	SET @cne7 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-7,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-7,@CurrentDate)))),0);
	SET @cne8 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-8,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-8,@CurrentDate)))),0);
	SET @cne9 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-9,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-9,@CurrentDate)))),0);
	SET @cne10 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-10,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-10,@CurrentDate)))),0);
	SET @cne11 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-11,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-11,@CurrentDate)))),0);
	SET @cne12 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-12,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-12,@CurrentDate)))),0);
	SET @cne13 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-13,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-13,@CurrentDate)))),0);
	SET @cne14 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-14,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-14,@CurrentDate)))),0);
	SET @cne15 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-15,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-15,@CurrentDate)))),0);
	SET @cne16 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-16,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-16,@CurrentDate)))),0);
	SET @cne17 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-17,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-17,@CurrentDate)))),0);
	SET @cne18 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-18,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-18,@CurrentDate)))),0);
	SET @cne19 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-19,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-19,@CurrentDate)))),0);
	SET @cne20 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-20,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-20,@CurrentDate)))),0);
	SET @cne21 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-21,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-21,@CurrentDate)))),0);
	SET @cne22 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-22,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-22,@CurrentDate)))),0);
	SET @cne23 = ISNULL((SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-23,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-23,@CurrentDate)))),0);

	SET @con0 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, @CurrentDate) AND Month = 'January'),0);
	SET @con1 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-1,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-1,@CurrentDate)))),0);
	SET @con2 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-2,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-2,@CurrentDate)))),0);
	SET @con3 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-3,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-3,@CurrentDate)))),0);
	SET @con4 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-4,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-4,@CurrentDate)))),0);
	SET @con5 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-5,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-5,@CurrentDate)))),0);
	SET @con6 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-6,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-6,@CurrentDate)))),0);
	SET @con7 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-7,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-7,@CurrentDate)))),0);
	SET @con8 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-8,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-8,@CurrentDate)))),0);
	SET @con9 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-9,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-9,@CurrentDate)))),0);
	SET @con10 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-10,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-10,@CurrentDate)))),0);
	SET @con11 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-11,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-11,@CurrentDate)))),0);
	SET @con12 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-12,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-12,@CurrentDate)))),0);
	SET @con13 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-13,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-13,@CurrentDate)))),0);
	SET @con14 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-14,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-14,@CurrentDate)))),0);
	SET @con15 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-15,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-15,@CurrentDate)))),0);
	SET @con16 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-16,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-16,@CurrentDate)))),0);
	SET @con17 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-17,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-17,@CurrentDate)))),0);
	SET @con18 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-18,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-18,@CurrentDate)))),0);
	SET @con19 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-19,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-19,@CurrentDate)))),0);
	SET @con20 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-20,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-20,@CurrentDate)))),0);
	SET @con21 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-21,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-21,@CurrentDate)))),0);
	SET @con22 = ISNULL((SELECT ValueCON FROM tblOmOra WHERE Year=DATEPART(YEAR, DATEADD(MONTH,-22,@CurrentDate)) AND Month=CONVERT(nvarchar,DATENAME(MONTH, DATEADD(MONTH,-22,@CurrentDate)))),0);
	SET @con23 = ISNULL((SELECT ValueCON 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 @sumACNE = @sumACNE + CASE @i
			WHEN 1 THEN @acne0
			WHEN 2 THEN @acne1
			WHEN 3 THEN @acne2
			WHEN 4 THEN @acne3
			WHEN 5 THEN @acne4
			WHEN 6 THEN @acne5
			WHEN 7 THEN @acne6
			WHEN 8 THEN @acne7
			WHEN 9 THEN @acne8
			WHEN 10 THEN @acne9
			WHEN 11 THEN @acne10
			WHEN 12 THEN @acne11
			WHEN 13 THEN @acne12
			WHEN 14 THEN @acne13
			WHEN 15 THEN @acne14
			WHEN 16 THEN @acne15
			WHEN 17 THEN @acne16
			WHEN 18 THEN @acne17
			WHEN 19 THEN @acne18
			WHEN 20 THEN @acne19
			WHEN 21 THEN @acne20
			WHEN 22 THEN @acne21
			WHEN 23 THEN @acne22
			WHEN 24 THEN @acne23
		END;
		
		SET @sumACON = @sumACON + CASE @i
			WHEN 1 THEN @acon0
			WHEN 2 THEN @acon1
			WHEN 3 THEN @acon2
			WHEN 4 THEN @acon3
			WHEN 5 THEN @acon4
			WHEN 6 THEN @acon5
			WHEN 7 THEN @acon6
			WHEN 8 THEN @acon7
			WHEN 9 THEN @acon8
			WHEN 10 THEN @acon9
			WHEN 11 THEN @acon10
			WHEN 12 THEN @acon11
			WHEN 13 THEN @acon12
			WHEN 14 THEN @acon13
			WHEN 15 THEN @acon14
			WHEN 16 THEN @acon15
			WHEN 17 THEN @acon16
			WHEN 18 THEN @acon17
			WHEN 19 THEN @acon18
			WHEN 20 THEN @acon19
			WHEN 21 THEN @acon20
			WHEN 22 THEN @acon21
			WHEN 23 THEN @acon22
			WHEN 24 THEN @acon23
		END;

		SET @sumATOT = @sumATOT + CASE @i
			WHEN 1 THEN @acne0+@acon0
			WHEN 2 THEN @acne1+@acon1
			WHEN 3 THEN @acne2+@acon2
			WHEN 4 THEN @acne3+@acon3
			WHEN 5 THEN @acne4+@acon4
			WHEN 6 THEN @acne5+@acon5
			WHEN 7 THEN @acne6+@acon6
			WHEN 8 THEN @acne7+@acon7
			WHEN 9 THEN @acne8+@acon8
			WHEN 10 THEN @acne9+@acon9
			WHEN 11 THEN @acne10+@acon10
			WHEN 12 THEN @acne11+@acon11
			WHEN 13 THEN @acne12+@acon12
			WHEN 14 THEN @acne13+@acon13
			WHEN 15 THEN @acne14+@acon14
			WHEN 16 THEN @acne15+@acon15
			WHEN 17 THEN @acne16+@acon16
			WHEN 18 THEN @acne17+@acon17
			WHEN 19 THEN @acne18+@acon18
			WHEN 20 THEN @acne19+@acon19
			WHEN 21 THEN @acne20+@acon20
			WHEN 22 THEN @acne21+@acon21
			WHEN 23 THEN @acne22+@acon22
			WHEN 24 THEN @acne23+@acon23
		END;

		SET @sumCNE = @sumCNE + CASE @i
			WHEN 1 THEN @cne0
			WHEN 2 THEN @cne1
			WHEN 3 THEN @cne2
			WHEN 4 THEN @cne3
			WHEN 5 THEN @cne4
			WHEN 6 THEN @cne5
			WHEN 7 THEN @cne6
			WHEN 8 THEN @cne7
			WHEN 9 THEN @cne8
			WHEN 10 THEN @cne9
			WHEN 11 THEN @cne10
			WHEN 12 THEN @cne11
			WHEN 13 THEN @cne12
			WHEN 14 THEN @cne13
			WHEN 15 THEN @cne14
			WHEN 16 THEN @cne15
			WHEN 17 THEN @cne16
			WHEN 18 THEN @cne17
			WHEN 19 THEN @cne18
			WHEN 20 THEN @cne19
			WHEN 21 THEN @cne20
			WHEN 22 THEN @cne21
			WHEN 23 THEN @cne22
			WHEN 24 THEN @cne23
			
		END;

		SET @sumCON = @sumCON + CASE @i
			WHEN 1 THEN @con0
			WHEN 2 THEN @con1
			WHEN 3 THEN @con2
			WHEN 4 THEN @con3
			WHEN 5 THEN @con4
			WHEN 6 THEN @con5
			WHEN 7 THEN @con6
			WHEN 8 THEN @con7
			WHEN 9 THEN @con8
			WHEN 10 THEN @con9
			WHEN 11 THEN @con10
			WHEN 12 THEN @con11
			WHEN 13 THEN @con12
			WHEN 14 THEN @con13
			WHEN 15 THEN @con14
			WHEN 16 THEN @con15
			WHEN 17 THEN @con16
			WHEN 18 THEN @con17
			WHEN 19 THEN @con18
			WHEN 20 THEN @con19
			WHEN 21 THEN @con20
			WHEN 22 THEN @con21
			WHEN 23 THEN @con22
			WHEN 24 THEN @con23
		END;

		SET @sumTOT = @sumTOT + CASE @i
			WHEN 1 THEN @cne0+@con0
			WHEN 2 THEN @cne1+@con1
			WHEN 3 THEN @cne2+@con2
			WHEN 4 THEN @cne3+@con3
			WHEN 5 THEN @cne4+@con4
			WHEN 6 THEN @cne5+@con5
			WHEN 7 THEN @cne6+@con6
			WHEN 8 THEN @cne7+@con7
			WHEN 9 THEN @cne8+@con8
			WHEN 10 THEN @cne9+@con9
			WHEN 11 THEN @cne10+@con10
			WHEN 12 THEN @cne11+@con11
			WHEN 13 THEN @cne12+@con12
			WHEN 14 THEN @cne13+@con13
			WHEN 15 THEN @cne14+@con14
			WHEN 16 THEN @cne15+@con15
			WHEN 17 THEN @cne16+@con16
			WHEN 18 THEN @cne17+@con17
			WHEN 19 THEN @cne18+@con18
			WHEN 20 THEN @cne19+@con19
			WHEN 21 THEN @cne20+@con20
			WHEN 22 THEN @cne21+@con21
			WHEN 23 THEN @cne22+@con22
			WHEN 24 THEN @cne23+@con23
		END;

		-- Perform calculation and store the result
		SET @currentResultTOT = CONVERT(DECIMAL(12,2), (CONVERT(DECIMAL(12,2),@sumATOT) * 200000) / CONVERT(DECIMAL(12,2),@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;

in code behing I get the data with

List yValues = new List();


AddIfNotNull(yValues, rdr["ResultTOT1"]);
AddIfNotNull(yValues, rdr["ResultTOT2"]);
AddIfNotNull(yValues, rdr["ResultTOT3"]);
AddIfNotNull(yValues, rdr["ResultTOT4"]);
AddIfNotNull(yValues, rdr["ResultTOT5"]);
AddIfNotNull(yValues, rdr["ResultTOT6"]);
AddIfNotNull(yValues, rdr["ResultTOT7"]);
AddIfNotNull(yValues, rdr["ResultTOT8"]);
AddIfNotNull(yValues, rdr["ResultTOT9"]);
AddIfNotNull(yValues, rdr["ResultTOT10"]);
AddIfNotNull(yValues, rdr["ResultTOT11"]);
AddIfNotNull(yValues, rdr["ResultTOT12"]);
AddIfNotNull(yValues, rdr["ResultTOT13"]);
AddIfNotNull(yValues, rdr["ResultTOT14"]);
AddIfNotNull(yValues, rdr["ResultTOT15"]);
AddIfNotNull(yValues, rdr["ResultTOT16"]);
AddIfNotNull(yValues, rdr["ResultTOT17"]);
AddIfNotNull(yValues, rdr["ResultTOT18"]);
AddIfNotNull(yValues, rdr["ResultTOT19"]);
AddIfNotNull(yValues, rdr["ResultTOT20"]);
AddIfNotNull(yValues, rdr["ResultTOT21"]);
AddIfNotNull(yValues, rdr["ResultTOT22"]);
AddIfNotNull(yValues, rdr["ResultTOT23"]);
AddIfNotNull(yValues, rdr["ResultTOT24"]);

Marius Vasile1y ago
In tblOmOra Year is int and Month is string, that's why I was trying to use
```
Month=CONVERT(nvarchar,DATENAME(MONTH,@CurrentDate));
```
I tested this and is working in SSMS
0
Jignesh Kumar1y ago
Hello Marius,

You can use this and give a try,
```
SET @cne0 = ISNULL((SELECT ValueCNE FROM tblOmOra 
                    WHERE Year = DATEPART(YEAR, @CurrentDate) 
                    AND Month = DATENAME(MONTH, @CurrentDate)), 0);
```
Note : Have you cheked, How your month field is stored in tblOmOra

Check this part in SET "AND Month=CONVERT(nvarchar,DATENAME(MONTH,@CurrentDate))"
0
Marius Vasile1y ago
Thank you Jignesh for your answer. What am I trying to do is put in a Chart monthly values for current dateuntil 23 months before. I did this for one year period with the same approach I mentioned here but now I am facing difficulties. See resolve here https://www.csharp.com/forums/looping-through-int-values-in-a-stored-procedure.
0

Jignesh Kumar1y ago

Hello marius,

You want to get all 23 years data combined, you can use this query,

DECLARE @CurrentDate AS DATETIME = GETDATE();
DECLARE @StartDate AS DATE = DATEADD(MONTH, -23, @CurrentDate);
DECLARE @EndDate AS DATE = EOMONTH(@CurrentDate);

SELECT * FROM YourTable tr With(NOLOCK)
                  WHERE tr.DataInitierii >= @StartDate
                  AND tr.DataInitierii <= @EndDate;

You want to get month wise data,

DECLARE @CurrentDate AS DATETIME = GETDATE();
DECLARE @Counter INT = 0;

WHILE @Counter <= 23
BEGIN
    DECLARE @MonthStart DATE = DATEADD(MONTH, -@Counter, DATEFROMPARTS(YEAR(@CurrentDate), MONTH(@CurrentDate), 1));
    DECLARE @MonthEnd DATE = EOMONTH(@MonthStart);

    PRINT 'Fetching data for: ' + CAST(@MonthStart AS VARCHAR(10)) + ' to ' + CAST(@MonthEnd AS VARCHAR(10));

    SELECT * FROM YourTable tr With(NoLock)
    WHERE tr.DataInitierii BETWEEN @MonthStart AND @MonthEnd;

    SET @Counter = @Counter + 1;
END;

Marius Vasile1y ago
I found the faulty part after I checked bit by bit in SSMS. It seems that
```
SET @cne0 = (SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, @CurrentDate) AND Month=CONVERT(nvarchar,DATENAME(MONTH,@CurrentDate)));
```
returns NULL. But when I run the query in SSMS
```
SELECT ValueCNE FROM tblOmOra WHERE Year=DATEPART(YEAR, @CurrentDate) AND Month=CONVERT(nvarchar,DATENAME(MONTH,@CurrentDate));
```
I get the correct value. Can anyone tell me what is wrong in the SET query? @cne0 isw declared as int, value from database is int
0

Marius Vasile1y ago

Thank you Sophia, I have to compare by year and date, see example of a full code below

SET @acne1 = (SELECT CONVERT(decimal(6,2),SUM(CASE When tc.Subcategorie = 'Accident de munca CNE' 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 = @Spec);

I have an aggregate function for each month and because the range may even strech in three different years I have to compare based on year and month

Sophia Carter1y ago
It seems like you are on the right track with your SQL queries to retrieve data for the current month and the previous months. However, the issue you are encountering with getting NULL results despite having data in the database might be due to the way you are comparing dates.

When dealing with date parts in SQL Server, it can be more reliable to compare the full date values rather than individual parts like year and month. Here are a few considerations and suggestions to tackle this issue:

1. Full Date Comparison: Instead of comparing only the year and month parts of the date, you can try matching the full date to ensure accuracy. For example:
```
   WHERE CONVERT(DATE, tr.DataInitierii) = DATEADD(MONTH, -1, CONVERT(DATE, @CurrentDate))
```
2. Debugging Query: To investigate further, you can try running queries to check for data specifically from the previous months to see if the issue lies within the comparison conditions.

3. Data Type Compatibility: Ensure that the data type of the columns (like `tr.DataInitierii`) and variables (like `@CurrentDate`) you are comparing are compatible. If there is a mismatch, it can lead to unexpected results.

4. Handling Time Component: If `tr.DataInitierii` includes a time component along with the date, you might want to compare the date part only to avoid precision issues.

5. Testing with Simplified Queries: To isolate the problem, you can simplify your queries and conditions to focus on retrieving data for a specific month (e.g., the previous month). This can help in identifying any discrepancies.

By ensuring that your date conditions match correctly, you should be able to retrieve the desired data spanning across the last 24 months effectively. If you encounter further challenges or need more specific assistance with your SQL queries, feel free to provide additional details for a more targeted solution.
+1