Add year to my sql search clause

Question

I set up a trial that does a search for the amount of hours pointed out per month of each employee of the company. The trial works well, but I noticed that she ends up looking for years past and I would like to add another YEAR clause in this search, only I’m not able to assemble.

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[SP_RELATORIO_FUNCIONARIOS] 

-----------------PARAMETROS DA PROCEDURE----------------------------------------------------------------------
@ID_DEPARTAMENTO AS BIGINT

AS
BEGIN
BEGIN TRY

----- INIALIZA VARIAVEIS----------------------------------------------
DECLARE @ID_FUNCIONARIO AS BIGINT
DECLARE @NOME AS VARCHAR (50)
DECLARE @JAN AS NUMERIC  (18,0)
DECLARE @FEV AS NUMERIC  (18,0)
DECLARE @MAR AS NUMERIC  (18,0)
DECLARE @ABR AS NUMERIC  (18,0)
DECLARE @MAI AS NUMERIC  (18,0)
DECLARE @JUN AS NUMERIC  (18,0)
DECLARE @JUL AS NUMERIC  (18,0)
DECLARE @AGO AS NUMERIC  (18,0)
DECLARE @SETE AS NUMERIC  (18,0)
DECLARE @OUTU AS NUMERIC  (18,0)
DECLARE @NOV AS NUMERIC  (18,0)
DECLARE @DEZ AS NUMERIC  (18,0)

CREATE TABLE #TABELA_RELATORIO_FUNCIONARIOS(

NOME VARCHAR(50),
JAN  NUMERIC (18,0),
FEV  NUMERIC (18,0),
MAR  NUMERIC (18,0),
ABR  NUMERIC (18,0),
MAI  NUMERIC (18,0),
JUN  NUMERIC (18,0),
JUL  NUMERIC (18,0),
AGO  NUMERIC (18,0),
SETE NUMERIC (18,0),
OUTU NUMERIC (18,0),
NOV  NUMERIC (18,0),
DEZ  NUMERIC (18,0),

)
DECLARE INFO_RELATORIO_FUNCIONARIO_CURSOR CURSOR FOR 

SELECT ID_FUNCIONARIO, NOME  
FROM TB_FUNCIONARIO
WHERE ID_DEPARTAMENTO=@ID_DEPARTAMENTO ORDER BY NOME

OPEN INFO_RELATORIO_FUNCIONARIO_CURSOR
FETCH NEXT FROM INFO_RELATORIO_FUNCIONARIO_CURSOR
INTO @ID_FUNCIONARIO ,@NOME 
WHILE @@FETCH_STATUS = 0  
BEGIN




(SELECT @JAN=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=1)

(SELECT @FEV=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=2 )

(SELECT @MAR=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=3 )

(SELECT @ABR=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=4 )

(SELECT @MAI=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=5)

(SELECT @JUN=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=6)

(SELECT @JUL=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=7)

(SELECT @AGO =COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS  WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=8)

(SELECT @SETE=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS  WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=9 )

(SELECT @OUTU=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS  WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=10)

(SELECT @NOV=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=11)

(SELECT  @DEZ= COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=12 )

INSERT INTO #TABELA_RELATORIO_FUNCIONARIOS(NOME,JAN,FEV,MAR,ABR,MAI,JUN,JUL,AGO,SETE,OUTU,NOV,DEZ)
VALUES (@NOME,@JAN,@FEV,@MAR,@ABR,@MAI,@JUN,@JUL,@AGO,@SETE,@OUTU,@NOV,@DEZ)

FETCH NEXT FROM INFO_RELATORIO_FUNCIONARIO_CURSOR
INTO @ID_FUNCIONARIO , @NOME 

END

CLOSE INFO_RELATORIO_FUNCIONARIO_CURSOR
DEALLOCATE INFO_RELATORIO_FUNCIONARIO_CURSOR

SELECT * FROM #TABELA_RELATORIO_FUNCIONARIOS
DROP TABLE #TABELA_RELATORIO_FUNCIONARIOS
END TRY
BEGIN CATCH


END CATCH





END

Answer 1

Include an input parameter @YEAR as INT, and then Filter in the same way you did with the month (Month) but put the variable instead of the fixed values for example like this:

(SELECT @JAN=COALESCE(SUM( HR_EFETIVA),0) FROM TB_APONTAMENTO_HORAS WHERE ID_FUNCIONARIO=@ID_FUNCIONARIO AND MONTH(DT_FIM)=1 AND YEAR(DT_FIM)=@YEAR)