Add year in my sql search clause

1

I've set up a procedure that does a search for the number of hours pointed per month of each company official. The procedure works fine, but I noticed that it ends up searching from years past and I would like to add another clause YEAR in this search, but I am not able to mount.

-- =============================================
-- 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
    
asked by anonymous 13.09.2017 / 20:37

1 answer

1

Include an input parameter @YEAR as INT, and then filter 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)

    
13.09.2017 / 22:47