How to calculate end_date from start_date and duration in SQL Server Query

Question

I have a table with 4 fields .Start-date, Duration, End_date, Actual_date.

I am providing the start_date and duration as the input to calculate end-date.

While calculating the end_date the Saturday and Sundays are not considered.

Also if the actual_date is having value that value is saved as end_date.

Example : Start_date = 18/05/2020 , Duration : 10. So the End_date should be calculated by excluding 23/05/2020 and 24/05/2020.

I had tried different ways to archive this.

The below is the alter query for the column end_date.

This is meeting half of my requirement but dont know how to exclude the weekends (Saturday and Sunday) from the start_date and duration.

ALTER TABLE [DBName].[dbo].[TestDate] ADD End_Date as CAST(
CASE 
WHEN (Actual_Date='' or Actual_Date IS NULL) then  DATEADD(day, Duration, Start_Date)
ELSE Actual_Date
END as DATE
)
GO

Please help me on this.

Answer 1

I believe the code you are looking for is

ALTER TABLE [DBName].[dbo].[TestDate] ADD End_Date as  
    DATEADD(D, 
            7 * (Duration / 5) -- Business weeks as calendar days
            + CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) -- Move weekend start to Friday, if needed
            + (Duration % 5) -- Day outside of full business weeks
            +IIF(DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6, 2, 0) -- Weekend Jump
            , StartDate
        )
GO

Basically, this separates out the whole business weeks and the additional days beyond. However, if you consider those additional days to come at the beginning, you just need to know if they go past a Friday or not and if so add some days. To simplify that, we first move Sat/Sun to Friday (you can use 1,0,0,0,0,0,2, in both spots, to move forward to Monday instead), then add 2 days if we end up in the weekend.

If you want to QA the end result, this code will give you all the component parts to review and validate:

WITH TestStartDates AS (
    SELECT TOP (7) 
        DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT 1)), CAST('2020-05-18' AS DATE)) as StartDate
    FROM Sys.Columns
),
TestAddDays AS (
        SELECT TOP (45) 
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) Duration
    FROM Sys.Columns
)
SELECT 
    StartDate
    , DATENAME(DW, StartDate) as Weekday
    , CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) OffsetWeekend
    , DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1), StartDate) WeekendToFriday
    , Duration
    , (Duration / 5) as WeeksToAdd
    , (Duration % 5) DaysOverBusinessWeeks
    , DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) StartDateWeekdayNum
    , CASE WHEN DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6 THEN 2 ELSE 0 END InitialWeekendJump -- 8 = If we pass Friday, jump 2
    , DATEADD(D, 7 * (Duration / 5) + CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) + (Duration % 5) + IIF(DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6, 2, 0), StartDate) as EndDate
    , DATENAME(DW, DATEADD(D, 7 * (Duration / 5) + CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) + (Duration % 5) + IIF(DATEPART(dw, DATEADD(D, CHOOSE(DATEPART(WEEKDAY, StartDate),-2,0,0,0,0,0,-1) , StartDate)) + (Duration % 5) > 6, 2, 0), StartDate)) AS EndWeekDay
FROM TestAddDays CROSS JOIN TestStartDates
ORDER BY Duration, StartDate

Answer 2

Iterative solution:

WITH cte AS ( SELECT id, 
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, start_date) > 5 
                                       THEN 9 - DATEPART(WEEKDAY, start_date) 
                                       ELSE 1 
                                       END, start_date) cur_date, duration-1 reminder
              FROM test
              UNION ALL
              SELECT id, 
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, cur_date) > 5 
                                       THEN 3 
                                       ELSE 1 
                                       END, cur_date), 
                     reminder - 1
              FROM cte
              WHERE reminder > 0 )
UPDATE test
SET test.end_date = cte.cur_date
FROM cte
WHERE test.id = cte.id
  AND cte.reminder = 0;

fiddle

PD. It is optimal hardly... for large duration values you may int. divide it by 7 and add according weeks (5 days per each) then iterate by int. reminder only.

Answer 3

I have created the table:

use tempdb
go

if OBJECT_ID('dbo.testdate','U') is not null
   drop table dbo.testdate

create table [dbo].[TestDate] (
 actual_dt date null,
 Start_dt date null, 
 Duration int 
 )

then I create a function to do the dirty work for me:

--==============================================================================
GO
if OBJECT_ID('dbo.fnfoo','Fn') is not null
   drop FUNCTION dbo.fnfoo
go
CREATE FUNCTION dbo.fnfoo
( 
    @startdate date,
    @duration int
)
RETURNS date
AS
BEGIN

    declare
    --@startdate date ='3-MAY-2020',
    --@duration int = 20,
    @end_date date

    DECLARE @Radhe TABLE(I INT IDENTITY(1,1) 
                   NOT NULL PRIMARY KEY CLUSTERED, thedate date)

   SELECT @end_date  = DATEADD(DAY,@DURATION*2,@startdate)



;WITH dates_CTE (thedate) as 
(
  SELECT DATEADD(DAY,-3,@startdate)
  UNION ALL
  SELECT DATEADD(day, 1, thedate)
    FROM dates_CTE
   WHERE thedate <= @end_date 

)     
INSERT INTO @Radhe
SELECT thedate 
FROM dates_CTE
WHERE DATEPART(dw, [thedate]) NOT IN (1,7) --not a weekend
OPTION (MAXRECURSION 0)

    RETURN (
                SELECT W.THEDATE
                FROM @RADHE W 
                WHERE W.I = 
                (
                SELECT TOP 1 R.I+@DURATION-1
                FROM @RADHE R
                WHERE R.thedate >= @startdate
                ORDER BY R.I ASC
                )
    )
END
go
--==============================================================================
GO

and then I use that function inside the computed column in the following alter table:

GO
ALTER TABLE [dbo].[TestDate] ADD End_Date as CAST(
CASE 
WHEN (Actual_Dt='' or Actual_Dt IS NULL) then  dbo.fnfoo( start_dt ,duration)
ELSE Actual_Dt
END as DATE
)
GO

after that I do some tests, like inserting some data into our table and see if it comes out with the right figures:

insert into dbo.TestDate(
 Start_dt,
 Duration) values ('1-may-2018',10)

  insert into dbo.TestDate(
 Start_dt,
 Duration) values ('3-may-2018',20)

insert into dbo.TestDate(
 Start_dt,
 Duration) values ('1-may-2020',10)

 insert into dbo.TestDate(
 Start_dt,
 Duration) values ('3-may-2020',20)

This is what I get with the above examples. check if this is correct:

 select * from [dbo].[TestDate]