Doubt - SQL Server Dates

Question

Doubt - SQL Server Dates

Navigation

#1 by (1 votes)

0

In the script below when a given task is completed the other will open on the first business day of the subsequent month.

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

Obs : I already have a working day 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;

sql-server query script

asked by anonymous 25.09.2017 / 13:56

1 answer

Dropdown menu with javascript Script under 18

score 1 · Accepted Answer

I've created a function to get the 1st business 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 function declare a variable to store the first business day of the subsequent month. Call the function by passing the +1 month closing date as a parameter:

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

Once you've done this, you just have to update your IF to the stored date:

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