CODEWITHSUNDEEP
sql-servert-sqlstored-functions
Benzz
Benzz

Reputation: 119

SQL Function check if a date is between 2 dates from another table

I am using a function which currently calculates the SLA between two dates passed in, this checks for NULL items and returns 0.

I now need to take into account the dates inside a new table 'NonBusinessDays' and take these off the total SLA value.

How would I check if the two passed in values contain any dates between their range from the other tables dates, if they do they need to take off that date from the SLA total.

Currently the table sits like;

CREATE TABLE #NonBusinessDays(
    [id] [uniqueidentifier] NOT NULL,
    [date] [date] NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [type] [nvarchar](50) NOT NULL)
GO

INSERT INTO #NonBusinessDays
           ([id]
           ,[date]
           ,[name]
           ,[type])
     VALUES
           ('1DF6A335-56F8-4DA7-B9C3-07DBAC9FBA8D'
           ,'2019-08-26'
           ,'Summer bank holiday'
           ,'PublicHoliday')

           ('0940FDC9-A875-48C5-BED8-0ABFA3AA5AE8',
            '2018-05-07',
            'Early May bank holiday',
            'PublicHoliday')
GO

So my Function is like this;

CREATE FUNCTION dateSLA
(
@date1 DATETIME,
@date2 DATETIME
)
returns INT
AS 
BEGIN

declare @days INT
set @days = 0

      IF @date1 IS NULL AND @date2 IS NULL
            BEGIN
            GOTO Final
      END
      ELSE IF @date1 IS NOT NULL AND @date2 IS NOT NULL
      BEGIN


            SET @days = DATEDIFF(DAY, CONVERT(DATE, @date1, 103), CONVERT(DATE, @date2, 103))

            GOTO Final
      END

Final:

return @days

END
GO

I would like it to check if any dates inside the NonBusinessDays Table are present between the parameters passed into the Function then it would take however many days off the SLA.

Example, if two dates from the BusinessDays exist between the passed in the parameters then this would take 2 off. So the SLA calcualted at 10 but this would make it 8 due to 2 non-business days were present.

Any guidance would be appreciated, if you require any more information please let me know and I will amend.

Upvotes: 2

Views: 2610

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

We can reduce this function considerably and take into account the non working days:

CREATE FUNCTION dateSLA
(
@date1 DATETIME,
@date2 DATETIME
)
returns INT
AS 
BEGIN

return COALESCE(
  DATEDIFF(day,@date1,@date2) -
  (select COUNT(*) from #NonBusinessDays
   where date between CAST(@date1 as date) and CAST(@date2 as date)),
  0)

END
GO

If either @date1 or @date2 is null then the datediff will return null, the subtraction will leave null and finally the coalesce turns this into a 0.

DATEDIFF counts boundaries There are just as many day boundaries (transitions at midnight) between two datetimes and the same values with the time portion removed. So we don't need them as dates for the DATEDIFF call. We do need such for matching a non-working day on the start date, so we retain the CAST there, and also because of the data type mismatch and for symmetry we do the same for the end date.

I'm ignoring what happens when @date2 is less than @date1, which is presumably an invalid input to this function anyway.

As I said in a comment, I'd prefer a full calendar table here where we could just do the COUNT(*) operation against the working days, but understand it's not an option at this time for you. (At which point you start questioning the need for a function at all)

Upvotes: 2

Related Questions