CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
kjohnson
kjohnson

Reputation: 139

Subtract Business Days Using Date Dimension Calendar

Lots of comments suggested creating a date table in SQL Server for business day and holiday calculations - excellent idea! I found a great how-to article to create such a set of tables in SQL Server - https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/. I highly recommend this article if you need to do lots of date calculations.

From those tables, I created a view called USCalendar that calculates the end date, given a start date (03/01/21) and number of business days (180) - works perfectly!

select Top 1 TheDate as EndDate
from (
    select Top 180 TheDate from USCalendar
    Where TheDate>='03/01/2021'
    And IsWeekend=0 and IsHoliday=0
    Order By TheDate
) as BusinessDays
Order By TheDate DESC

My only challenge is coming up with a way to reverse it - how do I find the start date if I know the target end date and number of business days?

Upvotes: 1

Views: 687

Answers (1)

Charlieface
Charlieface

Reputation: 72050

You don't actually need all that if you want just the one date.

You could do simply

select TheDate as EndDate
from USCalendar
Where TheDate >= '03/01/2021'
  And IsWeekend = 0 and IsHoliday = 0
Order By TheDate
OFFSET 179 ROWS
FETCH NEXT 1 ROW ONLY;

To reverse this, we change it to <= and order in the other direction:

select TheDate as EndDate
from USCalendar
Where TheDate <= '03/01/2021'
  And IsWeekend = 0 and IsHoliday = 0
Order By TheDate DESC
OFFSET 179 ROWS
FETCH NEXT 1 ROW ONLY;

I would advise you to make this an inline Table-Valued Function, so that you can put any date in. It basically becomes a parameterized view. It is inlined into the outer query, so it's very efficient:

CREATE /* OR ALTER */ FUNCTION dbo.GetBusinessEndDate
( @StartDate date, @numberOfDays int )
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    select TheDate as EndDate
    from USCalendar
    Where TheDate >= @StartDate
      And IsWeekend = 0 and IsHoliday = 0
    Order By TheDate
    OFFSET @numberOfDays ROWS
    FETCH NEXT 1 ROW ONLY
);

You use this like any other table or view, you can also CROSS APPLY it.

I would advise to make a separate function for StartDate, do not attempt to combine it.

Upvotes: 1

Related Questions