SQL Server What is the most efficient way to calculate a future date in SQL based on a number of working days without loops?

cgvd09ve 于 2023-06-04 发布在其他

关注(0)|答案(1)|浏览(148)

Calculate a number of working days in SQL and return the date without using Loops.

I need to create a function in Microsoft SQL server management studio that will take in a date and a number of working days as a variable and then return an end date that is however many working days in the future.

I have achieved this using a loop but was wondering if there is a more efficient way of achieving this?

The function is as follows:

[dbo].[AddWorkingDays]
(
@originaldate date,
@daystoadd INT
)
RETURNS date
AS
BEGIN
    -- Declare the return variable here
Declare @days INT;
Declare @counter INT;
Declare @duedate date;

SET @days = 1
SET @counter = 0 

WHILE (@counter < @daystoadd)
        BEGIN
            set @duedate = dateadd(DAY,@days,@originaldate)
                IF @duedate Not in (SELECT [date-off] from [Non-working-days-table])
            BEGIN    
                SET @counter = @counter + 1  
            END
            BEGIN
                SET @days = @days +1
            END
        END

    -- Return the result of the function
    RETURN @duedate

END

sql-server

来源：https://stackoverflow.com/questions/76373842/what-is-the-most-efficient-way-to-calculate-a-future-date-in-sql-based-on-a-numb

1条答案

按热度按时间

dfty9e191#

Look at the approach to the task.See this example:

nwd - non-working-day. Original date(startdt) not included in count. It doesn't matter)

-- test data
create table nwd (date_off date);
insert into nwd values
('2023-06-03'),('2023-06-04'),('2023-06-10'),('2023-06-11'),('2023-06-14')
,('2023-06-17'),('2023-06-18'),('2023-06-19')
;

declare @startdt date;
set @startdt=cast('2023-05-30' as date);
declare @daystoadd int;
set @daystoadd=13;

-- query
with t1 as(
select @startdt startdt,date_off
  ,datediff(d,@startdt,date_off) dif
  ,row_number()over(order by date_off) nwdCnt
  ,datediff(d,@startdt,date_off) -row_number()over(order by date_off) workdays
from nwd
where date_off>@startdt
)
select max(date_off) maxdoff,max(workdays) mwd
  ,case when max(workdays) is not null then
         dateadd(d,@daystoadd-max(workdays),max(date_off)) 
   else dateadd(d,@daystoadd,@startdt)
   end targetdt
from t1
where workdays<@daystoadd;

CTE result

startdt	date_off	dif	nwdCnt	workdays
2023-05-30	2023-06-03	4	1	3
2023-05-30	2023-06-04	5	2	3
2023-05-30	2023-06-10	11	3	8
2023-05-30	2023-06-11	12	4	8
2023-05-30	2023-06-14	15	5	10
2023-05-30	2023-06-17	18	6	12
2023-05-30	2023-06-18	19	7	12
2023-05-30	2023-06-19	20	8	12

Target query result (draft) with startdt=2023-05-30 and daystoadd=13

startdt	maxdoff	mwd	targetdt
2023-05-30	2023-06-19	12	2023-06-20

Example

Upd1. Corrected to take into account the comment of user1191247 for dates before first non-working-day in nwd table.

赞(0）回复(0）举报 2023-06-04

我来回答

SQL Server What is the most efficient way to calculate a future date in SQL based on a number of working days without loops?

1条答案

相关问题

热门标签

最新问答