Doubt - SQL Server Dates

Question

In the script below when one task is completed the other will open on the first working day of the following month.

Example: Task 10 was completed on the day 18-11-2016, then another will be generated in the day 01-12-2016. The field that will have that date is the EstTarData as it is in the script where I put getdate() + 30.

Obs: I also already have a useful days function calculating correctly.

Function:

USE [0800net_PRD]
GO
/****** Object:  UserDefinedFunction [dbo].[dias_uteis]    Script Date: 25/09/2017 10:11:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION  [dbo].[dias_uteis]  (@DATA_INICIAL DATETIME, @DATA_FINAL 

DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @CONT INT 

    SET @DATA_INICIAL = (SELECT CAST(@DATA_INICIAL AS DATE) )
    SET @DATA_FINAL = (SELECT CAST(@DATA_FINAL AS DATE ))
    SET @CONT = 0

    WHILE (@DATA_INICIAL <= @DATA_FINAL)
     BEGIN
        IF (DATEPART(DW, @DATA_INICIAL ) IN(1,7) OR @DATA_INICIAL IN (SELECT CAST(FerData AS DATE) FROM Feriado))
         BEGIN
          SET @DATA_INICIAL = @DATA_INICIAL+1
         END

        IF DATEPART(DW, @DATA_INICIAL ) NOT IN(1,7) 
         BEGIN
          SET @CONT = @CONT+1
          SET @DATA_INICIAL = @DATA_INICIAL+1
         END 
     END
RETURN @CONT
END


  

      IF (SELECT TarStatus FROM Tarefa  WHERE TarID = @Tarefa) = 9  
      BEGIN 
          UPDATE EstoqueTarefa SET Ativo = 1 WHERE EstTarID IN (246,569) and EstTarData = getdate() + 30  END  
      END; 

Answer 1

I created a function to take the 1 working day of the month:

CREATE FUNCTION  [dbo].[PrimeiroDiaUtilMes]  (@DATA DATETIME)
RETURNS DATETIME
AS
BEGIN
    SET @DATA = (SELECT CAST(DATEADD(m, DATEDIFF(m, 0, @DATA), 0) AS DATE) )

    WHILE (DATEPART(DW, @DATA ) IN(1,7) OR @DATA IN (SELECT CAST(FerData AS DATE) FROM Feriado))
     BEGIN
        SET @DATA = @DATA+1
     END
RETURN @DATA
END

After creating the declare function a variable to store the first working day of the following month. Call the function by setting the end date +1 month:

DECLARE @DiaUtil DATETIME
SET @DiaUtil = (SELECT  dbo.PrimeiroDiaUtilMes(DATEADD(month, 1, TarFechamento) FROM Tarefa WHERE TarID = @Tarefa)

Done this just by passing your update IF the date stored:

IF (SELECT TarStatus FROM Tarefa  WHERE TarID = @Tarefa) = 9  
BEGIN 
   UPDATE EstoqueTarefa 
   SET Ativo = 1 
   WHERE EstTarID IN (246,569) 
   AND EstTarData IN = @DiaUtil
END