SQL - Subtract Business Days from Date

I am trying to query my table for records where a date field is between 10 Business Days ago and Today. I am struggling to calculate the date 10 business days ago.

In my case, Holidays don't matter. I only need to include Monday - Friday regardless of holiday / working day.

I found this SQL code which does what I want, but backwards. This calculates the number of business days between two dates and I need to subtract a number and get to a date.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2019-08-26'
SET @EndDate = GETDATE()

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

I've tried using DATEADD but can't figure out any logic to exclude Saturday and Sunday. I also don't want to use a function is I don't absolutely have to. I have not been able to find anything regarding DATEADD online without a function that loops through each day.

Desired Functionality:

START DATE: TODAY / GETDATE()
NUMBER OF BUSINESS DAYS: 10

DATEADD(DAY, -10 + CALULATE WEEKEND DAYS?, GETDATE())

Solution

Since you only care about weekends and not other holidays, it is easy to calculate the number of calendar days from the business days, using the knowledge that there are 5 business days in every calendar week (7 days). If you are OK with a scalar function you can use the following:

CREATE FUNCTION SubBusinessDays(
    @days int,
    @date datetime
) RETURNS int
BEGIN
    SET @days = @days-1; -- number of days are inclusive of the start date

    -- add full weeks and adjust for start date in weekend
    SET @date = DATEADD(DAY, -(@days / 5 * 7) +
                    (CASE (DATEPART(WEEKDAY, @date) + @@DATEFIRST) % 7 
                                      WHEN 0 THEN -1 
                                      WHEN 1 THEN -2 
                                      ELSE 0 END), @date);
    -- at this point @date is a work day

    SET @days = @days % 5;
    RETURN DATEADD(DAY, -@days 
                    + (CASE WHEN (DATEPART(WEEKDAY, @date) + @@DATEFIRST) % 7 - @days < 2 -- remaining days overlap weekend
                          THEN -2 ELSE 0 END), @date)
    
END

If on the other hand you need to work on a table you can use CROSS APPLY to do the same in steps as in the following sample:

DECLARE @t TABLE(StartDate Datetime, BDays int)

INSERT INTO @t
SELECT d, ofs
FROM (VALUES ('20190906'), ('20190907'), ('20190908'), ('20190909'), ('20190910')) AS sd(d)
    CROSS JOIN 
     (VALUES (7), (8), (9), (10), (11)) AS bd(ofs)


SELECT StartDate, BDays, EndDate
FROM @t
    CROSS APPLY (SELECT BDaysMinus1 = BDays-1) x1
    CROSS APPLY (SELECT EndDateTemp = DATEADD(DAY, 
                                        -((BDaysMinus1 / 5) * 7) 
                                        +(CASE (DATEPART(WEEKDAY, StartDate) + @@DATEFIRST) % 7  
                                            WHEN 0 THEN -1 
                                            WHEN 1 THEN -2 
                                            ELSE 0 END), StartDate),
                        RemainingDays = BDaysMinus1 % 5) x2
    CROSS APPLY (SELECT EndDate = DATEADD(DAY, -RemainingDays +(CASE WHEN (DATEPART(WEEKDAY, EndDateTemp) + @@DATEFIRST) % 7 - RemainingDays < 2 
                                        THEN -2 ELSE 0 END), EndDateTemp)) x3